MySQL record 06 part

事务、存储过程
事务：

MySQL的同步，同步是指 together done，要么一起前进，要么一起后退的意思。

注意，回滚 rollback 对已经提交 commit 的数据是无效的，也就是说，只能对没有被提交 commit 的数据进行回滚 rollback。

一个转账的案例如下：

a 的账户减少100，同时 b 的账户增加100

注意，MySQL默认是直接自动commit的，当commit之后就是做了持久化的操作，是不可回滚的，
所以要想不让MySQL自动commit的话，要以START TRANSACTION;声明，这样，在其后的语句就不会被MySQL自动commit了，直到用户输入了commit才会完成持久化的操作，也就是，以START TRANSACTION;起始的接下来的语句，在用户输入commit终结之前，所有的语句都可以rollback进行回滚，

在这里插入图片描述

setember2024the18thWednesday

事务的回滚，

请注意，回滚前面有定语修饰，事务的回滚，而前面已经讲过，commit的出现表示事务的完结并持久化，也就是说在事务没有结束之前，即在没有出现commit的时候，事务中的语句是允许回滚操作的。
另一个需要注意的点是，在当前事务中进行使用了查询语句，是可以看到数据有改变的，但这只是表明在此事务中的数据的变化，并不是持久化后的最终数据，换个窗口或者工具进行查询就能看到数据并没有变化，所以说，在事务中使用查询语句得到结果只反应此事务中数据的变化，并不是持久化后的最终数据。

事务的隔离级别：

数据库中最高的隔离级别是‘可串行化’，在MySQL中的默认隔离级别是‘可重复读’，

设置全局隔离级别：
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
查询隔离级别语句：
SELECT @@transaction_isolation;
MySQL默认的隔离级别是：REPEATABLE-READ
设置语句是：
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
注意，如果使用隔离设置语句后，必须更换查询工具（或者关闭并重新打开当前查询工具）才能看出隔离级别的变化，一定要切记：改变了隔离级别后，一定要关闭查询工具，或者立即开启其他的查询工具，这样才能看到正确的修改后的隔离级别。

举例，一个读到`未提交`（隔离级别是READ-`UNCOMMITTED`）的脏读：

通过设置全局隔离级别：读到未提交 READ-UNCOMMITTED
±------------------------+ | @@transaction_isolation |
±------------------------+ | READ-UNCOMMITTED |
初始数据为： a、b 各1000
然后，在此隔离级别下演示脏读，执行下面语句：
START TRANSACTION;
UPDATE account SET
money=money-100 WHERE name=‘a’;
UPDATE account SET money=money+100
WHERE name=‘b’;
注意，这里开启了事务 START TRANSACTION，但并没有提交COMMIT，
在此事务中查询到的结果：

使用其他查询工具也能查到这个未提交的数据变化， mysql> select * from account;
±—±-----±------+ | id | name | money |
±—±-----±------+ | 1 | a | 900 | | 2 | b | 1100 |

这就是在‘读到未提交 READ-UNCOMMITTED ’隔离级别下，开启事务但未提交的时候，出现的脏读。
读到未提交的意思是，在一个用户开启事务但没提交之前对数所做的更改，被其他用户读到的现象。正常情况下，a用户开启事务后对数据的修改，其他用户是应该读不到数据发生的变化，直到a用户做提交动作后，其他用户才能读到已经变化的数据，

举例，一个读到`已提交`（隔离级别是READ-`COMMITTED`）：

通过设置全局隔离级别：读到已提交 READ-COMMITTED
±------------------------+ | @@transaction_isolation |
±------------------------+ | READ-UNCOMMITTED |
初始数据为： a、b 各1000

然后，在此隔离级别下演示脏读，执行下面语句：
START TRANSACTION;
UPDATE account SET money=money+100 WHERE name=‘a’;
UPDATE account SET money=money-100 WHERE name=‘b’;
注意，这里开启了事务 START TRANSACTION，但并没有提交COMMIT，
在此事务中查询到的结果：

