mysql学习笔记（8）
一.事件0.引入
在mysql中，事件是一个最小的不可分割的单元，事件能保障一个业务的完整性。
例如银行转账：
a ----> -100
update user set money = money = money - 100 where name = 'a'; b ----> +100
update user set money = money = money + 100 where name = 'b'; 实际程序中，如果只有一条程序执行成功，而另一条没有成功，出现数据不一致。
update user set money = money = money - 100 where name = 'a';
update user set money = money = money + 100 where name = 'b'; 多条sql程序，有同事执行成功的需求。要么就同时失败。
mysql中怎样控制事件？
1.mysql是默认开启事件的（自动提交）。
select @@autocommit; +--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+ 默认事件开启的作用是什么？
当我们去执行一个提及来语句时，效果会立刻体现出来，且不能回滚。
create database bank;
use bank;
create table user(
id int primary key,
name varchar(20),
money int
); insert into user value(1,'张三',2000); mysql> select * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |2000 |
+----+------+-------+ 回滚（rollback）：撤销sql语句执行效果；
rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |2000 |
+----+------+-------+
1 row in set (0.00 sec) 那么该怎样撤销呢？
设置mysql自动提交为FALSE：
set autocommit = 0;
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec) 上面的操作关闭了mysql的自动提交（commit）；
测试一下：
mysql> insert into user value(2,'李四',6000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user;
+----+------+-------+##这是虚拟的表，真是的表没有提交
| id | name | money |
+----+------+-------+
|1 | 张三 |2000 |
|2 | 李四 |6000 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+----+------+-------+##真是的表还未提交。
| id | name | money |
+----+------+-------+
|1 | 张三 |2000 |
+----+------+-------+
1 row in set (0.00 sec) 怎样让他真是的插入数据呢？
##再一次插入数据
mysql> insert into user value(2,'李四',6000);
Query OK, 1 row affected (0.00 sec)
##手动插入数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
##无法撤销
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |2000 |
|2 | 李四 |6000 |
+----+------+-------+
2 rows in set (0.00 sec) 小结
自动提交；@@autocommmit = 1
手动提交；commit;
事件回滚；rollback;
如果说这时候转账，
update user set money = money - 100 where name = '张三';
update user set money = money + 100 where name = '李四'; mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |1900 |
|2 | 李四 |6100 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |2000 |
|2 | 李四 |6000 |
+----+------+-------+
2 rows in set (0.00 sec) 2.手动开启事件
set autocommit = 1;mysql> select @@autocommit;+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+1 row in set (0.00 sec) 除了上述方法之外，begin; 或者 start transaction;都可以手动开启一个事件。
mysql> select *from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |2000 |
|2 | 李四 |6000 |
+----+------+-------+
mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |1900 |
|2 | 李四 |6100 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql>select * from user;
+----+------+-------+## rollback是没有效果的
| id | name | money |
+----+------+-------+
|1 | 张三 |1900 |
|2 | 李四 |6100 |
+----+------+-------+
2 rows in set (0.00 sec) 手动开启事件（1）
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |1800 |
|2 | 李四 |6200 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+----+------+-------+##成功回滚了
| id | name | money |
+----+------+-------+
|1 | 张三 |1900 |
|2 | 李四 |6100 |
+----+------+-------+
2 rows in set (0.00 sec) 手动开启事件（2）
mysql> start transaction;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |1900 |
|2 | 李四 |6100 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1Changed: 1Warnings: 0
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |1800 |
|2 | 李四 |6200 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |1900 |
|2 | 李四 |6100 |
+----+------+-------+
2 rows in set (0.00 sec)
事件开启之后，一旦commit调，就不能回滚。
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |1900 |
|2 | 李四 |6100 |
+----+------+-------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|1 | 张三 |1900 |
|2 | 李四 |6100 |
+----+------+-------+
2 rows in set (0.00 sec) 3.事物的特征
A.原子性：事件是最小的单元，不可分割；
C.一致性：事件要求，同一事物中的sql语句，必须保证同时成功或同时失败；
I . 隔离性：事件a和事件b之间是有隔离性的；
D.持久性：事件一旦竣事（commit; rollback），就不可能返回。
4.事物的开启
①事件默认提交： set autocommit = 0;
②begin;
③start transection;
5.事件手动提交
commit;
6.事件手动回滚
rollback;
7.事物的隔离性
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]