mysql规范

数据库表设置规范

• 字符集一般选择utf8mb4（MySQL5.5.3以后支持），因为utf8mb4是utf8的超集并完全兼容utf8，能够用四个字节存储更多的字符
• 排序规则一般选择utf8mb4_general_ci，比较和排序的时候更快
• 一个项目一个数据库，多个项目慎用同一个数据库
• 表存储引擎使用InnoDB

数据库表命名规范

• 所有库名称必须使用小写字母并用下划线分割，库名与应用名称尽量一致

• 表名称必须使用小写字母或数字并用下划线分割，但是禁止出现数字开头，禁止两个下划线中间只出现数字，如：level_3_name

• 所有库表名称禁止使用MySQL保留关键字

  • 关键字：这类字在MySQL中具有特殊含义，例如常见的alter、static、cache
  • 保留字：就是MySQL自身保留的标识符。一般情况下是不允许使用的，例如select、insert等

• 库表的命名要能做到见名识意，并且最好不要超过32个字符，表的命名最好是加上“业务名称_表的作用”，如：alipay_task / force_project / trade_config

• 临时表必须以tmp为前缀并以日期为后缀，备份表必须以bak为前缀并以日期为后缀

• 用单数形式表示名称，例如，使用 employee，而不是 employees

字段设置规范

• 字段必须填写描述信息，也就是备注，枚举字段需要说明每个枚举值的意义

• 词语，数字存入字段中统一使用英文逗号作为分隔符，清洗好的句子或者段落统一使用\n作为分割符

• 小数类型为 decimal，禁止使用 float 和 double

• varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度大于此值，定义字段类型为 text，独立出来一张表，用主键来对应，避免影响其它字段索引效率

• 在能满足取值范围的情况下，优先选择占用存储空间最小的数据类型，如布尔值使用tinyint

• 主键索引名为 pk_字段名，唯一索引名为 uk_字段名，普通索引名则为idx_字段名

• 如果存储的字符串长度几乎相等，使用 char 定长字符串类型

• 字段初始尽量不设置为NULL，因为NULL字段的索引需要额外空间，但是不要把它当成优先的优化措施

  • 考虑字段类型的适当性，如timestamp类型
  • 具体的业务要求，如NULL代表未跑过的数据，''代表跑过但是没有符合业务要求的数据

• 关于timestamp与datetime的选择

  • timestamp 只占4个字节，而且是以utc的格式储存， 它会自动检索当前时区并进行转换，但是支持范围为1970-01-01 00:00:01~2038-01-19 03:14:07，一般使用场景如下：

    • 插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间（DEFAULT CURRENT_TIMESTAMP）
    • 更新记录时未指定具体时间数据则将该时间戳字段值设置为当前时间（ON UPDATE CURRENT_TIMESTAMP）

  • datetime 占8个字节，但是支持范围为1000-01-01 00:00:00~9999-12-31 23:59:59，而且在MySQL 5.6版本之后，也支持自动更新，用法与timestamp类似。

字段命名规范

• **[必要]**表必备三字段，id，insert_time，update_time
  • id 必为主键，类型为 bigint，单表时自增、步长为 1
  • insert_time为记录插入时间，update_time为记录最新更新时间，统一使用datetime类型，设置见上图
• 命名规则同表，ps：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写，因此，数据库名、 表名、字段名，都不允许出现任何大写字母，避免节外生枝
• 业务上具有唯一特性的字段，必须建成唯一索引，如果遇到多个字段的组合，建议先使用SimHash计算值，存储字段为sim_value
• 若该表有上游表
  • 所有存储相同数据的字段名和字段类型必须一致（不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低）
  • 需要保留上游表的溯源字段，如：上游表的id字段作为本表old_id字段或者引用上游表unique类型而且不变的字段如obs_path
  • 若该表需要根据上游表进行增量更新，保留上游表insert_time或者update_time（根据具体业务要求）中的值作为upstream_time字段
• 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 tinyint （1 表示是，0 表示否），如表达逻辑删除的字段名 is_deleted，1 表示删除，0 表示未删除

参考资料

mysql官方文档关于datetime与timestamp的说明

阿里巴巴Java开发手册（详尽版）1.4.0