使用其他查询工具对这个未提交的数据变化进行查询：
mysql> select * from account;
±—±-----±------+
| id | name | money |
±—±-----±------+
| 1 | a | 1000 |
| 2 | b | 1000 |
±—±-----±------+
发现，数据没有变化，是没有出现脏读的情况，
这说明,在读到已提交 READ-COMMITTED 这个隔离级别下，a用户开启了事务后在没有做提交动作之前对数据的修改，其他的用户是看不到a用户对数据的修改的，因为隔离级别是读到已提交，所以是只有被提交的数据变化才能被其他用户读到。因此读到已提交 READ-COMMITTED 这个隔离级别是没有脏读的情况的。

存储过程：
在这里插入图片描述
就是把自己工作中常用到或重复用到的MySQL语句打包到一起，形成代码块，下次干活的时候，直接调用这个代码块就可以了，就不用一个语句一个语句的去手工执行了，大大减少了工作量。
一个简单的存储过程的创建和调用，

创建语句：
CREATE PROCEDURE proc()
BEGIN
SELECT * FROM student;
END;
调用语句：
call proc();

存储过程中的变量：

september2024the20thFriday
DECLARE var_name[,varname]…date_type[DEFAULT value];

一个存储过程的例子：
DECLARE myvariable INT DEFAULT 100;
BEGIN
DECLARE s_grade FLOAT;
DECLARE s_gender CHAR(2);
SELECT grade,gender INTO s_grade,s_gender FROM student WHERE name=‘rose’;
SELECT s_grade,s_gender;
END;
调用存储过程：
call mypr;
或者
CALL mypr();

删除存储过程：
DROP PROCEDURE doiterate;
注意，删除存储过程使用的命令是PROCEDURE 并且存储过程名字后面不带小括号，

一个有意思的存储过程案例：
CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop:LOOP
SET p1=p1+1;
IF p1<10 THEN ITERATE my_loop;
ELSEIF p1>20 THEN LEAVE my_loop;
END IF;
SELECT p1;
END LOOP my_loop;
END
这个存储进程被call的时候，会出现10个打印结果，分别是
±-----+ | p1 |
±-----+ | 10 |
±-----+ 1 row in set (0.00 sec)

±-----+ | p1 |
±-----+ | 11 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 12 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 13 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 14 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 15 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 16 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 17 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 18 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 19 |
±-----+ 1 row in set (0.01 sec)

±-----+ | p1 |
±-----+ | 20 |
±-----+ 1 row in set (0.01 sec)
当 p1<10的时候，每次迭代（进入循环）只执行语句 ITERATE my_loop，而语句 ITERATE my_loop 下面的语句是没有被执行的，直到，p1<10 不成立的时候，才开始执行 ITERATE my_loop 下面语句 ELSEIF p1>20 THEN LEAVE my_loop ，但 p1>20 不成立，因此继续执行下面的语句 END IF 和
SELECT p1 （这时打印的是 10），执行完 SELECT p1 是没有看到结束循环的语句的，因此，会再次进入循环，然后，再次验证 p1<10 不成立，开始执行 ELSEIF p1>20 THEN LEAVE my_loop ，但 p1>20 不成立，因此继续执行下面的语句 END IF 和
SELECT p1 （这时打印的是 11），执行完 SELECT p1 是没有看到结束循环的语句的，因此，会再次进入循环，
直到打印20后，再次进入循环，SET p1=p1+1后 p1=21，执行 ELSEIF p1>20 THEN LEAVE my_loop; p1>20 条件成立，退出循环，再关闭循环，END LOOP my_loop，最后关闭存储进程 END
注意，退出循环使用的语句是 LEAVE，结束循环使用的语句是：END LOOP，两者是不一样的。
查看数据库中包含的以大C开头的所有存储进程，
SHOW PROCEDURE STATUS LIKE’C%';

查看数据库中存储过程创建时使用的语句，
SHOW CREATE PROCEDURE chapter05.CountPr;
回显的结果是：
CREATE DEFINER=root@localhost PROCEDURE countPr(IN s_gender VARCHAR(50),OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM student WHERE gender=s_gender;
END