MySQL学习(2)---MySQL数据类型

ps：此随笔基于mysql 5.7.*版本。

补充：

• UNSIGNED：所有整数类型都可以有一个可选(非标准)UNSIGNED属性。无符号类型可用于在列中仅允许非负数存在，或者当开发者需要该列的较大数值范围时。例如，如果一个INT类型的列使用UNSIGNED属性，则该列范围的大小相同，但其端点向上移动，从-2147483648~2147483647到0~4294967295。浮点和定点类型也可以使用UNSIGNED属性。与整数类型一样，此属性可防止将负值存储在列中。与整数类型不同，列值的上限保持不变。
• ZEROFILL：当数值显示宽度比定义的列指定的宽度小时，空格的默认填充将替换为0，即会在数值前面自动进行补0操作。如果ZEROFILL为数字列所指定，MySQL会自动为该列添加UNSIGNED属性。

以test表为例，演示效果。

id字段具有ZEROFILL属性时，创建的test表如下：

添加数据并查询该表，可以发现id字段不足指定宽度的值左侧全部被0填充。

修改该表，去掉id字段的ZEROFILL属性，再次进行查询。

注意：对于表达式或查询具有ZEROFILL属性的列，该属性将被忽略。如果将大于显示宽度的值存储在具有具有ZEROFILL属性的整数列中，当MySQL为某些复杂的连接生成临时表时，可能会出现问题。

数值数据类型

整数类型(精确值)

MySQL支持SQL标准整数类型INTEGER(或INT)和SMALLINT。作为标准的扩展，MySQL还支持整数类型TINYINT、MEDIUMINT和 BIGINT。下表显示了每种整数类型所需的存储空间和范围。

类型	存储所需字节	最小值(带符号)	最大值(带符号)	最小值(无符号)	最大值(无符号)
TINYINT	1	-128	127	0	255
SMALLINT	2	-32768	32767	0	65535
MEDIUMINT	3	-8388608	8388607	0	16777215
INT	4	-2147483648	2147483647	0	4294967295
BIGINT	8	-9223372036854775808	-9223372036854775807	0	18446744073709551615

• TINYINT

格式：TINYINT[(M)] [UNSIGNED] [ZEROFILL]

一个非常小的整数。有符号范围-128~127。无符号范围是0~255。

• SMALLINT

格式：SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

一个小整数。有符号范围-32768~32767。无符号范围是0~65535。

• MEDIUMINT

格式：MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

一个中等大小的整数。有符号范围-8388608~8388607。无符号范围是0~16777215。

• INT(等同于INTEGER)

格式：INT[(M)] [UNSIGNED] [ZEROFILL]

一个正常大小的整数。有符号范围-2147483648~2147483647。无符号范围是0~4294967295。

• BIGINT

格式：BIGINT[(M)] [UNSIGNED] [ZEROFILL]

一个大整数。有符号范围-9223372036854775808~9223372036854775807。无符号范围是0~18446744073709551615。

定点类型(精确值)

DECIMAL和NUMERIC类型存储精确的数字数据值。当需要的精度很重要时使用这些类型，例如货币数据。在MySQL中，NUMERIC被实现为DECIMAL，所以以下关于DECIMAL的语法同样适用于NUMERIC。下表显示了每种定点类型所需的存储空间。此处暂不列出范围。

类型	存储所需字节
DECIMAL	不定长(长度可变)
NUMERIC	不定长(长度可变)

• DECIMAL

格式：DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

一个压缩的”精确”定点数。M是总位数(精度)，M的范围为1~65。D是小数点后的位数(小数位数)，D的范围为0~30。小数点(".")和负数符号("-")不计入M。如果D为0，则值没有小数点或小数部分。最大位数(M)为65。支持的最大小数位数(D)为30。如果D省略，则默认为0。如果M被省略，默认值为10。

注意：如果指定UNISIGNED，则不允许负值。所有带有列的基本计算 ("+","-","*","/")，DECIMAL都以65位的精度完成

浮点类型(近似值)

