MySQL中建表与常见的类型设计陷阱详解

本文作为MySQL系列第三篇文章，详细讲解了MySQL的建表语句、以及表结构的设计规范和陷阱，对网络上常见的资料给出的设计方案，做了博主自己的理解和反驳。

一、MySQL建表语句

MySQL建表语句很简单，CREATE TABLE 表名 ()，在其中设置表的列（属性）即可。

CREATE TABLE `表名`  (
    // 定义属性
    // 定义索引
) // 设置表属性;

二、MySQL建表字符串类型设计

MySQL 数据库的字符串类型有 CHAR、VARCHAR、BINARY、BLOB、TEXT、ENUM、SET，其中最常使用的是 CHAR、VARCHAR。

1、CHAR

CHAR(N) 用来保存固定长度的字符（在Unicode字符集，Utf-8、Utf-16、Utf-32是这样的），N 的范围是 0 ~ 255，请牢记，N 表示的是字符，而不是字节。

在表结构设计中还需要额外定义建表对应的字符集。多字节字符集 (MBCS)，通常指的是ANSI、中文编码以及Shift-jis，jis，euc-jp，euc-kr等。Unicode字符集，Unicode字符集即平常说的宽字节，包含Utf-8、Utf-16、Utf-32。

常见的字符集有 GBK、UTF8，通常推荐把默认字符集设置为 UTF8。

2、VARCHAR

VARCHAR(N) 用来保存变长字符，N 的范围为 0 ~ 65536， N 表示字符。在超出 65536 个字符的情况下，可以考虑使用更大的字符类型 TEXT 或 BLOB，两者最大存储长度为 4G，其区别是 BLOB 没有字符集属性，纯属二进制存储。

随着移动互联网的飞速发展，推荐把 MySQL 的默认字符集设置为 UTF8MB4，否则，某些 emoji 表情字符无法在 UTF8 字符集下存储。

MySQL 8.0 版本字符集默认设置成 UTF8MB4，UTF8MB4 字符集 1 个字符最大存储 4 个字节，8.0 版本之前默认的字符集为Latin1。

鉴于目前默认字符集推荐设置为 UTF8MB4，所以在表结构设计时，可以把 CHAR 全部用 VARCHAR 替换，底层存储的本质实现一模一样。

3、枚举类型设计实战

枚举类型设计

设计表结构时，你会遇到一些固定选项值的字段。例如状态字段（***_state），有效的值为有限状态，例如01（订单初始状态）、02（下单成功）、03（支付中）……。

很多学习资料和博客推荐在 MySQL 8.0 版本之前，可以使用 ENUM 字符串枚举类型，只允许有限的定义值插入。如果将参数 SQL_MODE 设置为严格模式，插入非定义数据就会报错。

这里博主要跟这些资料唱个反调，我们在工程中的状态，基本都是我们手动set的，这里博主认为如果使用了 ENUM 字符串枚举类型恰恰不利于互联网的高速扩展的设计原则。

在这里我推荐在工程中维护一个 ENUM 枚举类，我们对数据库操作的的时候状态或者相关枚举类型的字段从枚举类中获取，这样方便维护，并且利于扩展。

`TXN_TYPE` varchar(8) CHARACTER  NOT NULL COMMENT '交易类型|消费:SQT,退货:SQRT',

三、MySQL建表ID和金额的设计与实战

1、ID自增的设计

进行实战设计之前，我们需要了解整型类型，

MySQL 数据库支持 SQL 标准支持的整型类型：INT、SMALLINT、TINYINT、MEDIUMINT 和 BIGINT 整型类型。INT占用4字节，取值范围是-2147483648 ~ 2147483647（2^31），BIGINT占用8字节，-9223372036854775808 ~9223372036854775807（2^63）

除了整型类型，数字类型还有浮点和高精度类型。MySQL 之前的版本中存在浮点类型 Float 和 Double，在真实的生产环境中不推荐使用，在计算时由于精度类型问题，会导致最终的计算结果出错。

ID一般我们会设置为自增，结合 auto_increment，可以实现自增功能，但在表结构设计时用自增做主键一般只会使用 BIGINT 类型做主键。

