位置：首页-资讯-数据库

MySQL表设计原则

2023-09-22 22:34

短信预约 -IT技能 免费直播动态提醒

前言

这里简单整理一些常用的数据库表设计原则以及常用字段的使用范围。

表的设计准则

1、命名规范

表名、字段名必须使用小写字母或者数字，禁止使用数字开头，禁止使用拼音，并且一般不使用英文缩写。
主键索引名为 pk_字段名；唯一索引名为 uk_字段名；普通索引名则为 idx_字段名。

表名：test_account    # test可为数据库的缩写字段名：account_number

2、主键设计要合理

主键设计的话，最好不要与业务逻辑有所关联，最好是毫无意义的一串独立不重复的数字，比如UUID，又或者 auto_increment 自增的主键，或者是雪花算法生成的主键等等;

3、不要使用保留字

如果字段名为保留字，在语句里没有加上反引号的话，会按照保留字解析语句报错或执行结果与预期不一致。

如：desc、case、revoke等。

4、选择合适的字段类型和长度

设计表时，我们需要选择合适的字段类型，比如：

尽可能选择存储空间小的字段类型，就好像数字类型的，从tinyint、smallint、int、bigint从左往右开始选择；
小数类型如金额，则选择 decimal，禁止使用 float 和 double（都存在精度损失的问题），也可以使用整数类型存分单位，不过需要转换；
如果存储的字符串长度几乎相等，使用 char 定长字符串类型；
varchar是可变长字符串，不预先分配存储空间，长度不要超过5000；
如果存储的值太大，建议字段类型修改为 text，同时抽出单独一张表，用主键与之对应；
单表中一条记录最大的长度是65535字节，如果需要存储超过65535字节的字符串，请使用text/longtext 类型；
- 我的理解是单条记录中所有使用使用B+树结构存储的字段大小加上其他结构字段的存储位置指针大小最大为65535字节；
  - 我测试了在设置已添加 varchar 字段后再分别添加 int 和 text 类型，varchar 字段需要减1和2，所以 int 应该占位4字节，text 是指向倒排索引的指针占位8字节。
- 以utf8编码为例，存储中文占3个字节： (65535-1-2)/3=21844；在一行只有一个字段的情况下 varchar(N) 中的 N 最大值为21844。
  - 减1的原因是实际行存储从第二个字节开始；
  - 减2的原因是varchar头部的2个字节表示长度；
存储时间时选择： int、datetime 还是 timestamp？
- 如果存储效率是关键因素并且您需要对日期和时间信息进行排序和比较操作，则使用 int 类型可能更为合适。如果可读性和直观性是更重要的，则应选择 datetime类型。timestamp 类型包含时区，存储时会自动检索当前时区并进行转换这个使用需要注意。
- 注意： 对于 timestamp 来说，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。对于 int 和 datetime 来说，存什么拿到的就是什么。int 类型和 timestamp 类型都是4字节且存储时间最多到2038年，datetime 类型是8字节可以到9999年。

不清楚字段类型使用范围可参考下面的字段类型选择或官方文档。

5、字段注释

除主键外的每个字段必须添加注释说明，尤其是枚举字段，每种类型的含义一定要描述清楚。

6、表必备三字段

每个表都需要添加这几个通用字段如 主键id、create_time、update_time，id 作为主键，建议使用无符号 bigint，无符号可以支持的范围是 2⁶³-1 (0-18446744073709551615)，足以满足我们的业务需要。

如果id需要对外展示，比如id作为订单id等，可以使用雪花算法生成的id。
- 雪花算法生成的id步长不固定，但是仍然遵守大致递增的规律，对MySQL性能没有影响的同时，又可以很好的保证竞争对手无法通过订单id推算订单量。
create_time 表示记录创建时间，update_time 表示记录更新时间。

7、尽可能使用not null定义字段

每个默认值为 null 的字段会在行头额外占据1个字节的存储空间，还可能使得我们的 sql 产生预期外的结果。

8、大字段

