MySQL基础大全(3)-数据库中进阶的基础(事务)

1. 标识列(自增长列)

是指无需手动插入，系统提供默认的序列值；如下：

#基本语法：需要注意的是，要是一个主键或unique(被设置的列)
create table test5(
Atest5 int primary key auto_increment,
Bname varchar(5)
)
# 一般默认的就是每一次都加1，不过可以自己设置步长；
set auto_increment_increment=2;#这样每一次插入数据Atest5自增长2；这是改变整个数据库的自增长步长，一般不需要改，了解即可；

2. 事务控制语言(Transaction Control Language)

2.1 概念

一个或者一组sql语言组成的一个执行单元，这个执行单元要么全部执行，要么都不执行；主要理解就是多条sql语句的运行，类似于Java中的同步；

2.2 事务的诞生

在MySQL中，有许多的数据通过不同的技术存储到文件中，主要有innodb,myisam,memory；但是只有innodb支持事务。

2.3 事务的特性

1. 原子性(Atomicity)：指事务是一个不可分割的工作单位，事务中的操作要么全部发生，要么都不发生；
2. 一致性(Con sistency)：事务必须使数据库从一个一致性状态到达另一个一致性状态；
3. 隔离性(Isolation)： 一个事务不能对其他事务进行干扰，所有的事务都是允许并发进行的；
4. 持久性(Durability)：表示一个事务一旦提交，它对数据库的数据改变是永久性的；

2.4 事务的创建

1. 隐式事务：事务没有明显的开启和结束标记；
   例如：insert、update、delete语句等都属于一个事务；
2. 显式事务：事务具有明显的开启和结束标记；(要先设置自动提交功能为禁用)，

#自动提交功能的设置，一般只针对当前会话，一旦重新连接之后，就需要再次设置；
set autocommit=0#表示自动提交功能关闭；
#然后就可以开启事务了
start transaction;#或者可以使用begin;
#中间是增删改查(作为事务)；
select,insert,delete,update;
#最后结束事务；
commit;#提交事务；
rollback;#回滚事务；

---

2.5 事务可能会导致的问题

当同时执行多个事务的时候，如果这些事务访问的是数据库的同一个数据，若是数据库中的这个数据没有一点保护机制，就很容易出现并发问题；
以下就是一些经常出现的问题:

1. 脏读：假设两个事务A和B，如果事务A读取了已经被事务B更新了但是没有提交的字段，一旦B回滚，那么之前事务A读取的数据就是无效的；
2. 不可重复读：对于两个事务A和B,事务A读取了一个字段，但是事务B更新了该字段，于是事务A再次读取同一个字段的时候就会读取到不同的值；(简单而言就是需要读取事务已经提交的数据，防止出现重复读，导致的值不同，针对的是更新)
3. 幻读：对于两个事务A和B来说，如果事务A读取一个表中的字段，但是当事务B往表中插入一些新的行之后，如果事务A再次执行，就会发现同一个表多了几行；(这里针对的是插入，因为是数据的总数增多了)

---

2.6 设置隔离级别来解决事务所导致的并发问题

**事务的隔离级别种类：

隔离级别	中文翻译	详细描述
read uncommitted	读未提交数据	允许事务读取未被提交的数据，可能出现脏读，幻读和不可重复读的问题
read committed	读已提交数据	只允许事务读取已经提交的数据，可以防止脏读，但是还是会有幻读和不可重复读
repeatable read	可重复读	可以保证事务可以多次从一个字段中读取到相同的值，在这个事务持续期间，会禁止其它的事务对其进行更新，避免出现脏读和不可重复读，但是还是会出现幻读(这是表中整体数据数目的改变)目前是MySQL默认的隔离级别
serializable	串行化	在一个事务使用这个表期间，所有其它的事务都无法对这个表进行增删改，由此来保证数据的一致，能解决所有的并发问题，但是性能低下

---

如何查看默认的隔离级别？

#语句：
 show @@tx_isolation;

---

为了下面的演示，这里先创建一张表,并放入一些数据；

create table test2(
Id int primary key,
Mname varchar(24) not null都是
)

insert into test2 values(1,"神"),(2,"神秘的天");

---

脏读：(以下为演示代码及结果显示)重点是读和写的同样的隔离级别；

#首先可以去命令行连接上数据库，执行如下语句；此为命令行语句
set autocommit=0;#关闭自动提交
#将隔离级别设置到最低才能看到脏读；
 
begin#开始事务；
update test2
set Mname="sd"
where Id=1;
#先不要结束，等到另一个连接执行完再结束；
rollback;#如果修改有误，可以回滚，不提交；但是之前的数据还是被读出去了；

# 此为另一个数据库连接执行的代码，查询
#同样先将数据库隔离级别设置为最低的；
set session transaction isolation level read uncommitted;
select * from test2;

查询到的结果：(此时还是未提交的时候)

当察觉到自己修改的数据有误，将修改回滚之后，查询到的数据结果：

---

不可重读的和前面的脏读真的类似，只是说，不可重读是一个事务A先修改，但是没提交，于是导致另一个事务B读出数据之后，过一定时间，等到A提交后B再次读取就会发现两次读取的数据不一样了；(很好理解，不在粘贴代码，占位置)

---

接下来是幻读的实例：完成这个实例的基础是先完成了脏读的实例，因为有些步骤是重复的，就不再次记录了；
使用的基础表还是最开始的test2;
这是在命令行端的代码

#下面两个代码我标了执行步骤；
select * from test2;#开始查看是2行数据  ...1
begin;#比Navicat中的代码先开始， ....2
update test2 set Mname = "全改了算了";#修改所有的数据，命令行显示修改了3行，但是实际上只有两行    ....6
commit;#比Navicat中的代码后结束，就容易产生幻读；  ...7

在Navicat中的代码：

#关闭自动提交
set autocommit=0;    ..3

insert into test2 values(3,"雨");  ...4
commit;      ....5
select * from test2;  ...8

幻读,就是指在你已经开始对一张表中的数据进行了处理，但是在你处理的同时，又有了新的数据放进来；导致你处理的时候可能会多处理一行数据；

---

3. savapoint(保存点)

有的时候一个事务的语句很多的时候，不可能一口气全部直接写完，不然一旦出错，或者想修改了也会很不方便，一般可以设置一些保存点，也就是没写一些语句，设置一个Sava point,这样方便回滚；

#简单的使用语法
insert into test2 values(1,"雨");
savepoint 保存点名字;
insert into test2 values(2,"雨");
rollback to 保存点的名字;#这样就可以返回到第一条语句执行后的时候了；

MySQL部分学习记录

show engines;#查看MySQL的存储引擎
show variables like 'autocommit';#查看自动提交的状态(是开启开始关闭)
truncate table 表名;#删除表中的所有数据；