mysql：day4--存储过程、事务处理

朱雀 2022-07-19 01:22 191阅读 0赞

# 小知识点： #

## 主键自动增长列： ##

加一个AUTO\_INCREMENT

## unique ##

字段唯一性约束

CREATE TABLE test1 (
      id  INT AUTO_INCREMENT PRIMARY KEY,/*要int类型的才能自动增长*/
      nm VARCHAR(32) UNIQUE
    );
    INSERT INTO test1 VALUES (1,'小月月');
    INSERT INTO test1(nm) VALUE('我曾挚爱曹思琪');/*第一次可以执行,并且自动增长了id*/
    INSERT INTO test1(nm) VALUE('我曾挚爱曹思琪');/*再次执行不行，因为nm是unique的要唯一*/

# 存储过程 #

## 语法： ##

存储过程是保存在数据库上的一段可执行代码。  
它的语法是：  
Create procecure sp\_name (参数..)  
Begin  
SQL语句  
End ;  
DELIMITER ;  
调用它的方法:  
Call sp\_name(参数…); //空参时则括号中不填内容  
这里我们要把默认的语句结束符”;”改成其他的，这样存储过程中定义的分号就不会被看成是语句结束，否则会直接被提交。最后，再把“;”还原默认的结束符

## 无参的存储过程 ##

DELIMITER $$
    CREATE PROCEDURE p1()
    BEGIN
        INSERT INTO test1 VALUES(3,'酥酥');
        SELECT * FROM test1; 
    END $$
    DELIMITER ;
    CALL p1;

执行结果：  
1、Stored Procs会增加一个存储过程  
![这里写图片描述][20160807233754068]  
2、调用p1会执行里面的语句：  
![这里写图片描述][20160807233610723]

## 有参的存储过程 ##

DELIMITER $$
    CREATE PROCEDURE p2(IN id INT,IN nm VARCHAR(32))
    BEGIN 
        INSERT INTO test1 VALUES(id,nm);
        SELECT * FROM test1;
    END $$

执行结果：Stored Procs会增加一个存储过程p2  
![这里写图片描述][20160807233834099]

CALL p2(4,'大浪比普朗东');

执行结果如图：  
![这里写图片描述][20160807233920398]

## 带返回值的有参存储过程–方式1 ##

DELIMITER $$
    CREATE PROCEDURE p3(IN id INT , IN nm VARCHAR(32),OUT num INT )
    BEGIN 
        INSERT INTO test1 VALUES(id,nm);
        SELECT * FROM test1 ORDER BY id;
        SELECT COUNT(*) INTO num  FROM test1 ;
    END $$
    DELIMITER ;

执行结果为：Stored Procs会增加一个存储过程p3![这里写图片描述][20160807234043556]  
调用p3 结果为：  
![这里写图片描述][20160807234203276]  
此时返回值为`@returnvalue`，查询返回值

SELECT @returnvalue;

结果为：![这里写图片描述][20160807234244054]

## 带返回值的有参存储–方式2 ##

DELIMITER $$
    CREATE PROCEDURE p4(IN id INT , IN nm VARCHAR(32) )
    BEGIN 
        INSERT INTO test1 VALUES(id,nm);
        SELECT * FROM test1 ORDER BY id;
        SELECT nm AS vv FROM test1 WHERE id=2;
        SET @returnvalue = nm ;
    END $$
    DELIMITER ;

执行结果为：Stored Procs会增加一个存储过程p4  
![这里写图片描述][20160807234438039]  
调用p4：  
![这里写图片描述][20160807234453404]  
此时返回值为`@returnvalue`，查询返回值：

SELECT @returnvalue;

结果为：  
![这里写图片描述][20160807234541930]

## 细节： ##

显示用户定义的变量是

SELECT @returnvalue;

即用户定义的变量名称：`@变量名`  
而系统变量名称要`@@变量名`

# 事务处理 #

## 事务简介 ##

原子性(atomicity)：组成事务处理的语句形成了一个逻辑单元，不能只执行其中的一部分。  
一致性(consistency)：在事务处理执行前后，数据库是一致的(数据库数据完整性约束)。  
隔离性(isolcation)：一个事务处理对另一个事务处理的影响。  
持续性(durability)：事务处理的效果能够被永久保存下来 。  
一个事务只会有一个结果：要么成功、要么失败。  
Start transaction;开始一个事务。  
Commit;提交所做的修改。  
Rollback;回滚所做的修改。如果在操作时出错，应该从新开始一个事务。  
总结：事务从Start transaction到Commit或者Rollback结束，这中间的语句是一个整体，如果执行Rollback，那么这些动作都会回滚。如果执行Commit那么这些动作全部执行成功。

## java中实现事务 ##

要将自动提交设为false，最后再设置回去，关闭连接。

