MySQL8.0学习记录15 - ALTER语句

DATABASE 只读

ALTER DATABASE 除了常来修改数据库名、字符集以及校对规则之外，MySQL8.0新增了READ ONLY 选项，这个在数据迁移的时候比较有用。
测试一下：

create table t_person(
	id int,
	name varchar(10)
);

insert into t_person(id,name) values(1,'A');

ALTER DATABASE TEST READ ONLY = 1;

insert into t_person(id,name) values(2,'B');

最后一句，会报错“Schema ‘test’ is in read only mode.”

一旦database 只读之后，对数据库很多操作都是终止的，包括但不限于表的更新、创建、删除，视图的更新、创建、删除，索引的相关操作等等。

ALTER EVENT

EVENT这个东西以前没有用过，它可以用来定时或在指定的时间执行SQL：

create table t_event_test(
	id int,
	count int
);

CREATE EVENT myevent
    ON SCHEDULE
      EVERY 1 SECOND 
    COMMENT '每秒更新一次count'
    DO
      UPDATE t_event_test SET count = count + 1 where id = 1; 

ALTER 可以修改EVENT的时间和语句，也可以disable：

ALTER EVENT myevent
    DISABLE;

个人感觉event用来定期删除历史数据是有一定的可行性。

ALTER TABLE

ALTER TABLE通常会使用三种算法来处理：

1. COPY: 对原表的副本进行操作，表的数据从原表逐行复制到新表。不允许并发的DML。
2. INPLACE: 操作避免复制表的数据，可以在原地重建表。在操作的准备和执行阶段，表上的独占元数据锁可能会被短暂占用。通常情况下，支持并发DML.
3. INSTANT: MySQL8新增的，操作只修改数据字典中的元数据。在操作的执行阶段，表上的独占元数据锁可能会被短暂占用。表的数据不受影响，操作是瞬时的。允许并发的DML。

MySQL8.0开始如果表的数据库引擎支持，默认ALGORITHM=INSTANT。

ALGORITHM

COPY

使用COPY算法的ALTER TABLE操作会等待其他正在修改表的操作完成。当ALTER TABLE操作执行的时候，原始表可以被其他会话读取；在ALTER TABLE操作开始后开始的对表的更新和写入会被停止，直到新表准备好，然后会自动重定向到新表。

ALTER TABLE在准备从表和表定义缓存中清除过时的表结构时，会使用独占锁，阻止读取。

用COPY算法时并发查询是可以的。一个表复制操作总是至少包括LOCK=SHARED的并发限制（允许查询，但不允许DML）。可以通过指定LOCK=EXCLUSIVE来进一步限制支持LOCK子句的操作的并发性，这样也不允许读。

需要注意是，COPY过程会需要的额外的空间，如果表是在共享表空间的话，在操作完成后这些空间不会被释放。对于独立表空间，则不需要担心；好在MySQL5.6之后都是默认独立表空间，可以查看变量innodb_file_per_table来确认。

INPLACE

INPLACE 算法支持一部分Online DDL。Online DDL有一些限制。

修改元数据而不动数据内容的操作是瞬时的，比如：

• 修改列名
• 修改默认值
• 修改字符集，瞬时操作有一定的条件：类型时 CHAR、VARCHAR、TEXT、ENUM；列上没有索引；从utf8mb3 改为utf8mb4，或任一字符集改成binary
• 修改生成的存储列的语句，但不改变其类型、表达式或无效性
• 修改列的NULL属性，前提是列没参与到生成列的表达式中

INSTANT

瞬时添加列

• 在MySQL 8.0.29之前，一个列只能被添加为表的最后一列。从MySQL 8.0.29开始，一个即时添加的列可以被添加到表中的任何位置。
• 列不能被添加到使用ROW_FORMAT=COMPRESSED的表，具有FULLTEXT索引的表，位于数据字典表空间的表，或者临时表。临时表只支持ALGORITHM=COPY。
• 改变列的数据类型只支持ALGORITHM=COPY

瞬时删除列

• 删除一个列不能与其他不支持ALGORITHM=INSTANT的ALTER TABLE操作结合在同一个语句中
• 不能从使用ROW_FORMAT=COMPRESSED的表、具有FULLTEXT索引的表、位于数据字典表空间的表或者临时表中删除列。临时表只支持ALGORITHM=COPY

其他支持瞬时操作的还有添加或删除一个虚拟列、添加或删除一个列的默认值、改变索引类型（指的是使用BTREE 或HASH）等等。

Renaming, Redefining, and Reordering Columns

• CHANGE：既可以重命名又可以修改列定义，可以修改列顺序
• MODIFY：只可以修改列定义，可以修改列顺序
• RENAME COLUMN：只可以重命名