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重命名列

• 自动变更
  • 指向旧列的索引
  • 外键
• 不会自定变更
  • 生成的列和分块的表达式
  • 视图和存储过程