数据库DDL变更规范

1、 创建表的案例   表名和字段名 不允许使用 双引号 标记字段名、DEFAULT、和comment部分。

其中，created_at 和 updated_at 为必须字段，方便数据仓库做增量同步以及根据时间维度的计算

CREATE TABLE check_off_list (

  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  kdt_id bigint(20) unsigned NOT NULL DEFAULT '0' comment '店铺id',

  alias_name varchar(15) not null default '店铺别名',

  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间',

  updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP comment '修改时间',

  PRIMARY KEY (id),

  UNIQUE KEY uniq_kdt_id (kdt_id),

  key  idx_aliasname(alias_name)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT '表级别注释';

  

考虑的不同业务对时间的要求不同，时间的默认值还可以是 '1970-01-01 08:00:00'

CREATE TABLE check_off_list (

  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  kdt_id bigint(20) unsigned NOT NULL DEFAULT '0' comment '店铺id',

  alias_name varchar(15) not null default '店铺别名',

  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间',

  updated_at datetime NOT NULL DEFAULT '1970-01-01 08:00:00' comment '修改时间',

  PRIMARY KEY (id),

  UNIQUE KEY uniq_kdt_id (kdt_id),

  key  idx_aliasname(alias_name)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT '表级别注释';

2、 修改表的案例 禁止将对同一个表的变更拆分为多条ddl 拆开使用

alter table check_off_list add column user_name varchar(64) not null default '' comment '用户名称'；

alter table check_off_list add column shop_name varchar(64) not null default '' comment '用户名称';

alter table check_off_list add key idx_uname(user_name);

正确的写法 修改表的变更 添加字段和索引

alter table check_off_list add column user_name varchar(64) not null default '' comment '用户名称',

add column shop_name varchar(64) not null default '' comment '用户名称',

add key idx_uname(user_name),drop key idx_no_used;

3、 表和字段规范

• 不能和MySQL系统关键字冲突，且一律小写。 
• 表名不要超过32个字符，采用英文单词，或者英文单词缩写+数字的格式，不得在表名中留空格。
• 表名，采用使用业务命名，关联表, 以 re_ 开头。示例:  cpc_ad  order  ump  cps_shop  cps_website  goods  address  books  book。
• 字段名 示例: tweet_id  user_id。

• 注释用COMMENT关键字, 并把含义(表/列), 写清楚创建人/含义等。

• 使用utf8mb4 作为默认字符集。
• 表的引擎为innodb。   

4、字段类型

• 涉及到主键、消息、商品、物流、订单、金额、用户ID、粉丝ID、店铺ID（ktd_id）统一采用BIGINT。
• 禁止字段默认可为NULL ，同时NOT NULL必须添加DEFAULT值，可以使用default ''  ,default 0 等无明确业务含义的值代替， 因为NULL字段的索引很难优化且会占额外的空间。
  例子  alias_name varchar(15) not null default '店铺别名'

• 禁止 TEXT/BLOB 字段类型，对于大字段一定要使用压缩完之后，再存储到MySQL中

• 字符串一般统一用varchar(N)，N要尽可能的小，避免排序和创建临时表的时候消耗大量内存，不用char，无特殊情况不需要单独声明字符集，默认采用表的字符集。

• 标志类列， 统一使用TINYINT，不用char/enum，是否用UNSIGNED由业务决定

• 对字符串类型有大小写敏感需求的, 请使用 COLLATE utf8mb4_bin

5、索引  

• 索引的命名采用idx_cola_colb ，如果字段名称太长，可以采用缩写 idx_ca_cb，如果是唯一索引则用uniq开头，uniq_cola_colb
• 尽量使用复合索引满足更多的查询条件。

• 表一定要有自增主键，字段名为ID，主键不得有业务含义，禁用联合主键，禁用字符串做主键。

• 避免重复索引，如(a),(a,b)(a,b,c)就是重复索引，一个(a,b,c)组合索引就满足了。
• 使用选择性高的列作为组合索引的前缀索引，count(distinct col)/count(*)，越接近1，选择度越高，一般不低于0.1
• 索引长度776个字节，utf8mb4 字符集，只能索引191个字符长度。

• 不使用全文索引