MySQL学习笔记十：游标/动态SQL/临时表/事务

一、游标

1.1、越界标志

在MySQL游标中，可以声明DECLARE CONTINUE HANDLER来操作1个越界标志。

语法：DECLARE CONTINUE HANDLER FOR NOT FOUND STATEMENT;

1.2、REPEAT方式

DELIMITER $$
CREATE PROCEDURE CHANGESEX ()
BEGIN
    DECLARE HAVE INT DEFAULT 1;
    DECLARE PID INT;
    
    DECLARE CURT CURSOR FOR SELECT ID FROM STUDY11;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET HAVE=0;
    OPEN CURT;
    FETCH CURT INTO PID;
    REPEAT
        UPDATE STUDY11 SET SEX=CASE SEX WHEN '男' THEN 'MALE' ELSE 'FEMALE' END WHERE ID=PID;
        FETCH CURT INTO PID;
    UNTIL HAVE=0 END REPEAT;
    CLOSE CURT;
END$$
DELIMITER ;

需要注意的是：MySQL不能像SQL Server那样，使用DEALLOCATE的方式释放游标资源。

1.3、WHILE方式

DELIMITER $$
CREATE PROCEDURE CHANGESEX1 ()
BEGIN
    DECLARE HAVE INT DEFAULT 1;
    DECLARE PID INT;
    
    DECLARE CURT CURSOR FOR SELECT ID FROM STUDY11;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET HAVE=0;
    OPEN CURT;
    FETCH CURT INTO PID;
    WHILE HAVE=1 DO
        UPDATE STUDY11 SET SEX=CASE SEX WHEN 'MALE' THEN '男' ELSE '女' END WHERE ID=PID;
        FETCH CURT INTO PID;
    END WHILE;
    CLOSE CURT;
END$$
DELIMITER ;

二、动态SQL

2.1、动态SQL格式

SET SQL= (预处理的SQL语句，可以使用CONCAT拼接的语句，参数用 ？代替 。);

SET @SQL=SQL;

PREPARE STMT_NAME FROM @SQL;

SET @VAR_NAME=XXX;

EXECUTE STMT_NAME [USING @VAR_NAME[,@VAR_NAME]...];

{DEALLOCATE | DROP} PREPARE STMT_NAME;

2.2、动态SQL示例

DELIMITER $$
CREATE PROCEDURE GETNAME (IN PID INT)
BEGIN
    #定义预处理SQL语句
    DECLARE STRSQL VARCHAR(1000);
    
    #拼接SQL语句
    SET STRSQL="SELECT NAME FROM EMP WHERE ID=?";
    
    #将自定义变量赋值给用户变量
    SET @SQL=STRSQL;
    
    #预处理动态SQL语句
    PREPARE STMT FROM @SQL;
    
    #传递动态SQL参数
    SET @PARAM1=PID;
    
    #执行动态SQL语句
    EXECUTE STMT USING @PARAM1;
    
    #释放PREPARE
    DEALLOCATE PREPARE STMT;
END$$
DELIMITER ;

可以看出，MySQL动态SQL支持""。

2.3、动态SQL注意事项

1）存储动态SQL的值的变量不能是自定义变量，必须是用户变量或者全局变量 。如：SET SQL='XXX';PREPARE STMT FROM SQL;都是错误的写法，正确的写法为：SET @SQL='XXX';PREPARE STMT FROM @SQL; 

2）即使PREPARABLE_STMT语句中的 ? 所代表的是一个字符串，也不需要用引号将 ? 两边包起来。

3）如果动态语句中用到了 IN ，则SQL语句可以写成：SET STRSQL="SELECT NAME FROM EMP WHERE ID IN (?,?,?)";

三、临时表

3.1、临时表创建

方法一：

