MySQL database data type and the meaning of int (M)

NT (M) let's first split, int is representing integer data so the middle of the M should represent how many bits, and later the MySQL handbook also learned that my understanding is correct, I would like to illustrate.

The integer types in the MySQL data type is a bit strange. You might see an int data type such as int (3), int (4), int (8). When I first approached MySQL, I thought int (3) took up less storage space than int (4), and int (4) used less storage space than int (8).

Later, see the MySQL manual and find that you understand the mistake.

The code is as follows	Copy Code
Int (m): M indicates the maximum display width for integer types.

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

Originally, in Int (m), the value of M has nothing to do with how much storage space the Int (m) occupies. Int (3), int (4), and Int (8) All occupy 4 btyes of storage space on disk. To put it bluntly, the Int (M) is the same as the int data type except that the way it is displayed to the user is a little different.

In addition, int (M) can only be combined with Zerofill to make us see the difference clearly.

The code is as follows

Copy Code

mysql> drop table if exists t; mysql> CREATE TABLE t (id int zerofill); mysql> INSERT into T (ID) values; 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 | +---------+

As can be seen from the above test, "(M)" Specifies the width of the int type numeric display, if the field data type is int (4), then: When the value of 10 o'clock is displayed, the left side of the "00", when the value 100 is, on the left to fill "0"; When the value 1000000 is displayed, The specified width "(4)" has been exceeded, so it is output as-is.

When using integer types in MySQL data types (tinyint, smallint, mediumint, Int/integer, bigint), there is no point in adding a "(M)" to the data type after the non-special requirements.

Add the data type below

1. Integral type

MySQL data type	Meaning (Signed)
tinyint (m)	1 byte range ( -128~127)
smallint (m)	2 byte range ( -32768~32767)
Mediumint (M)	3 byte range ( -8388608~8388607)
Int (m)	4 byte range ( -2147483648~2147483647)
BigInt (M)	8 byte Range (18 +-9.22*10 of the X-square)

If unsigned is added to the value range, the maximum value is doubled, such as the tinyint unsigned value range (0~256).

m in int (m) is the display width in the result set of the select query, does not affect the actual range of values, does not affect the width of the display, do not know what the use of this m.

2. Float type (float and double)

MySQL data type	Meaning
Float (m,d)	Single-precision floating-point 8-bit precision (4 bytes) m total number, D decimal place
Double (m,d)	Double-precision floating-point 16-bit precision (8 bytes) m total number, D decimal place

Set a field defined as float (5,3), if you insert a number 123.45678, the actual database is 123.457, but the total number is actually the same, that is, 6 bits.

3. Fixed-point number

Floating-point types hold approximate values in the database, while the fixed-point type stores the exact 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)	Fixed length, up to 65,535 characters
Tinytext	Variable length, up to 255 characters
Text	Variable length, up to 65,535 characters
Mediumtext	Variable length, up to 2 of 24 square-1 characters
Longtext	Variable length, up to 2 of 32 square-1 characters

MySQL database data type and the meaning of int (M)