FLOAT和DOUBLE类型表示近似数值数据值。下表显示了每种浮点类型所需的存储空间。此处暂不列出范围。

类型	存储所需字节
FLOAT	4
DOUBLE	8

• FLOAT

格式1：FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

一个小的(单精度)浮点数。允许的值为-3.402823466E+38~-1.175494351E-38、0和1.175494351E-38~3.402823466E+38。这些是基于 IEEE标准的理论限制。根据不同的硬件或操作系统，实际范围可能会稍小一些。FLOAT(M,D)是一个非标准的MySQL扩展。M是总位数，D是小数点后的位数。如果省略M和D，则将值存储在硬件允许的范围内。单精度浮点数精确到大约7位小数。

注意：如果指定UNISIGNED，则不允许负值。同时，使用FLOAT可能会导致一些意想不到的问题，因为MySQL中的所有计算都是以双精度完成的。

格式2：FLOAT[(P)] [UNSIGNED] [ZEROFILL]

一个浮点数。p表示以位为单位的精度，但MySQL仅使用此值来确定是使用FLOAT还是DOUBLE用于生成的数据类型。如果p是从0到24，则数据类型变为FLOAT无M或D值。如果p是从25到53，则数据类型变为DOUBLE无M或D值。结果的范围与一般的单精度FLOAT或双精度数据类型相同。

• DOUBLE

格式：DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

一个正常大小(双精度)的浮点数。允许的值为-1.7976931348623157E+308~-2.2250738585072014E-308、0和2.2250738585072014E-308~1.7976931348623157E+308。这些是基于IEEE标准的理论限制。根据不同的硬件或操作系统，实际范围可能会稍小一些。DOUBLE(M,D)是一个非标准的MySQL扩展。

注意：如果指定UNISIGNED，则不允许负值。

位值类型

BIT数据类型用于存储位值。这种类型允许存储位值。

• BIT

格式：BIT[(M)] [UNSIGNED] [ZEROFILL]

位值类型。M表示每个值的位数，从1到64。如果M省略，则默认为1。

日期和时间数据类型

DATE、DATETIME和TIMESTAMP类型

• DATE

格式：DATE

日期类型。支持的范围为"1000-01-01"到"9999-12-31"。MySQL以"YYYY-MM-DD"格式显示日期值，但允许使用字符串或数字将值分配给日期列。

• DATETIME

格式：DATETIME[(fsp)]

日期和时间的组合。支持的范围是"1000-01-01 00:00:00.000000"到"9999-12-31 23:59:59.99999"。MySQL以"YYYY-MM-DD hh:MM:ss[.fraction]"格式显示日期时间值，但允许使用字符串或数字将值分配给日期时间列。可以给出0~6范围内的可选fsp值，用来指定小数秒精度。值为0表示不存在小数部分。如果省略，则默认精度为0。

• TIMESTAMP

格式：TIMESTAMP[(fsp)]

时间戳。范围为"1970-01-01 00:00:01.000000"UTC到"2038-01-19 03:14:07.9999"UTC。时间戳值存储为自历元(1970-01-01 00:00:00 UTC)以来的秒数。时间戳不能表示值"1970-01-01 00:00:00"，因为这相当于从纪元开始的0秒，而值0保留用于表示"0000-00-00 00:00-00"，即“零”时间戳值。可以给出0~6范围内的可选fsp值，用来指定小数秒精度。值为0表示不存在小数部分。如果省略，则默认精度为0。

时间类型

MySQL以"hh:mm:ss"格式检索和显示时间值(对于大的小时值，则以"hhh:mm:ss"形式)。时间值的范围可以从"-838:59:59"到"838:59:59"。小时部分可能大一些或小一些，因为时间类型不仅可用于表示一天中的时间(必须小于24小时)，还可用于表示两个事件之间的经过时间或时间间隔(可能远大于24小时，甚至为负)。

格式：TIME[(fsp)]