con.setAutoCommit(false);
    /*语句代码*/
    con.setAutoCommit(true);
    con.closse();

## 事务的隔离级别 ##

![这里写图片描述][20160807235228821]

### 设置事务的隔离级别 ###

在mySql的命令行设置隔离级别。  
在MySql的命令行设置隔离级别，只对此打开的命令行窗口有效。第一个命令行窗口，就是一个独立的客户端。  
Select @@tx\_isolation; 可以查看目前的隔离级别。  
Set transaction isolation level <级别名称>可以设置隔离级别。  
级别名称为：\{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE \}  
需要注意的是，隔离级别必须要在事务当中使用，没有事务，隔离级别也没有意义了。

### 读未提交示例操作过程-Read uncommitted ###

1、打开两个MySql的命令提示行，均进入相同数据库，并检查当前表内容为相同数据

2、在A、B两端执行select @@tx\_isolation;检查当前默认的隔离级别别，可以发现都是  
Repeatable Read – 可重复读-（在当前事务内，重复读取第一次读取过的数据就叫可重复读。）

3、修改A端的隔离级别为read uncommitted – 读未提交。意思是可以读取别人没有提交的数据。  
set transaction isolation level read uncommitted;  
在A端的的MySql5.5上请执行:  
Set session transaction isolation level read uncommitted;  
然后再查看是否已经发生改变：

4、在A、B两端都开启事务  
start transaction;  
5、在B端修改一行数据如：  
update stud set name=’Jhon’ where id=1;  
其后在A端执行查询：select \* from stud;

6、此时B端再次执行回滚操作  
Rollback;  
再在A端进行查询，结果发现数据又回到了之前的数据。这就是脏读：

7、对于B端写入的新数据，如果没有提交A端也一样能查询到，这叫幻读。  
需要注意的是，当执行完成一个操作，无论你是rollback还是commit，事务的隔离级别都会再回到默认级别，即：  
Repeatable read。所以，要想演示必须重新设置A端的隔离级别。  
**个人总结**：**一个事务A只要操作，即使没有提交另一个事务B也能读到**

### 读已提交操作过程：-read COMMITTED ###

1、检查A、B两端是否一致：

2、修改A端（左）的隔离级别为read committed;  
set transaction isolation level read committed;  
在A端开启事务：  
start transaction;  
在B端开启事务

3、在A端进行查询：  
Select \* from stud;  
在B端修改一行记录并提交  
Update stud set name=’itcast’ where id=1;  
再回到A端进行查询，发现在同一个事务内，两次查询的结果不一样：  
**个人总结**：**一个事务A操作未提交时，在A事务中能查到 但在另一个事务B中读不到A事务操作后的数据**

### 可重复读示例Repeatable Read ###

1、查看A端的隔离级别是否为Repeatable read级别：  
Select @@tx\_isolation;  
2、先在A端在开启的事务内进行查询。  
然后在B端修改数据库的内容。  
最后再在A端的同一事务内进行查询，发现结果一致。  
**个人总结：事务A，事务B同时开启事务‘start transaction’ 此时事务A做了操作，提交后但此时事务B中独读取到的还是之前的数据 只有当事务B结束事务后，才能看到A事务操作后的数据但如果事务B不用‘start transaction’ 那么，可以读到此时事务A操作后的数据**

### Serializable 串行化 –是最高级的隔离级别 ###

在A端设置隔离级别为Serializable  
set transaction isolation level serializable;  
在A端开启一个事务-并对stud表进行查询。  
在B端开启一个事务，并写入一行记录。此时发现B的代码并没有执行，因为它在等A提交之后它才执行。  
类似于线程同步的概念  
**个人总结：**：**事务A开启事务，事务B设置串行化并开启事务，此时，事务B不结束事务，A事务中的操作就会一直等待B事务结束才能执行，相当于B事务锁住了线程**

### 特别注意： ###

事务的隔离级别在提交后会失效，所以如果想要再次用到事务隔离则要重新设置。

# mysql第四天记录 #

