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通常会使用三种算法来处理:
- COPY: 对原表的副本进行操作,表的数据从原表逐行复制到新表。不允许并发的DML。
- INPLACE: 操作避免复制表的数据,可以在原地重建表。在操作的准备和执行阶段,表上的独占元数据锁可能会被短暂占用。通常情况下,支持并发DML.
- 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:只可以重命名