MySQL DDL

摘要

MySQL生成表有两种方式，一种是通过工具，另一种是手写DDL（Data Definition Language）。通过工具设计表结构通常比较简单，这里我分享一个较好得DDL，分析点主要包括：

• 公共字段放哪些合适？
• 字段该选用哪种类型和类型的取值范围如何确定？
• DDL中的关键字都代表了什么？

DDL展示

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名称',
  `nickname` varchar(500) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '昵称',
  `store` json DEFAULT NULL COMMENT '说明: ["KEY","VALUE"]',
  `deleted` tinyint(2) unsigned DEFAULT '0' COMMENT '逻辑删除：0正常，1删除',
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4399 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

1、公共字段放哪些合适？

在每个表中，通常都会有创建时间(created_time)和更新时间(updated_time)，deleted是代表逻辑删除，其中当值为0是表示正常，1表示被删除。
在有的项目中，通常公共字段还会有创建人(created_user)和更新人(updated_user)，这俩个字段在业务表结构中几乎不会被使用到，但是在权限管理系统所用的表结构中，会非常有用，他会追踪权限的修改路径来找原因。

这里时间处理上有一个细节需要注意，由于timestamp的时间区间在1970-01-01 00:00:01.000000 到 2038-01-19 03:14:07.999999，系统是成长的系统，谁都不想给系统埋一个定时炸弹，再说，timestamp会因为时区的问题扰乱系统时间行为（系统默认服务大陆，国际化的系统忽略此点），为了避免此些问题，转而使用datetime来记录创建时间和更新时间。

当记录新增时，updated_time初始值为null，代表了此条记录只有一个新增，没有进行其它更新操作了。

datetime和timestamp的初始值及更新问题官方说明

2、字段该选用哪种类型和类型的取值范围如何确定？

• 数据/数值类型
  整数类型TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT、浮点小数据类型FLOAT和DOUBLE、定点小数类型DECIMAL。
• 日期/时间类型
  YEAR、TIME、DATE、DATETIME和TIMESTAMP。
• 字符串类型
  CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET等。
• 对象
  JSON。

1. 数据/数值类型

类型名称	存储需求	有符号	无符号	说明
TINYINT	1 byte	-128~127	0~255	很小的整数
SMALLINT	2 bytes	32768~32767	0~65535	小的整数
MEDIUMINT	3 bytes	-8388608~8388607	0~16777215	中等大小的整数
INT	4 bytes	-2147483648 ~ 2147483647	0~4294967295	普通大小的整数
BIGINT	8 bytes	-9223372036854775808~9223372036854775807	0~18446744073709551615	大整数

2. 浮点数与定点数类型

类型名称	存储需求	说明
FLOAT	4 bytes	单精度浮点数
DOUBLE	8 bytes	双精度浮点数
DECIMAL(M, D)	(M+2) bytes	压缩的“严格”定点数

2. 日期与时间类型

类型名称	存储需求	日期格式	说明
YEAR	1 byte	YYYY	年
TIME	3 bytes	HH:MM:SS	时间信息
DATE	3 bytes	YYYY-MM-DD	日期值
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS

3、DDL中的关键字都代表了什么？

• unsigned
  UNSIGNED属性就是将数字类型无符号化，当整数类型的字段后被unsigned修饰时，它的组织范围会从0开始，具体如上表格（无符号）所示。