MySQL表级别DDL艺术性

背 景

  MySQL因为稳定性，开源，免费等特性，在处理中小型数据的场景下深受大家的喜爱，但是平时大家都很忙，建表等操作都比较随意，这里就整理下相对比较规范的MySQL表级别的DDL操作，当然如果你还没有一个MySQL，你也可以看看博客Linux(CentOS-7)下安装MySQL-5.7.30;

数据类型

• 数值类型 存储整数，浮点数的数据类型，MySQL没有Boolean类型，可用整形代替，通常用TINYINT较为合理，值1代表true，0代表false，具体如表1；

  注意：DECIMAL(M,D)中D值的是小数部分的位数，若插入的值未指定小数部分或者小数部分不足D位则会自动补到D位小数，若插入的值小数部分超过了D为则会发生截断，截取前D位小数(四舍五入截取)。M值得是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过M-D位，否则不能成功插入，会报超出范围的错误。M是是只的最大精度数位1-65，D是小数点右侧数位0-30；

• 日期和时间类型: 存储时间日期相关的数据，具体如表2；

  注意：TIMESTAMP 类型常用于指定updatetime时间，如建表时指定update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 那么只要该表的内容发生变化，该update_time字段会自动获取当前时间来作为更新时间，不需要任何外界的值填入，是不是很棒的一个功能；

• 字符串类型 : 存储字符相关的数据，具体如表3，一般情况下，都只会用到CHAR和VARCHAR类型，具体如表3；

建 表

  以下是一个MySQL规范建表的样例；

