MySQL data type and characters commonly used segment Properties summary

Date and time data types

 Date 3 bytes, dates, format: 2014-09-18 time 3 bytes, duration, format: 08:42:30datetime 8 bytes, date time, format:2014-09-18 08:42:30timestamp 4 bytes, automatic storage record modified time year 1 bytes, years

Numeric data type

Tinyint 1 bytes, range ( -128~127) 2^8smallint 2 bytes, range (-32768~32767) 2^15mediumint 3 bytes, range (-8388608~8388607) 2 ^23int 4 bytes, range (-2147483648~2147483647) 2^31bigint 8 bytes, range (+-9.22*10 of 18 Parties) 2^63

Floating point Type

float (M, D) 4 bytes, single-precision floating point, m total number, D decimal place Double (M, D) 8 bytes, double-precision float, m total, D decimal decimal (M, D) decimal is a floating-point number stored as a string

I built a table in MySQL with a column named float (5, 3), and the following tests were done:
1. Insert 123.45678, the last query results are 99.999;
2. Insert 123.456, the last query result is 99.999;
3. Insert 12.34567, the last query result is 12.346;
Therefore, in the use of floating-point type, but also to pay attention to the trap, to insert the actual results in the database will prevail.

String data type

char (n) fixed length, up to 255 characters varchar (n) variable length, up to 65,535 characters tinytext variable length, up to 255 characters in text variable length, up to 65,535 characters mediumtext variable length, Up to 2 of 24 parties -1 characters longtext variable length, up to 2 32-by -1 characters

The 1.char (n) and varchar (n) brackets mean the number of characters, not the number of bytes, so when Chinese is used (UTF8) means you can insert M Chinese, but it actually consumes m*3 bytes.
2. At the same time char and varchar The biggest difference is that char regardless of the actual value will occupy n characters space, and varchar will only occupy the actual character should occupy +1 of the space, and the actual space +1<=n.
3. After the n setting of char and varchar is exceeded, the string is truncated.
The upper limit of 4.char is 255 bytes, the upper bound of varchar is 65535 bytes, and the text is capped at 65535.
5.char will truncate the trailing spaces when stored, varchar and text will not.
6.varchar uses 1-3 bytes to store the length, text does not.

Other types

1.enum ("Member1″," Member2″,NULL, the first member of the list is the default value.) 2.set ("member", "Member2″, ... The Member64″ set data type provides a way to specify 0 or more values in a predefined set of values, which includes a maximum of 64 members. The selection of a value is limited to the value declared in the column definition. 

MySQL data type and characters commonly used segment Properties summary