主键索引维护
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