mysql：事务的影响范围

环境：

• window 11
• mysql 8.0.12

参考：sqlserver：事务的影响范围

问题来源：

我们知道关系型数据库具有事务的概念，我们在事务内可以增删改数据，一旦发现有误，我们可以执行回滚，这样就可以撤销对数据的更改了，那么，除了增删改数据，其他的如：自增id、表结构、甚至是增删表的操作是否也可以被撤销掉（回滚: rollback）呢？

在《sqlserver：事务的影响范围》中实验得出的结论是：

sqlserver的事务回滚会将truncate、增删表、增删列、约束增删等操作都撤销掉，而自增id、序列都不会被回滚。

在mysql中实验的是：

表结构的更改都不会被撤销（包含sqlserver中的truncate、增删表等），自增id也不会被撤销，被撤销的只是增删改语句。

1. 自增id不能被回滚

create table test(
	id int primary key auto_increment,
	name varchar(50)
)
-- 开启事务
start transaction 
-- 插入一条数据
insert into test(name) values('小明')
-- 回滚
rollback
-- 再插入一条数据
insert into test(name) values('a')
-- 查看id情况
/* 输出
id|name|
--+----+
 2|a   |
*/

2. truncate 不能被回滚

这个和sqlserver不同。

create table test(
	id int primary key auto_increment,
	name varchar(50)
)
-- 插入一条数据
insert into test(name) values('小明')
-- 开启事务并执行truncate
start transaction 
truncate table test
-- 回滚
rollback
-- 查看事务内的truncate能否被撤销
select * from test
/* 输出
id|name|
--+----+
*/

3. 增删列不能被回滚

create table test(
	id int primary key auto_increment,
	name varchar(50)
)
-- 开启事务并新增、删除列
start transaction 
alter table test add name2 varchar(50)
alter table test drop column name
-- 回滚后查看列的新增和删除效果是否被撤销
rollback
select * from test
/* 输出
id|name2|
--+-----+
*/

4. 修改列类型不能被回滚

create table test(
	id int primary key auto_increment,
	name varchar(50)
)
-- 开始事务并修改列类型
start transaction 
alter table test modify column name int
rollback
-- 回滚后查看效果
select
	c.TABLE_CATALOG ,
	c.TABLE_SCHEMA,
	c.TABLE_NAME ,
	c.COLUMN_NAME ,
	c.COLUMN_TYPE
from	
information_schema.`COLUMNS` c 
where 	c.TABLE_SCHEMA = 'test'	and c.TABLE_NAME = 'test'

/* 输出
TABLE_CATALOG|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME|COLUMN_TYPE|
-------------+------------+----------+-----------+-----------+
def          |test        |test      |id         |int(11)    |
def          |test        |test      |name       |int(11)    |
*/

5. 给表增加约束不能被回滚

这个由于安装的mysql中缺少information_schema.CHECK_CONSTRAINTS导致实验无法进行，不过应该是不能被回滚。

实验时提示：Unknown table ‘check_constraints’ in information_schema，看文档是：8.0.16以上才支持检查约束：

6. 创建表、删除表不能被回滚

create table test(
	id int primary key auto_increment,
	name varchar(50)
)
-- 开启事务并进行表的增删
start transaction
drop table test
create table test2(
	id int primary key auto_increment,
	name varchar(50)
)
select t.TABLE_SCHEMA ,t.TABLE_NAME from information_schema.TABLES t where t.TABLE_SCHEMA ='test'
/* 输出
TABLE_SCHEMA|TABLE_NAME|
------------+----------+
test        |test2     |
*/

-- 回滚事务并观察表的情况
rollback 
select t.TABLE_SCHEMA ,t.TABLE_NAME from information_schema.TABLES t where t.TABLE_SCHEMA ='test'
/* 输出
TABLE_SCHEMA|TABLE_NAME|
------------+----------+
test        |test2     |
*/

7. 给表、列重命名不能被回滚

create table test(
	id int primary key auto_increment,
	name varchar(50)
)
start transaction
rename table test to test2
rollback
select t.TABLE_SCHEMA ,t.TABLE_NAME from information_schema.TABLES t where t.TABLE_SCHEMA ='test'
/* 输出
TABLE_SCHEMA|TABLE_NAME|
------------+----------+
test        |test2     |
*/

-- 重命名列也是一个效果