如果存储的值太大，建议字段类型修改为 text，同时抽出单独一张表，用主键与之对应，如果有那种非常非常大的数据，可以保存到 mongodb 中，然后，在业务表保存对应 mongodb 的 id 即可。

9、一张表的字段不宜过多

一般尽量不要超过20个字段，索引的话一般不超过3个，不要超过5个。

以InnoDB为例，单表最多允许添加1017个字段，16个索引。

10、设计表时，评估哪些字段需要加索引

查询更快，占用空间小。

定义主键的数据列一定要建立索引；
定义有外键的数据列一点要建立索引；
对于经常查询的数据列建立索引；
对于需要在指定范围内的快速或频繁查询的数据列；
经常用where字句中的数据列；
经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用；
限制索引数目，索引数一般不超过3个，最多不超过5个。索引提高了访问速度，但太多索引会影响数据的更新；

11、不必强求数据库三范式

三大范式产生的背景是资源紧张不足，我们只能尽可能地节约资源，从而获得更高的性能。

但是现在随着技术的发展，服务器的性能也越来越强，我们在设计表时，通过适当的字段冗余，来获取更高更快的查询性能也是没有问题的。

12、存储引擎默认选择 InnoDB

两大常用存储引擎，MyISAM 和 InnoDB，MyISAM 不支持事务，支持全文索引，InnoDB两者都支持。

在使用 mysqldump 命令进行数据库备份的时候会开启一个事务，来确保拿到一致性视图。如果存在MyISAM的表那么就只能通过 FTWRL 方法加全局锁，这是数据库是只读状态。

13、选择合适统一的字符集

数据库库、表、开发程序等都需要统一字符集，通常中英文环境用utf8。

MySQL支持的字符集有utf8、utf8mb4、GBK、latin1等。

utf8：支持中英文混合场景，国际通过，3个字节长度
utf8mb4: 完全兼容utf8，4个字节长度，一般存储emoji表情需要用到它。
GBK ：支持中文，但是不支持国际通用字符集，2个字节长度
latin1：MySQL默认字符集，1个字节长度

14、优先考虑逻辑删除，而不是物理删除

保证数据的安全性，方便追回数据。

物理删除是真实删除，逻辑删除是给数据添加一个字段，比如 is_del，以标记该数据已经逻辑删除。
物理删除就是执行 delete 语句，逻辑删除执行的是 update 语句。

15、不搞外键关联，一般都在代码维护

使用外键存在性能问题、并发死锁问题、使用起来不方便等等。每次做DELETE或者UPDATE都必须考虑外键约束，会导致开发的时候很难受,测试数据造数据也不方便。
还有一个场景不能使用外键，就是分库分表。

16、设计表时预估未来数据量

在设计表的时候估算一下，是否需要做分库分表。如果未来的数据量可能到达百万甚至千万的话，就可以提前考虑分库分表，可使用分库分表中间件实现，常用的有 Sharding-jdbc 和 Mycat 等。

分库分表主要有 水平拆分、 垂直拆分的说法，拆分策略有range范围、hash取模。而分库分表主要有这些问题：

事务问题
跨库关联
排序问题
分页问题
分布式ID

字段类型选择

以下内容转载于：MySQL教程：MySQL数据库学习宝典（从入门到精通）

1、整数类型

整数类型 一共有 5 种，包括 tinyint、smallint、mediumint、int 和 bigint。

类型名称	存储	范围	长度
TINYINT	1字节	-2⁷~2⁷-1 即 -128~127	2⁸-1（0~255）
SMALLINT	2字节	-2¹⁵~2¹⁵-1 即 -32768~32767	2¹⁶-1（0~65535 ）
MEDIUMINT	3字节	-2²³~2²³-1 即 -8388608~8388607	2²⁴-1（0~16777215）
INT (INTEGER)	4字节	-2³¹~2³¹-1 即 -2147483648~2147483647	2³²-1（0~4294967295）
BIGINT	8字节	-2⁶³~2⁶³-1 即 -9223372036854775808~9223372036854775807	2⁶⁴-1（0~18446744073709551615）

整数类型的可选属性有三个：

