MySQL数据类型和类型选择

官方文档地址   https://dev.mysql.com/doc/refman/5.7/en/data-types.html

1.类型选择

使用简单类型：简单数据类型的操作通常需要更少的CPU周期，例如，整型比字符操作代价更低。

更小的类型：一般情况下，尽量使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为更小的类型占用更少的磁盘空间、内存和CPU缓存，处理是需要的CPU周期也更少。但要确保没有低估需要存储值的范围。

避免NULL：通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值或者表存储的是稀疏数据。可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊的处理。

标识符选择：整数类型通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT。应该尽量避免使用字符串类型作为标识列，因为它们很耗空间，比数字类型慢。一旦选定了一种类型，要确保在所有关联表中都使用同样的类型。类型之间需要精确匹配，包括UNSIGNED这样的属性。混用不同的数据类型可能导致性能问题，也可能导致很难发现的错误。

2.整数类型

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

8bit存储空间；存储范围 -128 ~ 127，unsigned：0~ 255。

BOOL, BOOLEAN

与TINYINT为同义词；使用方式SELECT IF(0, 'false', 'true');

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

16bit存储空间；存储范围-32768 ~ 32767，unsigned：0~ 65535。

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

24bit存储空间；存储范围 -8388608 到 8388607，unsigned：0 ~16777215。

INT[(M)] [UNSIGNED] [ZEROFILL]

32bit存储空间；存储范围-2147483648到 2147483647，unsigned：0~4294967295。

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

与INT为同义词。

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

64bit存储空间；存储范围-9223372036854775808到 9223372036854775807，unsigned：0~ 18446744073709551615。

SERIAL

为BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的别名

其它说明：UNSIGNED标识的列不允许负值。有符号和无符号类型使用相同的存储空间，具有相同的性能。ZEROFILL，显示时在数字前自动补0；标识为ZEROFILL的列自动标识为UNSIGNED。MySQL可以为整数类型指定宽度，如INT(11)；它不会限制值的合法范围，只是规定了MySQL交互工具用来显示字符的个数。对于存储来说，INT(1)和INT(20)是相同的。MySQL整数运算都是使用BIGINT的整数，即使在32为的环境。因此防止使用无符号大于9223372036854775807 (63 bits)的整数进行运算。

3.实数类型

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

32bit存储空间,使用标准浮点运算进行近似计算；存储范围-3.402823466E+38~-1.175494351E-38，unsigned：1.175494351E-38~ 3.402823466E+38

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

64bit存储空间，使用标准浮点运算进行近似计算存储范围-1.7976931348623157E+308 ~ -2.2250738585072014E-308，unsigned：2.2250738585072014E-308~ 1.7976931348623157E+308.

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

允许存储最多65个数字，DECIMAL的运算结果不能超过65个数字。用于存储精确小数，支持精确计算。因为需要额外的空间，应只在对小数进行精确计算是才使用。可以使用BIGINT将需要存储的小数尾数乘以相应的倍数代替。

DEC[(M[,D])] [UNSIGNED] [ZEROFILL],NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL],FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

与DECIMAL为同义词，FIXED可与其他数据库兼容。

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

与DOUBLE为同义词，REAL_AS_FLOAT为启用，REAL为FLOAT类型，关闭为DOUBLE类型。

其它说明：浮点数和DECIMAL类型可以指定精度，如FLOAT(7,4)可以显示为-999.9999，MySQL保存值时进行四舍五入，如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。DECIMAL指定小数点前后允许的最大位数会影响空间消耗。实数类型建议只指定数据类型不指定精度。DECIMAL也可以用来存储比BIGINT还大的整数。

4.字符串类型

[NATIONAL] CHAR[(M)] [CHARACTER SETcharset_name] [COLLATE collation_name]

固定长度类型，根据定义的长度分配足够的空间，M范围1 ~ 255。适用存储很短的字符串；所有的值都接近同一长度的字符串，如密码的MD5值，Y/N等状态值。存储CHAR值时，MySQL会删除所有末尾空格。程序使用CHAR值时可能会补充相应长度的空格。

NATIONAL CHAR：使用预定义字符集的CHAR类型，等价NCHAR。CHAR类型按字节存储，NCHAR按字符存储。NCHAR根据unicode标准所进行的定义，用给定整数代码返回unicode字符。 NCHAR(6)对汉字和半角字符处理时一样，即可以存储6个汉字，也可以存储6个字母 。

