MySQL数据库表设计实战-基本数据类型

使用MySQL数据库，首先要了解MySQL数据库支持的数据类型。本文将介绍MySQL的常用数据类型和属性以及他们的使用范例，后续将介绍MySQL JSON数据类型以及MySQL空间数据类型。

MySQL常用数据类型总体包括：数字类型、日期和时间类型、字符串类型。每一种数据类型又包括多个具体的类型。

目前大多数用户使用的MySQL版本为MySQL8.0+，同样，本文也基于MySQL8.0+。

数据类型名后跟(M)的含义

在MySQL建表语句中进行数据类型声明时，通常数据类型名后会加上(M)，其中M是一个整数值。对于不同的数据类型，(M)的含义有所不同：

• 对于整数(int、tinyint、smallint、mediumint、bigint)类型，M表示最大显示宽度。
• 对于浮点(float、double)和定点(decimal、numeric)类型，更常见的声明为float(M，D)、decimal(M，D)、numeric(M，D)等等。M是可以存储的数字总长度(也可称为精度)。D表示小数位数，即小数点后的位数，最大可能值为30，但不应大于M−2。声明中的D也可以省略，例如仅仅声明为float(M)、decimal(M)、numeric(M)，表示没有小数点和小数部分。
• 对于字符串类型，M是最大长度。
• 对于时间(TIME、DATETIME和TIMESTAMP)类型，M表示小数秒(秒的下级数)精度，即秒小数点后的位数。M必须在0到6的范围内，值为0表示不存在小数部分。

所有数据类型其后的(M)都可以省略，省略后，分别使用各自类型的默认值。

SIGNED与UNSIGNED

在整数类型、浮点型、定点型声明中，可以加SIGNED或UNSIGNED关键字，他们分别表示有符号数或无符号数，他们之间的区别是：有符号数允许负数，无符号数不允许负数；他们的取值范围(对于整数类型而言，具体下文说明)也不同。

因此，对于存储类似“成绩”、“身高”、“年龄”等数据的列，负数无意义，我们可以声明为UNSIGNED，排除负数的可能，声明如下：

• score tinyint UNSIGNED;
• height decimal(5,2) UNSIGNED;
• age tinyint UNSIGNED;

注意，虽然浮点类型和定点类型也可以是UNSIGNED。与整数类型一样，此属性防止负值存储在列中。不同的是，列值的上限范围保持不变。且从MySQL 8.0.17开始，对于类型为FLOAT、DOUBLE和DECIMAL(以及任何同义词)的列，不赞成使用UNSIGNED属性，可以使用CHECK约束排除负数。

数字数据类型

01、bit(M)

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

要指定位值，可以使用b'value'表示法。value是使用0和1编写的二进制值。例如，b'111'和b'10000000'分别表示7和128。例如我们经常用到的“逻辑删除”字段，就可以声明为

delete_flag bit(1) not null default b'0'

表示该字段只有两种取值：b'0'和b'1'，分别表示“未删除”和“已删除”。

如果将一个值分配给长度小于M位的BIT(M)列，则该值将在左侧填充零。例如，给BIT(6)列赋值b'101'实际上与赋值b'000101'相同。

02、TINYINT(M)

小整数。带符号的范围是-128到127。无符号范围为0到255。

03、BOOL, BOOLEAN

这两个类型是TINYINT(1)的同义词。0表示false，非0表示true。例如以下查询输出false：

SELECT IF(0, 'true', 'false');

而下面两个查询都返回true：

• SELECT IF(1, 'true', 'false');
• SELECT IF(2, 'true', 'false');

有所区别的是，MySQL内置常量TRUE和FALSE值仅仅分别只是1和0的别名；例如下面两个查询都输出true：

• SELECT IF(0 = FALSE, 'true', 'false');
• SELECT IF(1 = TRUE, 'true', 'false');

而下面两个查询都输出false：

• SELECT IF(2 = TRUE, 'true', 'false');
• SELECT IF(2 = FALSE, 'true', 'false');

因为2既不等于1也不等于0。

04、SMALLINT(M)

小整数。带符号的范围是-32768到32767。无符号范围为0到65535。

05、MEDIUMINT(M)

中等大小的整数。带符号的范围是8388608到8388607。无符号范围为0到16777215。

06、INT(M)

整数。带符号的范围是-2147483648到2147483647。无符号范围为0到4294967295。

07、INTEGER(M)

此类型是INT的同义词。

08、BIGINT(M)

大整数。带符号的范围是-9223372036854775808到9223372036864775807。无符号范围为0到18446744073709551615。

数据类型SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名。

关于MySQL整数类型，总结如下：

09、DECIMAL(M,D)

定点数，表示一个精确的数值。M是总位数(精度)，D是小数点后的位数(小数位数)。其中，总位数M不包括小数点和负数符号-(如果是负数)。如果D为0，可以声明为DECIMAL(M)，表示没有小数点或小数部分。

