基于AI生成的MySQL建表SOP

2024年 3月 17日数据运维泡泡

简介

希望通过一套SOP，提高MySQL建表效率，降低出错的可能性。使用填表+AI的形式，可以省去编写MySQL建表脚本的过程。

SOP

MySQL建表

表属性	值
表名	user
引擎	InnoDB
字符集	utf8
表注释	用户表

字段名	数据类型	是否允许为空	默认值	注释
id	unsigned bigint	NOT NULL	AUTO_INCREMENT	主键
user_name	varchar(50)	NOT NULL	DEFAULT ”	用户名
user_rank	unsigned int	NOT NULL	DEFAULT ‘0’	用户等级
gmt_create	date_time	NOT NULL	CURRENT_TIMESTAMP	创建时间
gmt_modified	date_time	NOT NULL	CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	更新时间

字段名	索引名	索引类型	是否为联合索引
user_rank	idx_user_user_rank	普通索引	否

将以上三张表丢给AI，即可生成如下建表脚本

CREATE TABLE `user` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `user_name` varchar(50) NOT NULL DEFAULT '' COMMENT '用户名',
  `user_rank` int unsigned NOT NULL DEFAULT '0' COMMENT '用户等级',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_user_user_rank` (`user_rank`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

建表规约

引擎统一使用InnoDB或与旧表相同

字符集统一使用utf8或与旧表相同

所有字段都应为NOT NULL

所有字段都应有默认值

字符类型字段使用varchar

数字类型字段参考下表

对象	年龄区间	类型	字节
人	150岁之内	unsigned tinyint	1
龟	数百岁	unsigned smallint	2
恐龙化石	数千万岁	unsigned int	4
太阳	约50亿年	unsigned bigint	8

所有表应至少包含id、gmt_create、gmt_modified三个字段

notes/MySQL/MySQL数据库开发的三十六条军规.md at master · guanguans/notes (github.com)

p3c/p3c-gitbook/MySQL数据库/建表规约.md at master · alibaba/p3c (github.com)

MySQL 时区全解，datetime 和 timestamp 区别，GMT、UTC、CST、东八区分别指什么？ | 老郭种树 (guozh.net)

应用

资源表

资源检索系统中，图片、视频等资源以url的形式存储在数据库中，并拥有类型、格式等属性。

表属性	值
表名	resource
引擎	InnoDB
字符集	utf8
表注释	资源表

字段名	数据类型	是否允许为空	默认值	注释
id	unsigned bigint	NOT NULL	AUTO_INCREMENT	主键
name	VARCHAR(100)	NOT NULL	DEFAULT ”	名称
url	VARCHAR(6000)	NOT NULL	DEFAULT ”	URL
type	unsigned tinyint	NOT NULL	DEFAULT ‘0’	类型。0表示文字、1表示图片、2表示视频
format	unsigned smallint	NOT NULL	DEFAULT ‘0’	格式。
length	unsigned int	NOT NULL	DEFAULT ‘0’	长度单位：像素
width	unsigned int	NOT NULL	DEFAULT ‘0’	宽度。单位：像素
size	unsigned bigint	NOT NULL	DEFAULT ‘0’	大小。单位：kb
duration	unsigned bigint	NOT NULL	DEFAULT ‘0’	持续时间。单位：ms
user_id	unsigned bigint	NOT NULL	DEFAULT ‘0’	用户id
status	unsigned tinyint	NOT NULL	DEFAULT ‘0’	状态
gmt_create	date_time	NOT NULL	CURRENT_TIMESTAMP	创建时间
gmt_modified	date_time	NOT NULL	CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	更新时间

字段名	索引名	索引类型	是否为联合索引
user_id	idx_resource_user_id	普通索引	否

单元表

一个单元下拥有多个资源

表属性	值
表名	unit
引擎	InnoDB
字符集	utf8
表注释	单元表

字段名	数据类型	是否允许为空	默认值	注释
id	unsigned bigint	NOT NULL	AUTO_INCREMENT	主键
name	varchar(100)	NOT NULL	DEFAULT ”	名称
status	unsigned tinyint	NOT NULL	DEFAULT ‘0’	状态
gmt_create	date_time	NOT NULL	CURRENT_TIMESTAMP	创建时间
gmt_modified	date_time	NOT NULL	CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	更新时间

单元资源表

单元表与资源表间的关系表

表属性	值
表名	unit_resouce
引擎	InnoDB
字符集	utf8
表注释	单元资源表

字段名	数据类型	是否允许为空	默认值	注释
id	unsigned bigint	NOT NULL	AUTO_INCREMENT	主键
unit_id	unsigned bigint	NOT NULL	DEFAULT ‘0’	单元id
resource_id	unsigned bigint	NOT NULL	DEFAULT ‘0’	资源id
status	unsigned tinyint	NOT NULL	DEFAULT ‘0’	状态
gmt_create	date_time	NOT NULL	CURRENT_TIMESTAMP	创建时间
gmt_modified	date_time	NOT NULL	CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	更新时间

字段名	索引名	索引类型	是否为联合索引
unit_id	idx_unit_resouce_unit_id	普通索引	否

地区表

一个单元有地区特性

表属性	值
表名	unit_district
引擎	InnoDB
字符集	utf8
表注释	单元地区表

字段名	数据类型	是否允许为空	默认值	注释
id	unsigned bigint	NOT NULL	AUTO_INCREMENT	主键
unit_id	unsigned bigint	NOT NULL	DEFAULT ‘0’	单元id
province	varchar(100)	NOT NULL	DEFAULT ”	省份
city	varchar(100)	NOT NULL	DEFAULT ”	城市
status	unsigned tinyint	NOT NULL	DEFAULT ‘0’	状态
gmt_create	date_time	NOT NULL	CURRENT_TIMESTAMP	创建时间
gmt_modified	date_time	NOT NULL	CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	更新时间

字段名	索引名	索引类型	是否为联合索引
unit_id	idx_unit_district_unit_id	普通索引	否

关键词

一个单元有关键词特性

表属性	值
表名	unit_keyword
引擎	InnoDB
字符集	utf8
表注释	单元关键词表

字段名	数据类型	是否允许为空	默认值	注释
id	unsigned bigint	NOT NULL	AUTO_INCREMENT	主键
unit_id	unsigned bigint	NOT NULL	DEFAULT ‘0’	单元id
keyword	varchar(100)	NOT NULL	DEFAULT ”	关键词
status	unsigned tinyint	NOT NULL	DEFAULT ‘0’	状态
gmt_create	date_time	NOT NULL	CURRENT_TIMESTAMP	创建时间
gmt_modified	date_time	NOT NULL	CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	更新时间