M : 表示显示宽度，M的取值范围是(0, 255)。可配合ZEROFILL使用。
UNSIGNED : 无符号类型（非负），无符号整数类型的最小取值为0。所以，如果需要在MySQL数据库中保存非负整数值时，可以将整数类型设置为无符号类型。
ZEROFILL : 0填充，如果指定了ZEROFILL只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。

2、浮点数类型和定点数类型

MySQL支持的浮点类型有两种，分别是单精度浮点数（FLOAT） 和 双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。

类型名称	说明	存储	取值范围
FLOAT	单精度浮点数	4个字节	有符号的取值范围：-3.402823466E+38～-1.175494351E-38。无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。
DOUBLE	双精度浮点数	8个字节	有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。
DECIMAL(M,D)，DEC	压缩的“严格”定点数	M+2 个字节	同DOUBLE

浮点类型和定点类型都可以用(M, D)来表示，其中 M 称为精度，表示总共的位数；D 称为标度，表示小数的位数。

例：float(10,2)

浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

DECIMAL 类型 不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以 字符串 的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

从表格中可以看到，DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。

提示：

不论是定点还是浮点类型，如果用户指定的精度超出精度范围，则会四舍五入进行处理。

FLOAT 和 DOUBLE 在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为（10，0）。

浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的范围；缺点是会引起精度问题。

在 MySQL 中，定点数以字符串形式存储，在对精度要求比较高的时候（如货币、科学数据），使用 DECIMAL 的类型比较好

另外两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点数时需要注意，并尽量避免做浮点数比较。

浮点数和定点数的比较：

浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）。
定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）。

3、日期与时间类型

MySQL 中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。

每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将 0 值插入数据库中。

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901~2155	1 个字节
TIME	HH:MM:SS	838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC~ 2040-01-19 03:14:07 UTC	4 个字节

3.1、YEAR 类型

YEAR 类型 是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 YEAR，如下所示：

以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 ‘1901’～’2155’。输入格式为 ‘YYYY’ 或者 YYYY，例如，输入 ‘2010’ 或 2010，插入数据库的值均为 2010。
以 2 位字符串格式表示的 YEAR，范围为 ‘00’ 到 ‘99’。‘00’～’69’ 和 ‘70’～’99’ 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。
以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。

提示： 两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

3.2、TIME 类型

TIME 类型 用于只需要时间信息的值，在存储时需要 3 个字节。格式为 HH:MM:SS。HH 表示小时，MM 表示分钟，SS 表示秒。

TIME 类型 的取值范围为 -838：59：59～838：59：59，小时部分如此大的原因是 TIME 类型不仅可以用于表示一天的时间（必须小于 24 小时），还可能是某个事件过去的时间或两个事件之间的时间间隔（可大于 24 小时，或者甚至为负）。

可以使用各种格式指定 TIME 值，如下所示：

‘D HH：MM：SS’ 格式的字符串。还可以使用这些“非严格”的语法：‘HH：MM：SS’、‘HH：MM’、‘D HH’ 或 ‘SS’。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。
‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，‘101112’ 被理解为’10：11：12’，但是 ‘106112’ 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

3.3、DATE 类型

DATE 类型 用于仅需要日期值时，没有时间部分，在存储时需要 3 个字节。日期格式为 ‘YYYY-MM-DD’，其中 YYYY 表示年，MM 表示月，DD 表示日。

在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期，取值范围为 ‘1000-01-01’～’9999-12-3’。例如，输入 ‘2015-12-31’ 或者 ‘20151231’，插入数据库的日期为2015-12-31。
以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：‘00～69’ 范围的年值转换为 ‘2000-2069’，‘70-99’ 范围的年值转换为 ‘1970～1999’。例如，输入 ‘15-12-31’，插入数据库的日期为 2015-12-31；输入 ‘991231’，插入数据库的日期为 1999-12-31。
以YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。
使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和’98@11@31’ 是等价的，这些值也可以正确地插入数据库。

3.4、DATETIME 类型

DATETIME 类型 用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 ‘YYYY-MM-DD HH：MM：SS’，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