DECIMAL的最大位数M为65。支持的小数D的最大数目为30。如果省略M，则默认值为10。

从MySQL 8.0.17开始，DECIMAL类型的列(以及任何同义词)不赞成使用UNSIGNED属性。

所有具有DECIMAL列的基本计算（+、-、*、/）都以65位的精度完成。

DECIMAL类型用于存储精确的数字数据值。当保存精度很重要的数据，例如“金额”时，会使用此类型：声明salary(5,2)的列中的值的范围从-999.99到999.99。

10、DEC(M,D), NUMERIC(M,D), FIXED(M,D)

这些类型是DECIMAL的同义词。

11、FLOAT(M,D)

单精度浮点数。允许值为-3402823466E+38至-1.175494351E-38，0和1.175494351E-38至3.402823466E+38。这些是基于IEEE标准的理论限制。实际范围可能稍小，具体取决于硬件或操作系统。

M是总位数，D是小数点后的位数。如果省略M和D，则将值存储到硬件允许的极限。单精度浮点数精度到小数点后7位左右。

FLOAT(M，D)是一个非标准的MySQL扩展，从MySQL 8.0.17开始，该语法已被弃用，且UNSIGNED属性是不推荐使用的。

使用FLOAT(M，D)可能会带来一些意想不到的问题，因为MySQL中的所有计算都是以双倍精度完成的。所以，大多数场景下，FLOAT类型都不会被使用到。

12、FLOAT(p)

浮点数。p表示以位为单位的精度，声明时不可省略，MySQL使用此值来确定对生成的数据类型使用FLOAT还是DOUBLE。如果p在0到24之间，则数据类型变为FLOAT，且没有M或D值。如果p在25到53之间，则数据类型变为DOUBLE，没有M或D值。

提供FLOAT(p)语法是为了与ODBC兼容。

13、DOUBLE(M,D)

双精度浮点数。允许值为-17976931348623157E+308至-2.2250738585072014E-308，0，以及2.22507385850714E-308至1.7976931484623157E+308。这是基于IEEE标准的理论限制。实际范围可能稍小，具体取决于硬件或操作系统。

如果省略M和D，则将值存储到硬件允许的极限。双精度浮点数精确到小数点后约15位。

同样，DOUBLE(M，D)是一个非标准的MySQL扩展。从MySQL 8.0.17开始，该语法已被弃用，且UNSIGNED属性不推荐使用。

14、DOUBLE PRECISION(M,D), REAL(M,D)

这些类型是DOUBLE的同义词。如果启用了REAL_AS_FLOAT SQL模式，REAL是FLOAT的同义词，而不是DOUBLE。

FLOAT和DOUBLE类型表示近似的数字数据值。MySQL使用四个字节表示单精度值，使用八个字节表示双精度值。

日期和时间数据类型

用于表示时间值的日期和时间数据类型有DATE、TIME、DATETIME、TIMESTAMP和YEAR。

其中，TIME、DATETIME和TIMESTAMP类型声明可在类型名后加(M)，M表示微秒值位数，取值范围为1—6。例如：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

若M值为0表示不存在小数部分。如果声明中不写(M)，则默认精度为0。

表中的任何TIMESTAMP或DATETIME列都可以具有自动初始化和更新属性。

15、DATE

表示日期，没有时间部分的值。支持的范围为“1000-01-01”到“9999-12-31”。MySQL以“YYYY-MM-DD”格式显示DATE值，但允许使用字符串或数字给DATE类型的列赋值。

16、DATETIME(M)

年月日时分秒组合时间值。支持的范围为“1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999”。MySQL以“YYYY-MM-DD hh:MM:ss[.fraction]”格式显示DATETIME值，同样也允许使用字符串或数字给DATETIME列赋值。

17、TIMESTAMP(M)

时间戳。范围为1970-01-01 00:00:01.000000到2038-01-19 03:14:07 999999。MySQL将TIMESTAMP值从当前时区转换为UTC进行存储，并将其从UTC转换回当前时区进行检索。DATETIME等其他类型不会出现这种情况。

可以给每个数据库连接设置时区，只要时区设置保持不变，查询出的值与存储的值相同。如果存储TIMESTAMP值，然后更改时区并查询该值，则查询出的值与存储的值不同。

18、TIME(M)

时间值。范围为“-838:59:59.000000”到“838:59:59.00000”，小时部分之所以如此之大，是因为TIME类型不仅可以用于表示一天中的时间(必须小于24小时)，还可以用于表示经过的时间或两个事件之间的时间间隔(可能远大于24小时，甚至为负数)。MySQL以“hh:mm:ss[.fraction]”格式显示TIME值，允许使用字符串或数字给TIME列赋值。

19、YEAR(4)

四位数格式的年份，值显示为1901到2155或0000。MySQL以YYYY格式显示YEAR值，允许使用字符串或数字给YEAR列赋值。

