Explain the meaning of M in MySQL Data Type int (M), mysqlint

Explain the meaning of M in MySQL Data Type int (M), mysqlint

Introduction

The integer types in the MySQL data type is a bit strange. You may see int data types such as int (3), int (4), and int (8. When I first came into contact with MySQL, I thought that int (3) occupied less storage space than int (4), and int (4) occupied less storage space than int (8.

Later, I read the MySQL manual and found that I was wrong.

int(M): M indicates the maximum display width for integer types.

In integer data type, M indicates the maximum display width.

It turns out that in int (M), the value of M has nothing to do with the storage space occupied by int (M. Int (3), int (4), and int (8) occupy the storage space of 4 btyes on the disk. To put it bluntly, apart from the display method to users, int (M) and int data types are the same.

In addition, int (M) can only be used together with zerofill to clearly see the differences.

mysql> drop table if exists t;mysql> create table t(id int zerofill);mysql> insert into t(id) values(10);mysql> select * from t;+------------+| id   |+------------+| 0000000010 |+------------+mysql> alter table t change column id id int(3) zerofill;mysql> select * from t;+------+| id |+------+| 010 |+------+mysql>mysql> alter table t change column id id int(4) zerofill;mysql> select * from t;+------+| id |+------+| 0010 |+------+mysql>mysql> insert into t(id) values(1000000);mysql> select * from t;+---------+| id  |+---------+| 0010 || 1000000 |+---------+

From the test above, we can see that "(M)" specifies the width of the int type value display. If the field data type is int (4), when the value is 10, on the left side, add "00". When the displayed value is 100, add "0" on the left side. When the displayed value is 1000000, the specified width "(4)" is exceeded )", therefore, output as is.

When using the integer types (tinyint, smallint, mediumint, int/integer, and bigint) in the MySQL DATA type, add "(M)" after the data type for non-special requirements )", I cannot think of any significance.

Below is a supplement to the Data Type

1. Integer

MySQL Data Type meaning (Signed) tinyint (m) 1 byte range (-128 ~ 127) smallint (m) 2 bytes range (-32768 ~ 32767) mediumint (m) 3 bytes range (-8388608 ~ 8388607) int (m) 4 bytes range (-2147483648 ~ 2147483647) bigint (m) 8 bytes (+-9.22*10 to the power of 18)

If the value range is unsigned, the maximum value is doubled. For example, the value range of tinyint unsigned is (0 ~ 256 ).
In int (m), m indicates the display width in the SELECT query result set. It does not affect the actual value range and does not affect the display width.

2. float (float and double)

MySQL Data Type meaning float (m, d) single precision floating point type 8-bit precision (4 bytes) Total number of m, d decimal point double (m, d) double Precision Floating Point Type 16-bit precision (8 bytes) total m count, d decimal places

Set a field to float (5, 3). If a number of 123.45678 is inserted, the actual database stores 123.457, but the total number is still subject to the actual situation, that is, 6 digits.

3. Count

The floating point type stores approximate values in the database, while the fixed point type stores precise values in the database.

The decimal (m, d) Parameter m <65 is the total number, d <30, and d <m is the decimal place.

4. String (char, varchar, _ text)

MySQL Data Type meaning char (n) fixed length, up to 255 characters varchar (n) variable length, up to 65535 characters tinytext variable length, up to 255 characters text variable length, A Variable Length of up to 65535 characters in mediumtext, a variable length of up to 2 to the power of 24-1 characters in longtext, a maximum of 2 to the power of 32-1 Characters

5. Date and Time Data Types

MySQL Data Type meaning date 3 byte, date, format: 2014-09-18time 3 byte, time, format: 08: 42: 30 datetime 8 byte, date and time, format: 08: 42: 30 timestamp 4 bytes, automatic storage record modification time year 1 byte, year

Summary

The above is all about this article. I hope this article will help you in your study or work. If you have any questions, please leave a message.