以 ‘YYYY-MM-DD HH：MM：SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期，取值范围为 ‘1000-01-01 00：00：00’～’9999-12-3 23：59：59’。例如，输入 ‘2014-12-31 05：05：05’ 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
以 ‘YY-MM-DD HH：MM：SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，‘00～79’ 范围的年值转换为 ‘2000～2079’，‘80～99’ 范围的年值转换为 ‘1980～1999’。例如，输入 ‘14-12-31 05：05：05’，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

MySQL 允许“不严格”语法： 任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，‘98-12-31 11：30：45’、‘98.12.31 11+30+35’、‘98/12/31 113045’ 和 ‘98@12@31 11³⁰45’ 是等价的，这些值都可以正确地插入数据库。

3.5、TIMESTAMP 类型

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入数据时，要保证在合法的取值范围内。

提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 ‘00：00：00’，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

4、文本字符串类型

字符串类型 用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。

MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。

下表中列出了 MySQL 中的字符串数据类型，括号中的 M 表示可以为其指定长度。

类型名称	说明	存储
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2⁸
TEXT	小的非二进制字符串	L+2字节，在此，L<2¹⁶
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2²⁴
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2³²
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目(最大值为65535)
SET	一个设置，字符串对象可以有零个或多个SET 成员	1、2、3、4或8个字节，取决于集合成员的数量(最多64个成员)

VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

4.1、CHAR 和 VARCHAR 类型

CHAR(M) 为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M 表示列的长度，范围是 0～255 个字符。

例如，CHAR(4) 定义了一个固定长度的字符串列，包含的字符个数最大为 4。当检索到 CHAR 值时，尾部的空格将被删除。

VARCHAR(M) 是长度可变的字符串，M 表示最大列的长度，M 的范围是 0～65535。VARCHAR 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。

