MySQL data type

The type of data field defined in MySQL is very important for your database optimization;

MySQL supports multiple types and can be broadly divided into three categories: numeric, date/time, and string (character) types;

Integer type

Meaning of N in Int (n)

Defines the init (5) Zerofill when and int join when the display width is not on the possibility of a temporary table

n indicates the display width is n, but still occupies 4 bytes of storage, the storage range is unchanged;

>Create TableInt_test (AintZerofill not NULLAuto_increment,PRIMARY KEY(a));>CreateTable Int_test_4 (Aint(4) Zerofill not NULLAuto_increment,PRIMARY KEY(a));>Select *  fromInt_test_4;+------------+|A|+------------+|       0001 ||       0002 ||2147483648 |+------------+>Select *  fromint_test;+------------+|A|+------------+|0000000001 ||0000000002 ||2147483648 |+------------+

About floating-point type: 1) can not be used to try not to use, 2) floating point number can not be used in the comparison of the scene

Suggested tinyint replace enum

date and Time type

The date and time types that represent time values are datetime, date, TIMESTAMP, hour, and year.

MySQL5.6 does not support year (2)
Date type Considerations

Timestamp,datatime from MySQL5.6.5 Support auto-update to current time: Timestamp

Date conversion: Cast (datatime_col as date)

>select now (+0);

US support for 5.6

>select Now (4), Microsecond (now (4));

+--------------------------+---------------------+

|now (4) | Microsecond (now (4)) |

+--------------------------+---------------------+

|2016-04-16 08:50:01.6589 | 658900 |

+--------------------------+---------------------+

NULL is supported after timestamp5.6.6

Suggest datetime to replace timestamp

String Type

The string type refers to Char, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, enum, and set. This section describes how these types work and how they are used in queries.

Character type

The difference between varchar and char

Char is a fixed-length type, and varchar is a variable-length type, with the difference being that each value occupies M-bytes in a data column of type char (m), and if a length is less than m,mysql, it is complemented by a space character on its right. (The space characters that are filled in the search operation are removed) in the varchar (M) Type of data column, each value occupies just enough bytes plus a byte to record its length (that is, the total length is l+1 bytes) varchar stores a variable length string, 1 extra bytes (greater than 255 require 2 extra bytes) for storage lengths of less than 255 bytes and a maximum length of 65532 bytes (sum of all columns);

Char is stored for a fixed length, and the trailing space is truncated when read, up to a maximum of 255 characters;

1) The Meaning of CHAR (M):

The actual allocated length is: m* character encoding length = storage space

For example: stored 255 characters character kanji is 3 bytes

255*3 = 7,651 Total is 765 bytes

2) Meaning of n in varchar (n)
The maximum storage is n characters, and varchar (5) and varchar (200) store the same space as Hello, but the latter consumes more memory when sorting because the order by Col uses fixed_length to calculate col length (same as memory engine)
For example:

varchar (UTF8) The number of bytes occupied below

200*3+ 2

varchar (UTF8)

64*3=192<255
192+1=193

Suggestions:

Usually using MySQL is the InnoDB engine InnoDB engine is inherently variable-length storage

Rows are stored with:

trx_id, Row-id,rollback, Filed_pointer, Null-flag, filed1,....

INNODB Storage Engine Recommended varchar

Char is faster for myisam this heap table char fixed length allocation will be a little faster

Calculation examples

Give two examples to illustrate the actual length of the calculation.

A) If a table has only one varchar type, as defined as

createtable t4 (c varchar (N)) CHARSET=GBK;

The maximum value for n here is (65535-1-2)/2= 32766.

The reason for minus 1 is that the actual row storage starts with the second byte ';

The reason for minus 2 is that the 2 bytes of the varchar header represent the length;

The reason for the addition of 2 is that the character encoding is GBK.

b) If a table is defined as

createtable t4 (c int, C2 char (+), C3 varchar (N)) Charset=utf8;

The maximum value of n here is (65535-1-2-4-30*3)/3=21812

Minus 1 and minus 2 are the same as in the previous example;

The reason for minus 4 is that the int type C accounts for 4 bytes;

The reason for reducing 30*3 is that char (30) occupies 90 bytes and the encoding is UTF8.

MySQL data type