MySQL数据类型和类型选择

小鱼儿 2022-06-12 13:21 485阅读 0赞

官方文档地址  [ https://dev.mysql.com/doc/refman/5.7/en/data-types.html][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 SET*charset\_name*\] \[COLLATE *collation\_name*\]

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

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

\[NATIONAL\] VARCHAR(*M*) \[CHARACTER SET*charset\_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*\] \[COLLATE*collation\_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个不同的成员。

[https_dev.mysql.com_doc_refman_5.7_en_data-types.html]: https://dev.mysql.com/doc/refman/5.7/en/data-types.html
[Link 1]: https://www.baidu.com/s?wd=%E5%8D%8A%E8%A7%92%E5%AD%97%E7%AC%A6&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1Y3rH0dPv7-PWubP1PhPv7W0ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EPjcYPj0kPWT