Skip to content

MySQL 规范

标签
开发/数据/数据库
计算机/数据库/mysql
规范
字数
1706 字
阅读时间
7 分钟

MySQL 编写规范

  • 强制】使用大写关键字

  • 强制】使用 COUNT(*),其为 SQL92 定义的标准统计行数语法

  • 强制COUNT(DISTINCT col) 计算该列出 NULL 之外的不重复行数,注 COUNT(DISTINCT col1, col2) 如果其中一列全为 NULL,那么即使另一列有不同的值,也返回为 0。

  • 强制】当某一列的值全是 NULL 时, COUNT(col) 的返回结果为 0,但 SUM(col) 的返回结果为 NULL,因此使用 SUM() 时需注意 NPE 问题。

sql
    -- 正例
    SELECT IF(ISNULL(SUM(col)), 0, SUM(col)) FROM table;
  • 强制】使用 ISNULL() 来判断是否为 NULL 值。注意: NULL 与任何值的直接比较都为 NULL。
sql
    NULL <> NULL -- NULL
    NULL = NULL -- NULL
    NULL <> 1 -- NULL
  • 强制】使用 <> 作为“不等于”比较符。

  • 强制】在代码中写分页查询逻辑时,若 count 为 0 应直接返回,避免执行后面的分页语句。

  • 强制】不得使用外键与级联,一切外键概念必须在应用层解决。

    说明: 主键和外键:学生表中的 student_id 是主键,那么成绩表中的 student_id 则为外键。 级联更新:如果更新学生表中的 student_id,同时触发成绩表中的 student_id 更新,则为级联更新。

    原因: 外键与级联更新适用于单机低并发,不适合分布式、高并发集群; 级联更新是强阻塞,存在数据库更新风暴的风险; 外键影响数据库的插入速度。

  • 强制】禁止使用存储过程,存储过程难以调试和扩展,更没有移植性。

  • 强制】数据订正时,删除和修改记录时,要先 SELECT,避免出现误删除,确认无误才能执行更新语句。 例外:如果需要对某个值进行加减计算,可以直接 UPDATE 并必须带上相关的 WHERE 限制

  • 特别强制】查询语句至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。(这里是指 explain 查询语句时的 type 值)

  • 【推荐】IN 操作能避免则避免,若实在避免不了,需要仔细评估 IN 后边的集合元素数量,控制在 1000 个之内 。

  • 【参考】 如果有全球化需要,使用字符集 utf-8 编码。并使用下列两种排序规则:

    • 不区分大小写:utf8_general_ci

    • 区分大小写:utf8_bin

      注意字符统计函数

sql
    SELECT LENGTH("轻松工作"); -- 返回 12
    SELECT CHARACTER_LENGTH("轻松工作"); -- 返回 4

如果要使用表情,那么使用 utfmb4 来进行存储,注意它与 utf-8 编码的区别。

如果需要区分大小写查询,请使用 utf8_bin

  • 【参考】 TRUNCATE TABLEDELETE 速度快,且使用的系统和事务日志资源少。但 TRUNCATE 无事务且不触发 trigger,有可能造成事故,故不建议在开发代码中使用此语句。 说明: TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

建库规范

为了避免同一个实例(连接)下撞库名的问题,库名需要按数据库连接名称(实例名称)添加前缀。

建表规范

字段命名

所有数据表需要拥有创建时间和修改时间两个字段,若表中记录有软删除需求,创建删除时间字段,当删除时间字段值不为 0 时视作软删除。也可以遵循 Gorm 默认约定的 deleted_at IS NOT NULL 规则,不过需要注意的是,该情况下必须给 deleted_at 配置索引。

库中记录时间字段类型使用 datetime 或是 bigint(20),字段名称使用 <操作的过去式>_at 进行命名,如 created_atupdated_atdeleted_at。对于用户 ID 字段,若不为主键(id),则字段名使用 user_id,若需要「过期时间」或者「开始时间」字段,应遵循 <发生动作的一般现在时>_at 进行命名,比如过期对应 expire,则字段就应该命名为 expires_at;开始对应 starts,则字段就应该命名为 starts_at

字段类型

idcreated_atupdated_at 字段类型需为 bigint(20),一律不使用 unsigned,其他等同 id 或时间戳的字段也需如此。存储 IP 地址的字段为了支持 IPv6,需要设置其结构为 varchar(50) NOT NULL。所有字段若不为唯一索引,则都需要设置为 NOT NULL,字符型默认值一般为为空字符串,数字型一般设置为 0

字段顺序

建表时字段先后顺序一般为 id、创建时间、更新时间,删除时间(若有),具体业务相关字段排在后面。若之后添加字段也遵循末尾添加原则,避免影响上下游使用(如 DTS 的数据订阅)

索引

创建数据表时,需要根据需求添加相关索引。表中的 updated_at 字段必须添加索引(方便日后导数据)。 所有索引名均使用小写,普通索引名采用 idx_ 开头,并跟上字段名小写(省略 _)。例如:为字段 user_id 建立普通索引,索引名:idx_userid,唯一索引(UNIQUE)使用 uk_ 开头,并跟上字段名小写(省略 _)。例如:为字段 user_id 建立普通索引,索引名:uk_userid。若对多个字段建立联合索引,则依次跟上相应的字段名,例如:为 user_idupdated_at 建立联合索引,则索引名为:idx_userid_updated_at

表名

建表表名,一般性业务以自有前缀开头。比如:

  • 数字资产可以是 da_ 开头
  • 用户以 user_ 开头
  • 统计以 statistics_ 开头
  • 临时表以 temp_ 开头(需注明何时删除)
  • 复制的表以 copy_ 开头并以 日期时间 符合 202001011201 格式的 _<日期时间> 结尾。(如 test 表复制多个使用 copy_test_202001011200copy_test_202001011201 等等)

表赋权

在 PR 中书写 SQL 时,对表结构的修改需要加上赋权语句,如:

sql
GRANT INSERT, DELETE, UPDATE, SELECT ON <表名> to <用户>

贡献者

文件历史

撰写