`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',

原因有几点一是为了扩展性，int 的取值范围不一定适用互联网场景的增速，这里面需要注意MySQL 8.0 版本前自增不持久化，自增值可能会存在回溯问题，例如 1/2/3/4，我把4删点，再次插入的时候，主键ID还是 1/2/3/4，这就是回溯问题，解决办法就是在使用的时候评估这个方案会不会有影响，或者直接升级MySQL。

2、互联网企业金额字段设计原理

我们常常在其他博客看到这样一种说法“在海量互联网业务的设计标准中，并不推荐用 DECIMAL 类型，而是更推荐将 DECIMAL 转化为 整型 BIGINT类型。”，他给出的理由是所有金额相关字段都是定长字段，占用 8 个字节，存储高效。第二直接通过整型计算，效率更高。

而事实上真的是这样吗？

金额字段的取值范围如果用 DECIMAL 表示的，则定义为 DECIMAL(16,2) ，这样满足的万亿以上的场景了。

`TRANS_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子单交易金额',
`CASH_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子单现金金额',
`POINT_AMT` decimal(16, 0) NULL DEFAULT NULL COMMENT '子单积分金额',

为什么我推荐使用DECIMAL而不是BIGINT，我们在存储金额的时候一般是分为单位，例如100,.00就是 1 元，当我们下单金额例如100元，我们的库里就会落 10000.00，但是这比订单购买了1个item商品3件sku，这100元就要分摊给这3件sku商品，这时候对于分摊的计算，在代码中int、long类型没有BigDecimal 计算的精准。

四、MySQL建表时间类型设计与实战

MySQL 数据库中常见的日期类型有 YEAR、DATE、TIME、DATETIME、TIMESTAMEP。

因为业务绝大部分场景都需要将日期精确到秒，所以在表结构设计中，常见使用的日期类型为DATETIME 和 TIMESTAMP。

这里面TIMESTAMP有一个大坑，TIMESTAMP 其实际存储的内容为‘1970-01-01 00:00:00’到现在的毫秒数。在 MySQL 中，由于类型 TIMESTAMP 占用 4 个字节，因此其存储的时间上限只能到‘2038-01-19 03:14:07’。

我们工程中，生产环境等等一般使用的是DATETIME， DATETIME 最终展现的形式为：YYYY-MM-DD HH：MM：SS，固定占用 8 个字节。

从 MySQL 5.6 版本开始，DATETIME 类型支持毫秒，DATETIME(N) 中的 N 表示毫秒的精度。例如，DATETIME(6) 表示可以存储 6 位的毫秒值。同时，DATETIME 不存在时区转化问题。一般是在国际化项目中，服务器端或者前端进行转换，这样查询或者变更效率更高。

每个表都要有一个时间字段， 在做表结构设计规范时，强烈建议你每张业务核心表都增加一个 DATETIME 类型的 last_modify_date 字段，并设置修改自动更新机制， 即便标识每条记录最后修改的时间。开发人员可以知道每次操作记录更新的时间，以便做后续的处理。

`CREATE_TIME` datetime(0) NOT NULL COMMENT '创建时间',
`CREATE_BY` varchar(32) NOT NULL COMMENT ' 创建人',
`UPDATE_TIME` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
`UPDATE_BY` varchar(32) CHARACTER NOT NULL COMMENT '更新人',

比如在电商的订单表中，可以方便对支付超时的订单做处理；在金融业务中，可以根据用户资金最后的修改时间做相应的资金轧差等。

五、MySQL高扩展JSON设计与实战

关系型的结构化存储存在一定的弊端，因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中，或许需要扩展单个列的描述功能。

这时，如果能用好 JSON 数据类型，那就能打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择。JSON 类型的另一个好处是无须预定义字段，字段可以无限扩展。

`ITEM_INFO` JSON COMMENT '商品信息',

但是这里，博主并不推荐大家这么做，因为JSON类型及其难维护，并且写sql的时候很麻烦

我举个例子，我想插入一条信息，我需要

SET @item_info = '{
	"item_id" : "12345",
	"item_amt" : "1024.00"
}';

INSERT INTO 表名 VALUES ( , @item_info);

一般在生产中我们这样处理，在定义时，定义一个超大的字符串类型，在代码中使用JSON转换成一个JSON对象的字符串，保存。

`ITEM_INFO` varchar(1000) CHARACTER DEFAULT NULL COMMENT '商品信息',

总结

本文作为MySQL系列第三篇文章，详细讲解了MySQL的建表语句、以及表结构的设计规范和陷阱，对网络上常见的资料给出的设计方案，做了博主自己的理解和反驳。