例如，VARCHAR(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。VARCHAR 在值保存和检索时尾部的空格仍保留。

实例： 下面将不同的字符串保存到 CHAR(4) 和 VARCHAR(4) 列，说明 CHAR 和 VARCHAR 之间的差别，如下表所示。

插入值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
‘’	’ ’	4字节	‘’	1字节
‘ab’	‘ab’	4字节	‘ab’	3字节
‘abc’	‘abc’	4字节	‘abc’	4字节
‘abcd’	‘abcd’	4字节	‘abcd’	5字节
'abcdef’	‘abcd’	4字节	‘abcd’	5字节

对比结果可以看到，CHAR(4) 定义了固定长度为 4 的列，无论存入的数据长度为多少，所占用的空间均为 4 个字节。VARCHAR(4) 定义的列所占的字节数为实际长度 加 1。

4.2、TEXT 类型

TEXT 列保存非二进制字符串，如文章内容、评论等。当保存或查询 TEXT 列的值时，不删除尾部空格。

TEXT 类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。不同的 TEXT 类型的存储空间和数据长度不同。

TINYTEXT 表示长度为 255字符的 TEXT 列。
TEXT 表示长度为 65535字符的 TEXT 列。
MEDIUMTEXT 表示长度为 16777215字符的 TEXT 列。
LONGTEXT 表示长度为 4294967295 或 4GB 字符的 TEXT 列。

4.3、ENUM 类型

ENUM 是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：

<字段名> ENUM( '值1', '值1', …, '值n' )

字段名指将要定义的字段，值 n 指枚举列表中第 n 个值。

ENUM 类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。

ENUM 值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从 1 开始编号，MySQL 存储的就是这个索引编号，枚举最多可以有 65535 个元素。

例如，定义 ENUM 类型的列（‘first’，‘second’，‘third’），该列可以取的值和每个值的索引如下表所示。

值	索引
NULL	NULL
‘’	0
first	1
second	2
third	3

ENUM 值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL 值排在其他所有枚举值前。

提示： ENUM 列总有一个默认值。如果将 ENUM 列声明为 NULL，NULL 值则为该列的一个有效值，并且默认值为 NULL。如果 ENUM 列被声明为 NOT NULL，其默认值为允许的值列表的第 1 个元素。

4.4、SET类型

SET 是一个字符串的对象，可以有零或多个值，SET 列最多可以有 64 个成员，值为表创建时规定的一列值。指定包括多个 SET 成员的 SET 列值时，各成员之间用逗号,隔开，语法格式如下：

SET( '值1', '值2', …, '值n' )

与 ENUM 类型相同，SET 值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET 成员值的尾部空格将自动删除。

但与 ENUM 类型不同的是，ENUM 类型的字段只能从定义的列值中选择一个值插入，而 SET 类型的列可从定义的列值中选择多个字符的联合。

提示：如果插入 SET 字段中的列值有重复，则 MySQL 自动删除重复的值；插入 SET 字段的值的顺序并不重要，MySQL 会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。

5、二进制类型字符串

MySQL 支持两类字符型数据：文本字符串和二进制字符串。二进制字符串类型有时候也直接被称为“二进制类型”。

MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

下表中列出了 MySQL 中的二进制数据类型，括号中的 M 表示可以为其指定长度。

类型名称	说明	存储
BIT(M)	位字段类型	大约(M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY(M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1字节，在此，L<2⁸
BLOB (M)	小 BLOB	L+2 字节，在此，L<2¹⁶
MEDIUMBLOB(M)	中等大小的BLOB	L+3 字节，在此，L<2²⁴
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2³²

5.1、BIT 类型

位字段类型。M 表示每个值的位数，范围为 1～64。如果 M 被省略，默认值为 1。如果为 BIT(M) 列分配的值的长度小于 M 位，在值的左边用 0 填充。例如，为 BIT(6) 列分配一个值 b’101’，其效果与分配 b’000101’ 相同。

BIT 数据类型用来保存位字段值，例如以二进制的形式保存数据 13，13 的二进制形式为 1101，在这里需要位数至少为 4 位的 BIT 类型，即可以定义列类型为 BIT(4)。大于二进制 1111 的数据是不能插入 BIT(4) 类型的字段中的。

提示：默认情况下，MySQL 不可以插入超出该列允许范围的值，因而插入数据时要确保插入的值在指定的范围内。

5.2、BINARY 和 VARBINARY 类型

BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串。使用的语法格式如下：

列名称 BINARY(M) 或者 VARBINARY(M)

BINARY 类型的长度是固定的，指定长度后，不足最大长度的，将在它们右边填充 “\0” 补齐，以达到指定长度。
- 例如，指定列数据类型为 BINARY(3)，当插入 a 时，存储的内容实际为 “\a0\0”，当插入 ab 时，实际存储的内容为“ab\0”，无论存储的内容是否达到指定的长度，存储空间均为指定的值 M。
VARBINARY 类型的长度是可变的，指定好长度之后，长度可以在 0 到最大值之间。
- 例如，指定列数据类型为 VARBINARY(20)，如果插入的值长度只有 10，则实际存储空间为 10 加 1，实际占用的空间为字符串的实际长度加 1。

5.3、BLOB 类型

BLOB 是一个二进制的对象，用来存储可变数量的数据。BLOB 类型分为 4 种：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，它们可容纳值的最大长度不同，如下表所示：

数据类型	存储范围
TINYBLOB	最大长度为255 (28-1)字节
BLOB	最大长度为65535 (216-1)字节
MEDIUMBLOB	最大长度为16777215 (224-1)字节
LONGBLOB	最大长度为4294967295或4GB (231-1)字节

BLOB 列存储的是二进制字符串（字节字符串），TEXT 列存储的是非进制字符串（字符字符串）。

BLOB 列是字符集，并且排序和比较基于列值字节的数值；TEXT 列有一个字符集，并且根据字符集对值进行排序和比较。

6、JSON 类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。

在MySQL 5.7中，就已经支持JSON数据类型。在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效。创建数据表，表中包含一个JSON类型的字段 js 。

7、空间类型

MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如一个十字路口等等。MySQL中使用 Geometry（几何）来表示所有地理特征。Geometry指一个点或点的集合，代表世界上任何具有位置的事物。MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括：