MySQL DDL--MySQL 5.7版本Online DDL操作
主键索引维护
1、新增主键索引
## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。 ALTER TABLE tb001 ADD PRIMARY KEY (ID), ALGORITHM = INPLACE; Query OK, 0 rows affected (22.13 sec) Records: 0 Duplicates: 0 Warnings: 0 2、官网称如果创建主键索引时需要隐式转换列为NOT NULL则不能使用INPLACE方式(ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL)。 但在MySQL 5.7.19版本中测试发现在允许为NULL的列上创建主键，采用REBUILD INPLACE方式的Online DDL,操作期间允许表并发读写。
2、删除主键索引
## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。 ALTER TABLE tb001 DROP PRIMARY KEY, ALGORITHM = INPLACE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM=COPY. ALTER TABLE tb001 DROP PRIMARY KEY, ALGORITHM=COPY; Query OK, 800001 rows affected (20.01 sec) Records: 800001 Duplicates: 0 Warnings: 0
3、删除并新增主键索引
## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。 ALTER TABLE TB001 DROP PRIMARY KEY, ADD PRIMARY KEY(C1), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (22.57 sec) Records: 0 Duplicates: 0 Warnings: 0
4、修改主键列类型
## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。 ALTER TABLE TB001 CHANGE C1 C1 BIGINT NOT NULL, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. ALTER TABLE TB001 CHANGE C1 C1 BIGINT NOT NULL, ALGORITHM=COPY, LOCK=NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED. ALTER TABLE TB001 CHANGE C1 C1 BIGINT NOT NULL, ALGORITHM=COPY, LOCK=SHARED; Query OK, 799998 rows affected (20.49 sec) Records: 799998 Duplicates: 0 Warnings: 0
二级索引维护
1、新增二级索引
## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。 ALTER TABLE TB001 ADD INDEX IDX_C1(C1); Query OK, 0 rows affected (10.53 sec) Records: 0 Duplicates: 0 Warnings: 0
2、删除二级索引
## 仅需元数据修改操作，操作时间极短。 ALTER TABLE TB001 DROP INDEX IDX_C1; Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0
3、修改二级索引名
## 仅需元数据修改操作，操作时间极短。 ALTER TABLE TB001 RENAME INDEX IDX_C1 TO IDX_C1_NEW, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0
4、修改二级索引列类型
## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。 ALTER TABLE TB001 CHANGE C1 C1 INT; Query OK, 800000 rows affected (25.03 sec) Records: 800000 Duplicates: 0 Warnings: 0 ALTER TABLE TB001 CHANGE C1 C1 BIGINT, ALGORITHM = INPLACE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
自增属性修改
1、修改普通主键列为自增主键列
## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。 ALTER TABLE TB001 MODIFY ID BIGINT AUTO_INCREMENT; Query OK, 800000 rows affected (26.15 sec) Records: 800000 Duplicates: 0 Warnings: 0
2、修改自增主键列为普通主键列
## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。 ALTER TABLE TB001 MODIFY ID BIGINT NOT NULL; Query OK, 800000 rows affected (28.27 sec) Records: 800000 Duplicates: 0 Warnings: 0
3、修改表自增开始值
## 仅需要修改元数据信息，操作时间极短。 ALTER TABLE TB001 AUTO_INCREMENT=800020; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
列属性修改
1、新增列
## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。 ## 无论是新增可为NULL的列还是NOT NULL的列 ALTER TABLE TB001 ADD C2 INT NOT NULL DEFAULT 99999, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (33.05 sec) Records: 0 Duplicates: 0 Warnings: 0
2、删除列
## 可以使用ALGORITHM=INPLACE+LOCK=NONE方式，操作期间允许读写。 ALTER TABLE TB001 DROP COLUMN C5, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (33.57 sec) Records: 0 Duplicates: 0 Warnings: 0
3、修改列数据类型(INT至BIGINT)
## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。 ALTER TABLE TB001 CHANGE C5 C5 BIGINT NULL, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. ALTER TABLE TB001 CHANGE C5 C5 BIGINT NULL, ALGORITHM=COPY, LOCK=SHARED; Query OK, 0 rows affected (29.63 sec) Records: 0 Duplicates: 0 Warnings: 0
4、修改列类型显示长度(INT(10)至INT(8)或INT(8)至INT(10))
## 无论使用CHANGE命令还是MODIFY命令，都是修改元数据，操作时间极短。 ALTER TABLE tb001 MODIFY C2 INT(8), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tb001 CHANGE C2 C2 INT(8) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
5、修改列类型扩展存储长度(VARCHAR(300)至VARCHAR(500))
## 无论使用CHANGE命令还是MODIFY命令，都是修改元数据，操作时间极短。 ALTER TABLE tb001 CHANGE C3 C3 varchar(500) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tb001 MODIFY COLUMN C3 VARCHAR(500), ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0
6、修改列类型扩展存储长度(VARCHAR(10)至VARCHAR(100))
## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。 ALTER TABLE tb001 MODIFY COLUMN C3 VARCHAR(100), ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. ALTER TABLE tb001 MODIFY COLUMN C3 VARCHAR(100), ALGORITHM=COPY, LOCK=SHARED; Query OK, 0 rows affected (33.57 sec) Records: 0 Duplicates: 0 Warnings: 0
7、修改列类型收缩存储长度(VARCHAR(500)至VARCHAR(300))
## 只能使用ALGORITHM=COPY+LOCK=SHARED方式，操作期间允许读不允许写。 ALTER TABLE tb001 CHANGE C3 C3 VARCHAR(300) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. ALTER TABLE tb001 MODIFY COLUMN C3 VARCHAR(300), ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. ALTER TABLE tb001 MODIFY COLUMN C3 VARCHAR(300), ALGORITHM=COPY, LOCK=SHARED; Query OK, 800009 rows affected (27.28 sec) Records: 800009 Duplicates: 0 Warnings: 0
8、修改修改列注释
## 仅需修改元数据，操作时间极短 ALTER TABLE tb001 MODIFY COLUMN C3` varchar(300) DEFAULT NULL COMMENT '测试1'; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE tb001 CHANGE C3 C3 varchar(300) DEFAULT NULL COMMENT '测试2'; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
MySQL 5.7版本下DDL操作
