MySQL数据库设计规范

数据库创建

  • CREATE DATABASE test_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT ENCRYPTION='N' ;

建表语句参考

CREATE TABLE `corp_user` (
  `id` BIGINT  NOT NULL  COMMENT 'id(必须)',
  `user_id` BIGINT NOT NULL COMMENT '员工id',
  `user_status` VARCHAR(50) DEFAULT NULL COMMENT '员工状态',
  `merchant_id` INT DEFAULT '0' COMMENT '商户号（选填字段）',
  `plateform_type` VARCHAR(100) DEFAULT 'young_eagle' COMMENT '所属系统平台code，用于多平台支持（小鹰/本地呗/等等）（选填字段）',
  `system_code` VARCHAR(100) DEFAULT 'op' COMMENT '所属系统系统code，用于多系统支持（op/mp）（选填字段）',
  `revision` INT  COMMENT '乐观锁（选填字段）' ,
  `tenant_id`  INT DEFAULT '0' COMMENT '租户号，用户多租户逻辑处理（选填字段）',
  `remark` VARCHAR(1000) DEFAULT NULL COMMENT '备注(必须)',
  `create_name` VARCHAR(100) DEFAULT NULL COMMENT '创建者(必须)',
  `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间(必须)',
  `update_name` VARCHAR(100) DEFAULT NULL COMMENT '更新者(必须)',
  `update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间(必须)',
  `is_deleted` TINYINT(1) DEFAULT '0' COMMENT '是否删除(必须，0：正常 1：刪除)，逻辑删除，禁止用于其他状态列使用',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=INNODB  DEFAULT CHARSET=utf8mb4  COLLATE utf8mb4_general_ci  COMMENT='商户员工表';

注意事项

  1. 表格
    • 创建表语句需要包含库名注释，字段注释
    • 表名前写上库名(包含create table、create index 、alter、insert、update、delete...)，表、字段有注释、非特殊需要字段定义成not null,字符串类型可以定义成not null default ''
    • 存储引擎选择InnoDB
    • 主键
  2. 必须有主键，选择自增列(int/bigint unsigned)作为主键
  3. 如果无特殊需求，新表自增主键起始值(auto_increment)不要带在创建表脚本中
    • 创建表语句 create_time、update_time、create_name、update_name、is_deleted 字段类型需规范，需要索引
    • 日志类的表可以不加update_time、is_deleted 字段
  4. 字段
    • 字段禁止使用enum、set类型。替换为采用tinyint/varchar类型+注释方式
    • 表示时间的字段，禁止使用timestamp、int/bigint 类型表示时间一律采用datetime类型
  5. 采用datetime代替timestamp 5.6.4之前的版本建议采用TIMESTAMP，5.6.4之后建议采用DATETIME
  6. timestamp有时间范围限制,1970-01-01 00:00:00 UTC到2038-01-19 03:14:07UTC
    • 采用decimal(m,n) 代替float、double类型
    • 相同字段类型支持扩容（varchar(10) -> varchar(64)；tinyint -> int →bigint）
    • enum类型扩容，枚举值需要跟线上的顺序保持一致
    • int(1) int(5) int(11)表示的范围是一样的，结合zerofill定义才有意义
    • 状态、类型、性别等可以采用tinyint
    • IP、手机号 可以采用bigint方式存储。相关函数 (ip→数值inet_aton() 数值→ip inet_ntoa())
    • BLOB/CLOB/TEXT等大字段尽可能拆分出单独的表，采用主键关联
    • 字段长度按需要分配
    • 字段不要采用MySQL关键字(order、type、timestamp...)
  7. 整型范围 https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
  8. 存储空间使用 https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html
  9. MySQL关键字https://dev.mysql.com/doc/refman/8.0/en/keywords.html
  10. 索引
    • 逻辑关联ID需要加索引
    • 普通索引：KEY idx_hotel_id (hotel_id)
    • 普通联合索引：KEY idx_dh_id (create_date,hotel_id)
    • 前缀索引：字符串长度超过30，采用前缀索引，KEY idx_app_id (app_id(6))
    • 唯一索引：UNIQUE KEY uniq_bo_id (business,order_id)
  11. 禁止
    • 禁止创建 触发器、存储过程、自定义函数
    • 禁止创建表时采用外键(foreign key)
    • 禁止字段缩容 如（varchar(64) -> varchar(10)；bigint -> int；enum(3个值) -> enum(2个值) #原有值会变成空字符串(与sql_mode有关系)）
    • 禁止字段类型调整 如（enum -> varchar；数值类型 字符类型转换）
    • 禁止删除字段，删除表，更改表名
    • 禁止使用is_deleted列作为业务状态列使用，is_deleted=1等同于物理删除

results matching ""

    No results matching ""