一段时间。范围为"-838:59:59.000000”到“838:59:59.00000"。MySQL以"hh:mm:ss[.fraction]"格式显示时间值，但允许使用字符串或数字将值分配给时间列。可以给出0~6范围内的可选fsp值，以指定小数秒精度。值为0表示不存在小数部分。如果省略，则默认精度为0。

年份类型

该YEAR类型是用于表示年份值的1字节类型。可以将其声明为YEAR具有4个字符的隐式显示宽度，或者等效地声明为YEAR(4)具有显式显示宽度。

格式：YEAR[(4)]

4位格式的年份。MySQL以YYYY格式显示年份值，但允许使用字符串或数字将值分配给年份列。值显示为1901~2155或是0000。此外，YEAR(2)数据类型已被弃用，MySQL 5.7.5版本中已删除对它的支持。

字符串数据类型

CHAR和VARCHAR类型

• CHAR

格式：[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

一个固定长度的字符串，在存储时总是用空格右填充到指定的长度。M表示以字符为单位的列长度。M的范围是0~255。如果M省略，则长度为1。

注意：除非启用严格的SQL模式，CHAR否则检索值时将删除尾随空格。

• VARCHAR

格式：[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

一个可变长度的字符串。M表示最大列长度(以字符为单位)，且范围是0~65535。VARCHAR的有效最大长度取决于最大行大小(65535字节，在所有列中共享)和所使用的字符集。例如，utf8字符每个字符最多需要三个字节，因此使用utf8字符串集的VARCHAR列最多可以声明为21844个字符。

注意：MySQL遵循标准SQL规范，并且不会从VARCHAR值中删除尾随空格。

BINARY和VARBINARY类型

• BINARY

格式：BINARY[(M)]

该BINARY类型与CHAR类型类似，但存储的是二进制字节字符串而不是非二进制字符串。可选长度M表示以字节为单位的列长度。如果省略，M则默认为1。

• VARBINARY

格式：VARBINARY(M)

该VARBINARY类型与VARCHAR类型类似，但存储的是二进制字节字符串而不是非二进制字符串。M表示以字节为单位的最大列长度。

BLOB和TEXT类型

• BLOB

BLOB是一个二进制大对象，可以保存可变数量的数据。四种BLOB类型是TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

格式：BLOB[(M)]

最大长度为65535(2¹⁶−1)字节。每个BLOB值使用2字节长度前缀存储，该前缀指示值中的字节数。对于这种类型，可以给出可选长度M。如果这样做了，MySQL会将列创建为最小的BLOB类型，该类型足够大，可以容纳M字节长的值。其他同类型如TINYBLOB、MEDIUMBLOB和LONGBLOB与之类似。

• TEXT

这四种文本类型是TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。它们对应于四种BLOB类型，具有相同的最大长度和存储要求

格式：TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name]

TEXT最大长度为65535 (2¹⁶−1)个字符的列。如果值包含多字节字符，则有效最大长度较小。每个文本值使用2字节长度的前缀存储，该前缀指示值中的字节数。对于这种类型，可以给出可选长度M。如果这样做了，MySQL会将列创建为最小的文本类型，其大小足以容纳M个字符长的值。其他同类型如TINYTEXT、MEDIUMTEXT和LONGTEXT与之类似。

枚举类型

ENUM是一个字符串对象，其值选自创建表时列规范中明确枚举的允许值列表。

• ENUM

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

一个枚举。只能有一个值的字符串对象，从值列表"value1"、"value2"、"NULL"或特殊的错误值中选择。枚举值在内部表示为整数。一个ENUM列最多可以有65535个不同的元素(实际限制小于3000)。

SET类型

SET是一个字符串对象，可以有零个或多个值，每个值都必须从创建表时指定的允许值列表中选择。 SET包含多个集合成员的列值使用逗号","分隔的成员指定。这样做的结果是SET成员值本身不应包含逗号。

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

一个集合。一个字符串对象，可以有零个或多个值，每个值必须从值列表"value1"、"value2"中选择。集合值在内部表示为整数。

注：部分内容参考自MySQL官方手册。地址：https://dev.mysql.com/doc/refman/5.7/en/