mysql alter算法_MYSQL 5.7 alter table 小记

MYSQL 5.7 alter table 小记

mysql的alter table 本质上是通过创建临时表，然后将数据copy到临时表里，之后删除原来的表，重命名临时表 去做的alter操作

alter table 这个操作能干啥

创建删除索引

更改列类型

重命名列或者表本身

重置自增列的起始值

不能设置值为当前DB中已经用到过的最大值。

alter table t21 auto_increment=13;

alter table 的算法

COPY

INPLACE

算法区别

copy 这个操作一行一行的copy数据从原始表到新表， 而且不支持并发DML，但是并发查询仍然是支持的，默认使用共享锁(可以并发查询)，也可指定拍他锁(并发查询和DML都不支持)

inplace 这个操作会避免copy表数据，但是可能会重建表。在数据准备和执行阶段会进行行锁定，通常支持并发DML

mysql默认选择inplace，如果存储引擎不支持，就会选中copy。

InnoDB 对于在共享表空间中的表，使用的是COPY进行的alter table。此类会增加表的空间使用量，因为alter完成之后使用的额外空间并不会释放会操作系统。

使用INPLACE的算法场景包括：

alter table的操作被InnoDB online DDL的特性所支持。

重命名表。 mysql会重命名table对应的文件，并不会做copy数据的操作。(per_file_on_table 需要打开)

仅修改表的元数据的操作，包括： rename column，变更列的默认值，不改变数据类型存储空间的操作

重命名索引

添加或者删除一个二级索引

并发控制

LOCK= DEFAULT ｜ NONE ｜ SHARED ｜ EXCLUSIVE

DEFAULT 最大级别的并发。并发读和写 -> 并发读 -> 排它锁(依次降级)

NONE 并发读和写，不支持就报错

SHARED 并发读，不允许写。

EXCLUSIVE 不允许并发读和写。

修改列的三种方式

change

能够重命名列和更改列的定义，或者同时进行

有更多的能力比modify，但是在某些方面会比较慢。 比如： 如果不是重命名的话， change会对列命名两次

可以通过first/ after 更改列顺序

modify

能够改变列定义，但是不能改变列的名称。

不是重命名的时候比change更加方便

可以通过first/ after 更改列顺序

alter

用来更改列的默认值

demo

alter table temp_table change name name_new varchar(100);

alter table temp_table change name name varchar(100);

alter table temp_table modify name varchar(200) after id ;

alter table temp_table alter name set default ‘‘;

备注：

change是mysql对标准sql的拓展， modify是mysql对oracel的兼容。

modifyor change 去更改数据类型的时候，mysql会尝试转化存在的列的值到新的类型中。

注意： 通过change重命名列

自动变更

指向旧列的索引

外键

不会自定变更

生成的列和分块的表达式

视图和存储过程