-- 支持发布建表语句re-run容错机制
DROP TABLE  IF  EXISTS `cfg_azkaban_flow_info`;
-- 建表
CREATE TABLE IF NOT EXISTS `cfg_azkaban_flow_info` (
  `flow_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '自增的azkaban的flow_id',
   `flow_name` VARCHAR(100)  CHARACTER SET Latin1 COLLATE latin1_swedish_ci NOT NULL COMMENT 'azkaban的flow_name',
  `data_set` VARCHAR(200)  NOT NULL COMMENT '该flow隶属的数据集版本名字',
  `project_name` VARCHAR(100)  NOT NULL COMMENT '该flow所属的项目名字',
  `parents_flow_id` BIGINT(20) NOT NULL COMMENT '依赖的父级flow_id',
  `route_flag` VARCHAR(500)  NOT NULL COMMENT '该flow所属数据管道路线标签，存在一个flow所属多个，用逗号隔开',
  `pre_end_type` VARCHAR(50)  NOT NULL COMMENT '预估计本该结束的周期，D每天，W1每周一，M1每月1，（M-1）每月倒数第一',
  `pre_end_time` VARCHAR(50)  NOT NULL COMMENT '预估计本该结束的时间',
  `spend_time` DECIMAL(18,9) COMMENT '耗时(秒)',
  `is_enable` INT(11) NOT NULL COMMENT '是否有效',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_user` VARCHAR(100)  NOT NULL COMMENT '创建人',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `update_user` VARCHAR(100)  NOT NULL COMMENT '修改人',
  PRIMARY KEY (`flow_id`),
  UNIQUE KEY `uk_flow_name` (`flow_name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='azkaban数据流信息表'
;

AUTO_INCREMENT :MySQL以1位种子，+1位间隔的自增列，自增列必须是主键；
COMMENT ：分为列的COMMENT和表的COMMENT，都是对列或者表进行阐述的作用，最好要求必填
DEFAULT CURRENT_TIMESTAMP：设置默认值，一般创建时间create_time的默认值设置为当前时间
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP：设置更新时间自动化触发，当表内字段发生更新时，会自动获取当前时间为更新时间；
PRIMARY KEY (flow_id)：设置自增列为主键;
UNIQUE KEY uk_flow_name (flow_name):有的时候也需要保证某些值的唯一性，当表内出现同一个值时，你希望得到报错提示，需要建立唯一性约束，唯一性约束会自动生成唯一性索引，其中UNIQUE KEY为必要关键字，uk_flow_name 为唯一性约束和唯一性索引名字,flow_name为唯一性约束的字段，如果是多个字段在一起才能唯一性约束，如创建日期加流名字，则只需要在括号内用逗号隔开字段，如UNIQUE KEY uk_flow_name_ create_time (flow_name,create_time);
ENGINE=INNODB:MySQL引擎种类有很多，如InnoDB,MyISAM和Memory等，这里不做特别说明，后续我们可以深入聊聊，总之，一般要支持事务，频繁更新，外键，自增列，索引都要用InnoDB，是最常用的引擎；
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci：设置默认的字符集和排序顺序规则，常用的字符集utf8和utf8mb4两种编码，在mysql中请大家忘记utf8，永远使用utf8mb4。这是mysql的一个遗留问题，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行,例如，utf8mb4能识别字段内① 和1的区别，会认为是两个不同的字符,而utf8搞不定，认为是同一个，般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8mb4编码的默认值为utf8mb4_general_ci，而表不是最细粒度的字符集设置对象，字符集设置可以精确到列，即如果列没指定，则选用表级别默认的字符集及排序规则，列自己指定了，该列就会优先选用自己指定的字符集和排序规则，如列flow_name;

根据原有表建表

  根据以后表建表一般有两种操作；

1. CREATE TABLE IF NOT EXISTS 新建的表 LIKE 存在的表 ;:单纯复制表结构，表的主键，索引等也会一起建立，但是表内的数据不会带过去,如果需要数据，需要另外Insert into进来；
2. CREATE TABLE IF NOT EXISTS 新表 AS SELECT * FROM 旧表:表内的数据会带过来，但是表的主键，索引等不会在新表内出现，是个无任何约束的表，如果需要主键等约束以及索引，需要自己重新建立；

  实现操作举例如下；

-- like操作建表，单纯复制表结构，表的主键，索引等也会一起建立，但是表内的数据不会带过去；
CREATE TABLE IF NOT EXISTS cfg_azkaban_flow_info_like LIKE cfg_azkaban_flow_info;

-- as操作建表，表内的数据会带过来，但是表的主键，索引等不会在新表内出现，是个无任何约束的表
CREATE TABLE IF NOT EXISTS cfg_azkaban_flow_info_as AS SELECT * FROM  cfg_azkaban_flow_info;

新增字段

  模板，添加多列，每列都要带关键字ADD COLUMN,每列之间用,隔开，最后一列用;结尾；

ALTER TABLE 表名
ADD COLUMN 列名1 类型1 字符集1 是否为null COMMENT '' ,
ADD COLUMN 列名2 类型2 字符集2 是否为null COMMENT '' ;

  具体实现如下；

ALTER TABLE cfg_azkaban_flow_info 
ADD COLUMN `suit_code` VARCHAR(200) CHARACTER SET utf8 DEFAULT NULL COMMENT '二级套装code',
ADD COLUMN`suit_name` VARCHAR(200) CHARACTER SET utf8 DEFAULT NULL COMMENT '二级套装';

修改字段

  MySQL修改的关键字为ALTER，具体操作如下；

-- 将原来的suit_code VARCHAR(200)变成VARCHAR(500)，不允许为空，且默认值改成'大套'
-- 模板：ALTER TABLE tb_name MODIFY 字段名称 字段类型 [约束条件]
-- 修改时如果不带约束条件，原有的约束条件将丢失，如果想保留修改时就得带上约束条件
ALTER TABLE cfg_azkaban_flow_info MODIFY suit_code VARCHAR(500) NOT NULL DEFAULT '大套';

-- 将suit_code移到data_set后面，保留原完整性约束条件
-- 模板：ALTER TABLE tb_name MODIFY 字段名称 字段类型 [约束条件] AFTER 字段名称2
ALTER TABLE cfg_azkaban_flow_info MODIFY suit_code VARCHAR(500) NOT NULL DEFAULT '大套' AFTER data_set;

-- 将suit_code放到第一个，保留原完整性约束条件
-- 模板：ALTER TABLE tb_name MODIFY 字段名称 字段类型 [约束条件] FIRST
ALTER TABLE cfg_azkaban_flow_info MODIFY suit_code VARCHAR(500) NOT NULL DEFAULT '大套'  FIRST;

-- 将suit_code字段改为sub_suit_code，suit_name 改成sub_suit_name，多字段间用逗号隔开
-- ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段类型 约束条件
ALTER TABLE cfg_azkaban_flow_info 
CHANGE suit_code sub_suit_code  VARCHAR(500) NOT NULL DEFAULT '大套',
CHANGE suit_name sub_suit_name  VARCHAR(200) NOT NULL DEFAULT '大套1'
;

默认值操作

  MySQL默认值新增和删除如下，多个字段用,隔开，最后用;结束；

-- 添加默认值
ALTER TABLE cfg_azkaban_flow_info 
ALTER pre_end_time SET DEFAULT 0,
ALTER pre_end_type SET DEFAULT 'D1'
;

-- 删除默认值
ALTER TABLE cfg_azkaban_flow_info ALTER pre_end_time DROP DEFAULT, ALTER pre_end_type DROP DEFAULT;

主键操作

• 删除主键:如果主键不是自增列，直接删除,注意删除主键只是删除了主键约束，字段还是在的，不要搞错了，语句如下；

-- 删除主键，这样会报错，因为自增长的必须是主键
-- 模板：ALTER TABLE 表名 DROP PRIMARY KEY;
ALTER TABLE MyTest DROP PRIMARY KEY;

• 删除主键:如果主键是自增列，直接删除会报错Incorrect table definition; there can be only one auto column and it must be defined as a key,那么需要先去除自增这个数据，注意，去除自增不会去掉主键属性，然后再删除主键，注意删除主键只是删除了主键约束，字段还是在的，不要搞错了。

-- 删除主键，这样会报错，因为自增长的必须是主键
ALTER TABLE cfg_azkaban_flow_info DROP PRIMARY KEY;
--报错：Incorrect table definition; there can be only one auto column and it must be defined as a key


-- 先用MODIFY删除自增长属性，注意MODIFY不能去掉主键属性
ALTER TABLE cfg_azkaban_flow_info MODIFY flow_id INT UNSIGNED;
-- 再来删除主键
ALTER TABLE cfg_azkaban_flow_info DROP PRIMARY KEY;
-- 成功删除主键

• 新增主键：主键是一种约束，新增主键肯定是基于已存在的字段的，而不是说会新建个字段出来，主键会自动建立根据主键字段的唯一性索引，以下是新增单字段主键和新增复合组件的sql；

-- 新增单字段主键
ALTER TABLE cfg_azkaban_flow_info ADD CONSTRAINT symbol PRIMARY KEY index_type(flow_id);
--可以简写为
ALTER TABLE cfg_azkaban_flow_info ADD PRIMARY KEY(flow_id);



--新增复合主键，即主键由多个字段决定
ALTER TABLE cfg_azkaban_flow_info ADD CONSTRAINT symbol PRIMARY KEY index_type(flow_id,create_time);
--可以简写为
ALTER TABLE cfg_azkaban_flow_info ADD PRIMARY KEY(flow_id,create_time);

删除字段

  MySQL删除字段，操作如下，多个字段用,隔开，最后用;结束，高危操作，慎重使用；

-- 删除字段 模板： ALTER TABLE 表名 DROP 字段名 
ALTER TABLE cfg_azkaban_flow_info 
DROP sub_suit_code, 
DROP sub_suit_name;

已存在的表新增自增列

  如果需要在已经存在的表内新增自增列可以有以下操作；

-- 新建测试表
CREATE TABLE `t_abc` (
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 导入测试数据：
INSERT INTO `t_abc` (`name`)
VALUES
	('mike'),
	('tom'),
	('jack');

-- 因为MySQL内自增列必须为主键，所以增加一个自增主键字段，分两步操作
-- 首先增加自增字段，且必须为主键
alter table t_abc add column id int auto_increment primary key;

-- 执行这一条，它会自动为已存在的数据的自增字段赋初值，从1开始，同时将后续新增的数据从100开始
alter table t_abc auto_increment=100;
insert into t_abc(name) values('rowyet');
-- 得到的结果为
-- name    id
-- mike     1
-- tom      2
-- jack     3
-- rowyet   100




-- 修改已有数据初始值
-- 如果希望所有的数据都从10001 开始，我们可以这么做
-- 将所有数据增加10000
update t_abc set id=id+10000;

-- 找到目前库里的最大值加1作为新增字段的起始自增列，
set @maxId=1;
select max(id) into @maxId from t_abc;  --最大为10100
select @maxId+1 from dual; --计算得到10101作为下一个新增值的自增列的起始值

alter table t_abc auto_increment=10101; -- 设置10101作为下一个新增值的自增列的起始值

-- 验证效果
insert into t_abc(name) values('Marry');
select * from t_abc order by id desc;
-- 得到的结果为
-- name    id
-- mike     10001
-- tom      10002
-- jack     10003
-- rowyet   10100
-- Marry    10101

-- 其实还有种更简单的方法，那就是利用CREATE TABLE IF NOT EXISTS 新建的表 LIKE 存在的表;然后把旧表数据insert into 过去

表索引操作

  表索引可以大大的提升查询速度，但是表索引也是占空间的，所以给表加索引不是越多越好，而是能让查询遍历走索引，命中索引的概率越高越好，MySQL的索引一般分为主键索引,唯一性索引,普通索引,包含索引,全文索引等,这里不做详细说明，后续有专门的博客介绍，针对索引的操作如下；

-- 添加唯一性约束
-- ALTER TABLE tb_name ADD [CONSTANT [symbol]] UNIQUE [INDEX | KEY] [索引名称](字段名称,...)

-- flow_name添加唯一性约束，如果没有指定索引名称，系统会以字段名建立索引
ALTER TABLE cfg_azkaban_flow_info ADD UNIQUE(flow_name);
-- 添加唯一性约束
ALTER TABLE cfg_azkaban_flow_info ADD CONSTRAINT symbol UNIQUE KEY uk_flow_name(flow_name);
-- 查看索引
SHOW CREATE TABLE cfg_azkaban_flow_info;

-- 添加联合unique
ALTER TABLE cfg_azkaban_flow_info ADD CONSTRAINT symbol UNIQUE INDEX uk_flow_name_creat_time(flow_name, create_time);

-- 删除唯一
-- ALTER TABLE tb_name DROP {INDEX|KEY} index_name;
-- 删除刚刚添加的唯一索引
ALTER TABLE cfg_azkaban_flow_info DROP INDEX uk_flow_name_creat_time;
ALTER TABLE cfg_azkaban_flow_info DROP KEY uk_flow_name;
ALTER TABLE cfg_azkaban_flow_info DROP KEY flow_name;

-- 新增普通索引
-- 模板：ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) ;
ALTER TABLE `cfg_azkaban_flow_info` ADD INDEX ix_data_set ( `data_set` ); 

-- 新增包含索引 就是你where条件有各种and联合起来的多字段的时候，需要把这些字段都建在包含索引内
-- 模板：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` );
ALTER TABLE `cfg_azkaban_flow_info` ADD INDEX ix_is_enable_flow_name ( `is_enable`, `flow_name`);

-- 新增全文索引 ，不常用
-- 模板：ALTER TABLE `table_name` ADD FULLTEXT ( `column`);
ALTER TABLE `cfg_azkaban_flow_info` ADD FULLTEXT ( `project_name`) ;

-- 删除索引
-- 模板：ALTER TABLE tb_name DROP {INDEX|KEY} index_name;
ALTER TABLE cfg_azkaban_flow_info DROP INDEX uk_flow_name_creat_time;
ALTER TABLE cfg_azkaban_flow_info DROP KEY uk_flow_name;
ALTER TABLE cfg_azkaban_flow_info DROP KEY flow_name;
ALTER TABLE cfg_azkaban_flow_info DROP KEY ix_data_set;
ALTER TABLE cfg_azkaban_flow_info DROP KEY project_name;
ALTER TABLE cfg_azkaban_flow_info DROP KEY ix_is_enable_flow_name;

  索引建出来的效果如图1；

重命名表

  重命名表需要你有该表的删除和建表权限，不然会报错，重命名语句如下，有两种方法；

-- 方法一
-- 修改表名， TO 或AS都可以，也以省略掉
-- ALTER TABLE 表名 RENAME [TO|AS] 新表名
ALTER TABLE cfg_azkaban_flow_info RENAME TO cfg_azkaban_flow_info_bak;

--方法二
-- RENAME TABLE 表名 TO 新表名； 这里面的TO不可以省略
RENAME TABLE cfg_azkaban_flow_info_bak TO cfg_azkaban_flow_info;

trancate表

  MySQL的trancate需要你有该表的删除和建表权限，不然会报错，用途是把表内的数据全部删除，本质应该是先保存了建表语句，把表删除了，然后再根据保存的建表语句把表建出来，连自增列id都会回到初始定义的开始种子值，语句如下；

TRUNCATE TABLE cfg_azkaban_flow_info;

删除表

  MySQL删除表操作如下，高危操作，慎重使用；

DROP TABLE  IF  EXISTS `cfg_azkaban_flow_info`;