/*主键自动增长列和unique唯一字段*/
    CREATE TABLE test1 (
      id  INT AUTO_INCREMENT PRIMARY KEY,/*要int类型的才能自动增长*/
      nm VARCHAR(32) UNIQUE
    );
    INSERT INTO test1 VALUES (1,'小月月');
    INSERT INTO test1(nm) VALUE('我曾挚爱曹思琪');/*第一次可以执行,并且自动增长了id*/
    INSERT INTO test1(nm) VALUE('我曾挚爱曹思琪');/*再次执行不行，因为nm是unique的要唯一*/
    
    
    /*存储过程*/
    /*
    create procedure 过程名(参数)
    begin
        多条语句
    end 
    */
    /*调用：call 过程名（实参）*/
    /*细节：
    要把默认的语句结束符';' 改成其他的，这样存储过程中，
    定义的分号就不会被看成是语句结束，否则会直接被当成语句结束提交。
    最后，再把';' 还原成默认的结束符
    */
    /*无参的存储过程*/
    DELIMITER $$
    CREATE PROCEDURE p1()
    BEGIN
        INSERT INTO test1 VALUES(3,'酥酥');
        SELECT * FROM test1; 
    END $$
    DELIMITER ;
    CALL p1;
    /*有参的存储过程*/
    DELIMITER $$
    CREATE PROCEDURE p2(IN id INT,IN nm VARCHAR(32))
    BEGIN 
        INSERT INTO test1 VALUES(id,nm);
        SELECT * FROM test1;
    END $$
    DELIMITER ;
    CALL p2(4,'大浪比普朗东');
    /*带返回值的有参存储过程*/
    DELIMITER $$
    CREATE PROCEDURE p3(IN id INT , IN nm VARCHAR(32),OUT num INT )
    BEGIN 
        INSERT INTO test1 VALUES(id,nm);
        SELECT * FROM test1 ORDER BY id;
        SELECT COUNT(*) INTO num  FROM test1 ;
    END $$
    DELIMITER ;
    CALL p3(7,'漠漠',@returnvalue);
    SELECT @returnvalue;
    
    DELIMITER $$
    CREATE PROCEDURE p4(IN id INT , IN nm VARCHAR(32) )
    BEGIN 
        INSERT INTO test1 VALUES(id,nm);
        SELECT * FROM test1 ORDER BY id;
        SELECT nm AS vv FROM test1 WHERE id=2;
        SET @returnvalue = nm ;
    END $$
    DELIMITER ;
    CALL p4(8,'杨龙是个大混子');
    SELECT @returnvalue;
    /*这里我们自己定义的returnvalue是session级别的 其实可以不要特意的去声明*/
    /*系统变量名称:@@变量名   用户变量名称:@变量名*/
    
    /*事务处理*/
    /*原子性、一致性、隔离性、持续性 */
    /*一个事务只会有一个结果：要么都成功，要么都不成功*/
    START TRANSACTION;
    DELETE FROM test1 WHERE id=5;
    UPDATE test1 SET nm='辅助是个萝莉控' WHERE id=1;
    ROLLBACK;
    COMMIT;
    /*ROLLBACK回滚 /  COMMIT提交事物 */
    /*COMMIT 和ROLLBACK 只执行一个*/
    /*从START TRANSACTION到ROLLBACK或者COMMIT结束，这中间的语句是一个整体，如果执行ROLLBACK那么这些动作都会回滚，如果执行COMMIT那么就会全部成功执行提交*/
    
    /*查询事务的隔离级别*/
    SELECT @@tx_isolation;
    /*设置事务级别*/
    
    /*1读未提交 B事务设置READ UNCOMMITTED*/
    /*一个事务A只要操作，即使没有提交另一个事务B也能读到*/
    SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
    /*2读已提交 B事务设置READ COMMITTED*/
    /*一个事务A操作未提交时，在A事务中能查到 但在另一个事务B中读不到A事务操作后的数据*/
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ;
    /*3 可重复读 --默认的*/
    /*事务A，事务B同时开启事务‘start transaction’ 此时事务A做了操作，提交后
    但此时事务B中独读取到的还是之前的数据 只有当事务B结束事务后，才能看到A事务操作后的数据
    但如果事务B不用‘start transaction’ 那么，可以读到此时事务A操作后的数据*/
    
    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    /*4 串行化--最高级别 B事务设置SERIALIZABLE */
    /*事务A开启事务，事务B设置串行化并开启事务，此时，事务B不结束事务，A事务中的操作就会一直等待B事务结束才能执行，相当于B事务锁住了线程*/
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

[20160807233754068]: /images/20220718/bac1b09c1e5f44ebb467bcf57cfeb025.png
[20160807233610723]: /images/20220718/360969ecf35f47548bc70a84ade06528.png
[20160807233834099]: /images/20220718/b3382bd51f304f0a830cdcffcc4506a1.png
[20160807233920398]: /images/20220718/ff0692c47e194c13bbf47abb218e33cb.png
[20160807234043556]: /images/20220718/e23dde0967d9433ca380dcc9895de5ad.png
[20160807234203276]: /images/20220718/b8dae7700ca24a9bb78f3f02703f3aba.png
[20160807234244054]: /images/20220718/f2e6e7cc449e44639ba457330d2fddf6.png
[20160807234438039]: /images/20220718/48ad642e3c764294b8fcd211a12fe5d9.png
[20160807234453404]: /images/20220718/00d8a21aafac4b28a21bcf91b5ab17a9.png
[20160807234541930]: /images/20220718/62025ee23a4440d6887f0f960b43051b.png
[20160807235228821]: /images/20220718/003488e00d1145ce86dc56fd8374571f.png