软件工程之数据库规范

注意事项

当前规范适用于 Mysql 5.7及其以上版本

表约束

公共字段

每个表都得有以下属性

`id` bigint(1) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`status` tinyint(1) unsigned NOT NULL DEFAULT '200' COMMENT '状态(0:删除,100:下线,200:上线)',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

说明

• 它们是表的公共属性
• 它们不能作为业务数据
  • 如,涉及开始时间、最后一次某个属性更新时间,请新建业务字段
• 因 status 表达的是数据状态,属于数据逻辑。因此,它的枚举可以在 Dao 层
  • 对于 status 如果还有其他业务含义，共同表达当前数据是否可用，可以约定 status 控制展示的临界值，比如，审核是否通过等等，尽量收口到这一个字段
  • 展示数据的临界值可以设置为 200 大于等于200时，数据就可对外展示
  • 不过这样的话，每次 status 字段变化最好记录下，变化前后的状态值，及其变更的备注信息，到一张新表，方便数据异常时恢复状态值
• updated_at 不具备业务意义，理论上默认值可以自行设定，但是随着业务规模的增加，会有其他部门业务入侵，比如BI部门，可能会定期依据我们这个字段抽数据

字段约束

每个表都得受以下属性

• 每个字段必须 NOT NULL
  • NULL是需要一个标志位的，占用1个字符
• 每个字段必须有 默认值
• 每个字段必须有 字段备注
• 非负数字，必须 unsigned
  • 表达负数会占用一个符号位
  • 若没有 fill_zero 的限制，长度统一设置为1

MYSQL规约

索引命名规范

注：索引前缀、每个数据列名 之间依据以顺序 以 - 分隔

建表模板

CREATE TABLE `comic` (
  `id` bigint(1) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `related_id` int(1) unsigned NOT NULL DEFAULT '0' COMMENT '绑定的渠道id.表supplier.id',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '漫画名称',
  `pic` varchar(255) NOT NULL DEFAULT '' COMMENT '漫画封面',
  `intro` varchar(1000) NOT NULL DEFAULT '' COMMENT '漫画简介',
  `weight` int(1) unsigned NOT NULL DEFAULT '0' COMMENT '权重值.值越大,越靠前展示',
  `tag` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '标记。枚举值: 0:没有标记,1:热门,2:连载,3:完结',
  `method` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '枚举值(获取漫画详情的方式):0:未知,1:爬取时自动获取(每次),2:爬取时自动获取(仅限初始时),3:手动',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '100' COMMENT '状态(0:删除,100:下线,200:上线)',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT '1000-01-01 00:00:00' ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx-related_id-status` (`related_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='漫画基本信息'

分表原则

• 规则a
  • 单表数据量不超过 500W 行 或 容量不超过 2GB
    • 因为过大会造成修改表结构、备份、恢复都会有很大的问题
• 规则b
  • 预测未来三年内如果数据量会超过规则a，则考虑三年内应先分多少表
  • 如果未来数据量有可能远超当前预测规划的，提前想好对应方案
• 规则c
  • 分表数使用 2 的 N 次方
    • 加速分表运算、方便扩缩容
    • 计算数据落表所对应的表ID = 分表键 & (2^N - 1)
      • 请注意表ID从0开始
• TODO

分库原则

• TODO