MySQL data type

MySQL's data type is basically the same as SQL Server's data type, but there's one confusing thing about MySQL, which is that MySQL's int, char, varchar are all written as int (n), char (n), varchar (n) In this format, what exactly does this n mean? A lot of people would think that n here is the maximum length of preservation, for example. an int (10) can hold a 10-digit number, and an int (9) can only hold a number that is 9 bits in length. In fact, that's not true. First of all, what is the meaning of n in Int.

Follow the official MySQL explanation:

　　Int (m): M indicates The maximum display width for integer typestranslates into Chinese meaning that the m inside the brackets represents the maximum display length, Rather than the length of the store.

1 , integer mysql data type   meaning (signed) tinyint (m)  1 byte  range (--~127) smallint (m) 2 bytes  Range (-32768~32767) mediumint (m)    3 byte  range (-8388608~ 8388607 )int(m)  4 byte  range (-2147483648~2147483647) bigint (m)   8 byte  range (+-189.22*10)

Based on the information above, it can be concluded that either int (10) or int (9), which ultimately takes up four bytes, since the number of bytes is a fixed value, the data range that can be saved is also a fixed value. So "Int (10) can hold a 10-bit number, and int (9) can only hold a number that is 9 bits in length." Is wrong. "

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.

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> SE Lect * 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.

However, char (n), varchar (n), and n in Int (n) are different meanings. Let's take another look at the official explanation.

　　

First: discussion of char (n)

The length of a CHAR column is fixed to the length of this declare when you create The table. The length can is aNY value from 0 to 255. When CHAR values is stored, t hey is right-padded with spaces to thespecified length. When CHAR values is retrieved, trailing spaces is removed unless the pad_char_to_full_length [581] SQL mode is enabled.

The manual indicates that the length limit is 0-255, and the length limit is 0-255 characters. That is, there is no explicit limit on the number of bytes that are marked.

That is, char (2) can store a maximum of two character-length strings, and char (5) can store up to five character-length strings. N in char (n) limits the number of characters that can be stored.

Second: discussion of varchar (m)

Values in VARCHAR columns is variable-length strings. The length can specified as a value from 0 to 65,535.the effective maximum length of a varcharis subject to the maximum Row Size (65,535 bytes, which is gkfx among all columns) and the character set used. See section e.10.4, "Table Column-count and Row-size Limits".

official documents indicate that them in varchar (m) limits the number of bytes that varchar can store .

Add the data type below
1. Integral type

 1  mysql data type meaning (signed)  2  tinyint (m) 1 byte range (-128  ~ 127  )  3  smallint (m) 2 byte range (-32768  ~32767  )  4  mediumint (m) 3 byte range (-8388608  ~< Span style= "COLOR: #800080" >8388607  )  5  int  (m) 4 byte range (-2147483648  ~2147483647  )  6  bigint (m) 8 byte range (+-9.22  *10 18 times) 

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)

1 MySQL data type   meaning 2float(m,d)  single-precision floating-point    8-bit precision (4 bytes)     m total number, D decimal place 3  Double (m,d) dual 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.

1 decimal (m,d) parameter m< is the total number, d<30 and d<m are decimal digits.

4. String (Char,varchar,_text)

1 MySQL data type   meaning 2char(n)     fixed length, up to 255 characters 3varchar (n)  Variable length, up to 65,535 characters 4tinytext    variable length, up to 255 characters 5text        variable length, Up to 65,535 characters 6 mediumtext  variable length, up to 2 24 square-1 characters 7 longtext    variable length, Up to 2 of 32 square-1 characters

5. Date and Time data type

1 MySQL data type   meaning 2 date        3 bytes, date, format:All-3 time        3 bytes, time, Format:£ º:4 datetime    8 bytes, datetime, Format:-  Geneva:5timestamp   4 bytes, automatically stores the time the record was modified  6        years 1 bytes, year

MySQL data type