mysql5.7ddl原子_MySQLTableMySQL5.7版本DDL整理

主键索引维护

1、新增主键索引

## 可以使用ALGORITHM&＃61;INPLACE&＃43;LOCK&＃61;NONE方式&＃xff0c;操作期间允许读写。

ALTER TABLE tb001

ADD PRIMARY KEY (ID),

ALGORITHM &＃61; INPLACE;

Query OK, 0 rows affected (22.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

2、官网称如果创建主键索引时需要隐式转换列为NOT NULL则不能使用INPLACE方式(ALGORITHM&＃61;INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL)。

但在MySQL 5.7.19版本中测试发现在允许为NULL的列上创建主键&＃xff0c;采用REBUILD INPLACE方式的Online DDL,操作期间允许表并发读写。

2、删除主键索引

## 只能使用ALGORITHM&＃61;COPY&＃43;LOCK&＃61;SHARED方式&＃xff0c;操作期间允许读不允许写。

ALTER TABLE tb001

DROP PRIMARY KEY,

ALGORITHM &＃61; INPLACE;

ERROR 1846 (0A000): ALGORITHM&＃61;INPLACE is not supported. Reason: Dropping a primary key is not allowed without also adding a new primary key. Try ALGORITHM&＃61;COPY.

ALTER TABLE tb001

DROP PRIMARY KEY,

ALGORITHM&＃61;COPY;

Query OK, 800001 rows affected (20.01 sec)

Records: 800001 Duplicates: 0 Warnings: 0

3、删除并新增主键索引

## 可以使用ALGORITHM&＃61;INPLACE&＃43;LOCK&＃61;NONE方式&＃xff0c;操作期间允许读写。

ALTER TABLE TB001

DROP PRIMARY KEY,

ADD PRIMARY KEY(C1),

ALGORITHM&＃61;INPLACE,

LOCK&＃61;NONE;

Query OK, 0 rows affected (22.57 sec)

Records: 0 Duplicates: 0 Warnings: 0

4、修改主键列类型

## 只能使用ALGORITHM&＃61;COPY&＃43;LOCK&＃61;SHARED方式&＃xff0c;操作期间允许读不允许写。

ALTER TABLE TB001

CHANGE C1 C1 BIGINT NOT NULL,

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

ERROR 1846 (0A000): ALGORITHM&＃61;INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM&＃61;COPY.

ALTER TABLE TB001

CHANGE C1 C1 BIGINT NOT NULL,

ALGORITHM&＃61;COPY, LOCK&＃61;NONE;

ERROR 1846 (0A000): LOCK&＃61;NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK&＃61;SHARED.

ALTER TABLE TB001

CHANGE C1 C1 BIGINT NOT NULL,

ALGORITHM&＃61;COPY, LOCK&＃61;SHARED;

Query OK, 799998 rows affected (20.49 sec)

Records: 799998 Duplicates: 0 Warnings: 0

二级索引维护

1、新增二级索引

## 可以使用ALGORITHM&＃61;INPLACE&＃43;LOCK&＃61;NONE方式&＃xff0c;操作期间允许读写。

ALTER TABLE TB001 ADD INDEX IDX_C1(C1);

Query OK, 0 rows affected (10.53 sec)

Records: 0 Duplicates: 0 Warnings: 0

2、删除二级索引

## 仅需元数据修改操作&＃xff0c;操作时间极短。

ALTER TABLE TB001 DROP INDEX IDX_C1;

Query OK, 0 rows affected (0.13 sec)

Records: 0 Duplicates: 0 Warnings: 0

3、修改二级索引名

## 仅需元数据修改操作&＃xff0c;操作时间极短。

ALTER TABLE TB001

RENAME INDEX IDX_C1 TO IDX_C1_NEW,

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

4、修改二级索引列类型

## 只能使用ALGORITHM&＃61;COPY&＃43;LOCK&＃61;SHARED方式&＃xff0c;操作期间允许读不允许写。

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 &＃61; INPLACE;

ERROR 1846 (0A000): ALGORITHM&＃61;INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM&＃61;COPY.

自增属性修改

1、修改普通主键列为自增主键列

## 只能使用ALGORITHM&＃61;COPY&＃43;LOCK&＃61;SHARED方式&＃xff0c;操作期间允许读不允许写。

ALTER TABLE TB001 MODIFY ID BIGINT AUTO_INCREMENT;

Query OK, 800000 rows affected (26.15 sec)

Records: 800000 Duplicates: 0 Warnings: 0

2、修改自增主键列为普通主键列

## 只能使用ALGORITHM&＃61;COPY&＃43;LOCK&＃61;SHARED方式&＃xff0c;操作期间允许读不允许写。

ALTER TABLE TB001 MODIFY ID BIGINT NOT NULL;

Query OK, 800000 rows affected (28.27 sec)

Records: 800000 Duplicates: 0 Warnings: 0

3、修改表自增开始值

## 仅需要修改元数据信息&＃xff0c;操作时间极短。

ALTER TABLE TB001 AUTO_INCREMENT&＃61;800020;

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

列属性修改

1、新增列

## 可以使用ALGORITHM&＃61;INPLACE&＃43;LOCK&＃61;NONE方式&＃xff0c;操作期间允许读写。

## 无论是新增可为NULL的列还是NOT NULL的列

ALTER TABLE TB001

ADD C2 INT NOT NULL DEFAULT 99999,

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

Query OK, 0 rows affected (33.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

2、删除列

## 可以使用ALGORITHM&＃61;INPLACE&＃43;LOCK&＃61;NONE方式&＃xff0c;操作期间允许读写。

ALTER TABLE TB001

DROP COLUMN C5,

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

Query OK, 0 rows affected (33.57 sec)

Records: 0 Duplicates: 0 Warnings: 0

3、修改列数据类型(INT至BIGINT)

## 只能使用ALGORITHM&＃61;COPY&＃43;LOCK&＃61;SHARED方式&＃xff0c;操作期间允许读不允许写。

ALTER TABLE TB001

CHANGE C5 C5 BIGINT NULL,

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

ERROR 1846 (0A000): ALGORITHM&＃61;INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM&＃61;COPY.

ALTER TABLE TB001

CHANGE C5 C5 BIGINT NULL,

ALGORITHM&＃61;COPY, LOCK&＃61;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命令&＃xff0c;都是修改元数据&＃xff0c;操作时间极短。

ALTER TABLE tb001

MODIFY C2 INT(8),

ALGORITHM&＃61;INPLACE, LOCK&＃61;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&＃61;INPLACE, LOCK&＃61;NONE;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0

5、修改列类型扩展存储长度(VARCHAR(300)至VARCHAR(500))

## 无论使用CHANGE命令还是MODIFY命令&＃xff0c;都是修改元数据&＃xff0c;操作时间极短。

ALTER TABLE tb001

CHANGE C3 C3 varchar(500) DEFAULT NULL,

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(500),

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

6、修改列类型扩展存储长度(VARCHAR(10)至VARCHAR(100))

## 只能使用ALGORITHM&＃61;COPY&＃43;LOCK&＃61;SHARED方式&＃xff0c;操作期间允许读不允许写。

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(100),

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

ERROR 1846 (0A000): ALGORITHM&＃61;INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM&＃61;COPY.

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(100),

ALGORITHM&＃61;COPY, LOCK&＃61;SHARED;

Query OK, 0 rows affected (33.57 sec)

Records: 0 Duplicates: 0 Warnings: 0

7、修改列类型收缩存储长度(VARCHAR(500)至VARCHAR(300))

## 只能使用ALGORITHM&＃61;COPY&＃43;LOCK&＃61;SHARED方式&＃xff0c;操作期间允许读不允许写。

ALTER TABLE tb001

CHANGE C3 C3 VARCHAR(300) DEFAULT NULL,

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

ERROR 1846 (0A000): ALGORITHM&＃61;INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM&＃61;COPY.

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(300),

ALGORITHM&＃61;INPLACE, LOCK&＃61;NONE;

ERROR 1846 (0A000): ALGORITHM&＃61;INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM&＃61;COPY.

ALTER TABLE tb001

MODIFY COLUMN C3 VARCHAR(300),

ALGORITHM&＃61;COPY, LOCK&＃61;SHARED;

Query OK, 800009 rows affected (27.28 sec)

Records: 800009 Duplicates: 0 Warnings: 0

8、修改修改列注释

## 仅需修改元数据&＃xff0c;操作时间极短

ALTER TABLE tb001

MODIFY COLUMN C3&＃96; varchar(300) DEFAULT NULL COMMENT &＃39;测试1&＃39;;

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 &＃39;测试2&＃39;;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0