数据库表设计规范 作者: truesnow 发布于: 5个月前 收录于: 开发规范 325 0 摘要:统一数据库表的命名规范。包括库名、表名、字段名和类型、索引等规范。 **数据库表设计规范** # 变更历史 | 日期 | 变更人 | 变更说明 | | ---- | -------- | --- | | 2019-12-25 | truesnow | 第一版 | # 目标 统一数据库表的命名规范。避免前端、测试、业务方等在对接不同后端开发人员开发的服务时,对相同、相似的概念使用不一样的名称,造成疑惑,进而增加对接难度。 # 规范 ## 数据库命名规范 - 使用 `部门或业务线名称 + 服务名 + _db` 命名数据库。 > 例如: - 支付中心:使用 `pay_` 为前缀 - 用户中心:使用 `user_` 为前缀 - 用户触达服务:`user_push_db` - 白名单服务:`user_white_list_db` - 活动中心:使用 `activity_` 为前缀 - 拼团服务: `activity_group_buy_db` - 特价促销服务:`activity_special_price_db` - 秒杀服务:`activity_seckill_db` ## 数据表命名规范 - 统一以 `t_` 作为表名前缀 - 表名以名词单数命名 > 如: - 活动表:`t_activity` - 订单表:`t_order` - 表名必须有注释 - 一般数据表必须为 InnoDB 类型,编码默认为 `utf8mb4` ```sql ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='表名'; ``` ## 索引规范 - 普通索引使用 `idx_` 前缀,后跟字段名,如 `idx_order_id`,如为联合索引,字段间加下划线,且按先后顺序书写(最左原则),如:`idx_type_name` - 唯一索引使用 `uniq_` 前缀 - 如大数据团队会同步表数据,需设置更新时间为索引 ```sql INDEX `idx_update_time` (`update_time`) ``` - 在 `varchar` 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度即可 > 说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达 90% 以上, 可以使用 `count(distinct left(列名, 索引长度))/count(*)` 的区分度来确定。 ## 字段规范 ### 字段命名规范 - 字段名必须是 `order_id` 格式,全小写,以 `_` 连接,若含数字,数字与单词连接在一起,如 `level3_name` - 禁止使用保留字命名,如 `desc`、`range`、`match`、`delayed` 等,参考:[MySQL 关键字和保留字](https://dev.mysql.com/doc/refman/8.0/en/keywords.html) - 字段以名词单数命名 - 字段必须有注释 - 主键 ID:命名为 `id` - 状态字段以 `_status` 为后缀,非真即假字段命名不要以 `is` 开头,使用 `_status` 后缀(避免自动生成的 POJO 生成类似 `isIsDeleted` 这种代码),如:`review_status`、`pay_status`、`use_status`、`del_status` - 时间相关字段以 `_time` 为后缀,如:`start_time`、`end_time`、`pay_time` - 操作人相关字段以 `_user` 为后缀,如:`create_user`、`offline_user` - 金额相关字段以 `_amount` 为后缀,如:`order_amount` - 价格相关字段以 `_price` 为后缀,如:`sale_price`、`cost_price` - 数量相关字段以 `_qty` (Quantity 简写)为后缀,如:`buy_qty`、`sold_qty` - 统计数量相关字段以 `_count` 为后缀 ### 字段注释规范 - 字段注释如有标点符号全部使用英文半角标点 - 枚举类型注释格式,在字段名称后面,枚举值使用小括号 `()` 包含,枚举值后面用冒号 `:` 与枚举名称分隔,每个枚举后面用分号 `;` 分隔,示例 :`审核状态(1:待提交;2:审核中;3:审核通过;4:审核驳回)` - 其他含有特殊说明的注释使用小括号 `()` 说明,如:`活动结束时间(时间戳)`、`告警通知邮箱(多个用,分隔)` - 如字段已废弃或未使用等特殊情况,在注释最前面使用中括号 `[]` 写明,如:`[已废弃]`、`[该字段暂不使用]` - 字段注释发生变更时必须提 SQL 单跟随服务上线(包括新增枚举值、状态废弃等情况) ### 字段类型规范 - 所有字段必须有 `NOT NULL DEFAULT` - 任何字段如果为非负数,必须是 `unsigned` > 反例:库存未设置 `unsigned`,库存可为负,商品超卖;订单金额未设置 `unsigned`,订单为负仍下单成功 - text 类型无法设置 `NOT NULL`,但在程序中应默认写入空字符串 - 枚举值字段定义为 `tinyint(4) UNSIGNED` 类型 - 接口中会作为查询参数的字段:不能以 `0` 作为枚举起始值,`0` 只能作为字段默认值 > 理由是与业务或前端交互时,例如,在 PB 中,即使前端未传 status 字段,PB 也会将参数 status 值设为默认值 0,如果此时 status 为 0 有实际意义,那么这里查询就有歧义了,无法查询所有状态,而如果 status 为 0 无意义,后端可判断 status 为 0 时不按 status 搜索。 - 接口中不会使用到的字段,只在服务内部交互的非真即假字段,使用 0、1 作为枚举值 > 如:删除状态字段 `del_status`,0 表示未删除,1 表示已删除,前端与接口中不会按删除状态查询;库存释放状态字段 `stock_release_status`,0 表示未释放,1 表示已释放,这个状态是服务内部标识字段,只有服务内部会使用,前端与接口不会关心。 - 时间字段 - 除创建时间、更新时间使用 `timestamp` 类型,其余时间类型定义为 `int(11) UNSIGNED`,如精度为微秒定义为 `bigint(20) UNSIGNED`,代码中定义为 `Long` 类型。 > 如:活动开始时间、活动结束时间等,因为所有字段都必须为 NOT NULL,当时间字段为非必填时,timestamp 不好设置默认值。例如,下线操作需记录下线时间,数据创建时下线时间应为空,如为 timestamp 类型则不得不设置一个默认值,而设置为 int 类型,则可设置其默认值为 0。 - 当 `varchar` 长度超过 4096,定义字段类型为 `text`,独立出来一张表,用主键来对应,避免影响其它字段索引效率 ### 字段设计规范 - 字段允许适当冗余,以提高查询性能,但必须考虑数据一致。冗余字段应遵循: 1)不是频繁修改的字段 2)不是 `varchar` 超长字段,更不能是 `text` 字段 > 正例:商品类目名称使用频率高,字段长度短,名称基本一成不变,可在相关联的表中冗余存储类目名称,避免关联查询。 ### 公共字段定义 即每个表都应设置的字段,包括: - 主键:主键使用雪花算法 ```sql `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增ID', PRIMARY KEY (`id`), ``` - 删除状态 ```sql `del_status` tinyint(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT '删除状态(0:未删除;1:已删除)', ``` - 创建时间 ```sql `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', ``` - 更新时间 ```sql `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', ``` 示例: ```sql CREATE TABLE `t_activity` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID', `name` varchar(128) NOT NULL DEFAULT '' COMMENT '活动名称', `del_status` tinyint(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT '删除状态(0:未删除,1:已删除)', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='活动表'; ``` ### 常用字段定义 - 时间类型 ```sql `start_time` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '活动开始时间', `end_time` int(11) UNSIGNED NOT NULL DEFAULT '0' COMMENT '活动结束时间', ``` - 排序值 ```sql `ordinal` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '排序值', ```
所有评论(0)
暂无评论~_~