CREATE TEMPORARY TABLE `emp_t1` (
  `ID` int(11) DEFAULT NULL,
  `NAME` varchar(50) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  KEY `ID_INDEX` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

方法二：

CREATE TEMPORARY TABLE emp_t2 SELECT * FROM EMP WHERE ID<=10;

可以看出，MySQL并不支持像SQL Server那样使用SELECT * INTO #EMP FROM EMP的方式来创建临时表。

3.2、临时表删除

DROP TABLE EMP_T1,EMP_T2;

3.3、临时表测试（存储过程）

1）创建：

DELIMITER $$
CREATE PROCEDURE TempTest1 ()
BEGIN
    #临时表创建方式一测试
    CREATE TEMPORARY TABLE `emp_t1` (
        `ID` int(11) DEFAULT NULL,
        `NAME` varchar(50) DEFAULT NULL,
        `AGE` int(11) DEFAULT NULL,
        KEY `ID_INDEX` (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    INSERT INTO EMP_T1 SELECT * FROM EMP LIMIT 10;
    
    #临时表创建方式二测试
    CREATE TEMPORARY TABLE emp_t2 SELECT * FROM EMP WHERE ID<=10;
    
    #结果返回
    SELECT * FROM EMP_T1 UNION SELECT * FROM EMP_T2;
    
    #临时表删除
    DROP TABLE EMP_T1,EMP_T2;
END$$
DELIMITER ;

2）调用：

CALL TempTest1();

3）结果：

3.4、自定义函数能使用临时表吗？

不可以！自定义函数不支持创建表，不管是正常表还是临时表。（SQL Server自定义函数，也不支持临时表，但是支持表变量。）

四、事务

4.1、基础概念

事务（Transaction）是访问和更新数据库的程序执行单元；事务中可能包含一个或多个sql语句，这些语句要么都执行，要么都不执行。

4.2、MySQL逻辑架构与存储引擎

如上图所示，MySQL逻辑架构从上往下可以分为三层：

1）第一层：处理客户端连接、授权认证等。

2）第二层：服务器层，负责查询语句的解析、优化、缓存以及内置函数的实现、存储过程等。

3）第三层：存储引擎，负责MySQL中数据的存储和提取。

说明1：MySQL中服务器层不管理事务，事务是由存储引擎实现的。

说明2：MySQL支持事务的存储引擎有InnoDB、NDB Cluster等，其中InnoDB的使用最为广泛；其他存储引擎不支持事务，如MyIsam、Memory等。

4.3、事务控制语句

1）BEGIN 或 START TRANSACTION 显式地开启一个事务；

2）COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

3）ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

4）SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

5）RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

6）ROLLBACK TO identifier 把事务回滚到标记点；

7）SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

4.4、事务提交模式

SET AUTOCOMMIT=1 开启自动提交

SET AUTOCOMMIT=0 禁止自动提交

默认是开启自动提交，可以使用以下命令查询：

SHOW VARIABLES LIKE 'AUTOCOMMIT';

4.5、事务示例

4.5.1、无判断语句事务

DELIMITER $$
CREATE PROCEDURE TranTest1 ()
BEGIN
    #临时表创建
    CREATE TEMPORARY TABLE EMP_T1 SELECT * FROM EMP WHERE 1=2;
    
    #开启事务
    START TRANSACTION;
    
    #数据插入
    INSERT INTO EMP_T1 VALUES (1,'HELLO',18);
    INSERT INTO EMP_T1 VALUES (2,'WORLD',19);
    
    #提交事务
    COMMIT;
    
    #结果返回
    SELECT * FROM EMP_T1;
    
    #临时表删除
    DROP TABLE EMP_T1;
END$$
DELIMITER ;

4.5.2、有判断语句事务（推荐）

DELIMITER $$
CREATE PROCEDURE TranTest2 ()
BEGIN
    #判断事务是否异常的错误变量
    DECLARE PERROR INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET PERROR=1;

    #临时表创建
    CREATE TEMPORARY TABLE EMP_T1 SELECT * FROM EMP WHERE 1=2;
    
    #开启事务
    START TRANSACTION;

    #数据插入
    INSERT INTO EMP_T1 VALUES (1,'HELLO',18);
    INSERT INTO EMP_T1 VALUES (2,'WORLD',19);
    
    #提交事务
    IF (PERROR=0) THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
    
    #结果返回
    SELECT * FROM EMP_T1;
    
    #临时表删除
    DROP TABLE EMP_T1;
END$$
DELIMITER ;

说明：SQL Server中事务执行是否有报错，可以使用@@ERROR来判断，@@ERROR=0代表无报错。