MySQL 8.0不支持旧版本MySQL中允许的2位YEAR(2)数据类型，且从MySQL 8.0.19开始，不赞成使用具有显式显示宽度的YEAR(4)数据类型，而应使用不带显示宽度的YEAR，其含义相同。

String数据类型

String数据类型包括CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

对于字符串列(CHAR、VARCHAR和TEXT类型)的定义，类型声明中的长度M表示字符长度；对于二进制字符串列(binary、VARBINARY和BLOB类型)的定义，类型声明中的长度M表示字节长度。

字符串数据类型CHAR、VARCHAR、TEXT、ENUM、SET可以指定列字符集和排序规则：CHARACTER SET指定字符集。如果需要，可以使用COLLATE属性以及任何其他属性指定字符集的排序规则。例如：

CREATE TABLE t

(

c1 VARCHAR(20) CHARACTER SET utf8mb4,

c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs

);

此表定义创建了一个名为c1的列，该列的字符集为utf8mb4，具有该字符集的默认排序规则；创建了一列名为c2的列，其字符集为latin1，具有区分大小写的（_cs）排序规则。

20、CHAR(M)

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

MySQL允许创建CHAR(0)类型的列。当必须依赖于某个列的存在但实际上没有使用其值的情况下，这一点非常有用；又或者当需要一个只能取两个值的列时，CHAR(0)也很好：定义为CHAR(0) NULL的列只占用一位，并且只能取NULL和“”(空字符串)两个值。

21、VARCHAR(M)

可变长度的字符串，VARCHAR是CHARACTER VARYING的缩写。M表示以字符为单位的最大列长度。M的范围是0到65535。

22、BINARY(M)

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

CHAR BYTE数据类型是BINARY数据类型的别名，二者等效。

23、VARBINARY(M)

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

24、TINYBLOB

存储二进制String对象，最大长度为255(28−1)个字节。每个TINYBLOB值都使用1字节长度的前缀存储该值中的字节数。

25、TINYTEXT

存储非二进制String对象，最大长度为255(28−1)个字符。如果值包含多字节字符，则有效最大长度相应变小。每个TINYTEXT值都使用1字节长度前缀存储该值中的字节数。

26、BLOB(M)

最大长度为65535(216−1)字节的二进制String列。每个BLOB值都使用一个2字节长度的前缀存储该值中的字节数。

27、TEXT(M)

存储非二进制String对象，最大长度为65535(216−1)个字符。如果值包含多字节字符，则有效最大长度相应减小。每个TEXT值都使用2字节长度的前缀存储该值中的字节数。

28、MEDIUMBLOB

存储二进制String对象，最大长度为16777215(224−1)个字节。每个MEDIUMBLOB值都使用一个3字节长度的前缀存储该值中的字节数。

29、MEDIUMTEXT

存储非二进制String对象，最大长度为16777215(224−1)个字符。如果值包含多字节字符，则有效最大长度相应减小。每个MEDIUMTEXT值都使用一个3字节长度的前缀存储该值中的字节数。

30、LONGBLOB

存储二进制String对象，最大长度为4294967295或4GB(232−1)字节。LONGBLOB列的有效最大长度取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个LONGBLOB值都使用4字节长度的前缀存储该值中的字节数。

31、LONGTEXT

存储非二进制String对象，最大长度为4294967295或4GB(232−1)个字符。如果值包含多字节字符，则有效最大长度减小。LONGTEXT列的有效最大长度还取决于客户端/服务器协议中配置的最大数据包大小和可用内存。每个LONGTEXT值都使用4字节长度的前缀存储该值中的字节数。

32、ENUM('value1'，'value2'，…)

枚举。只能有一个值的字符串对象，从值“value1”、“value2”、…的列表中选择、NULL或特殊的“”错误值。ENUM值在内部表示为整数。

例如，声明为ENUM（“Mercury”、“Venus”、“Earth”）的列可以具有以下任何值和索引(序号)：

----------------------------

Value Index

NULL NULL

'' 0

'Mercury' 1

'Venus' 2

'Earth' 3

----------------------------

ENUM列最多可以有65535个不同的元素。

单个ENUM元素支持的最大长度为M<=255和（M x w）<=1020，其中M是元素文字长度，w是字符集中最大长度字符所需的字节数。

33、SET('value1'，'value2'，…)

可以具有零个或多个值的字符串对象，每个值都必须从值“value1”、“value2”、…的列表中选择。SET值在内部表示为整数。

例如，声明为SET（'one'，'two'）NOT NULL的列可以具有以下任何值：

------------------------

''

'one'

'two'

'one,two'

------------------------

SET列最多可以有64个不同的成员。

单个SET元素支持的最大长度为M<=255和（Mxw）<=1020，其中M是元素文字长度，w是字符集中最大长度字符所需的字节数。