[NATIONAL] VARCHAR(M) [CHARACTER SETcharset_name] [COLLATE collation_name]

可变长字符串，越短的字符串使用越少的空间。M范围1 ~ 65535，MySQL行最大长度为64KB，所以VARCHAR最大长度为64KB - 其它列长度。gbk编码，每个字符最多占2byte，utf-8编码，每个字符最多占3byte。UPDATE时可能使行变得比原来长，导致需要做额外的工作。适用列的更新很少、使用了UTF-8、GBK等复杂字符集的列。

NATIONAL VARCHAR：使用预定义字符集的VARCHAR类型，等价NVARCHAR。

BINARY(M)

固定长度类型，与CHAR类似。存储的是二进制字节码而不是字符。采用\0填充到需要的长度，在检索时也不会去掉填充。

VARBINARY(M)

与VARCHAR类似。存储的是二进制字节码而不是字符。

其它说明：COLLATE属性，即比对方法，用于指定数据集如何排序，以及字符串的比对规则。

5.日期和时间类型

DATE

日期类型，存储范围1000-01-01~9999-12-31。显示格式YYYY-MM-DD。

DATETIME[(fsp)]

存储范围从1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999。显示格式为YYYY-MM-DD HH:MM:SS[.fraction]，与时区无关，使用8byte存储空间。

TIMESTAMP[(fsp)]

保存从1970-01-01 00:00:01.000000(格林尼治时间GTM)以来的秒数，和UNIX时间戳相同，使用4byte存储空间，只能表示从1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999。值为0是表示0000-00-00 00:00:00。

TIME[(fsp)]

时间类型，存储范围-838:59:59.000000到838:59:59.000000。显示格式 HH:MM:SS[.fraction]。

YEAR[(4)]

存储范围 1901 ~ 2155和0000，显示格式 YYYY。

其它说明：FSP范围0~6，可以指定小数秒精度，0表示没有小数部分。TIMESTAMP的值与时区有关，DATATIME保留文本表示的日期和时间。TIMESTAMP比DATATIME空间效率高，通常建议使用TIMESTAMP。可选默认值CURRENT_TIMESTAMP、 ON UPDATE CURRENT_TIMESTAMP

6.大数据类型

TINYBLOB

最大长度255byte，存储二进制数据。在前缀使用1byte存储数据长度。

TINYTEXT [CHARACTER SET charset_name] [COLLATEcollation_name]

最大长度255个字符。存储字符数据，在前缀使用1byte存储数据长度。

BLOB[(M)]

最大长度65525byte或65KB，存储二进制数据。在前缀使用2byte存储数据长度。可选择M定义长度。与SMALLBLOB为同义词。

TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

最大长度65535个字符或65KB。存储字符数据，在前缀使用2byte存储数据长度。可选择M定义长度。与SMALLTEXT为同义词

MEDIUMBLOB

最大长度16777215byte或16MB，存储二进制数据。在前缀使用3byte存储数据长度。

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

最大长度16777215个字符或16MB。存储字符数据，在前缀使用3byte存储数据长度。

LONGBLOB

最大长度4294967295byte或4GB，存储二进制数据。在前缀使用4byte存储数据长度。

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

最大长度4294967295个字符或4GB。存储字符数据，在前缀使用4byte存储数据长度。

其它说明：BLOB和TEXT值对每个列的最前max_sort_length字节做排序，MySQL不能将BLOB和TEXT列全部长度进行索引。

7.其他类型

ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

枚举列可以把一些不重复的字符串存储成一个预定义的集合，每个值在列表中的位置保存为整数，为“数字-字符串”映射关系。ENUM字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE。MySQL存储枚举非常紧凑，ENUM与ENUM关联比VARCHAR与VARCHAR关联更快，ENUM与VARCHAR关联则会更慢。使用ENUM可以让表的大小缩小，在一定情况下即使出现ENUM与VARCHAR进行关联也值得。

SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name]

一个集合是一个字符串对象，它可以有零个或多个值，每个值都必须从列表中选择。创建表时指定允许的值。设置成员以逗号分隔，成员不能包含逗号。一个集合列最多可以有64个不同的成员。