MySQL - 动态SQL与预处理语句

在存储过程或者函数中，有时SQL语句是通过变量传值生成的。这时候就需要使用动态SQL，如果直接在SQL语句中植入变量，将提示无该字段或表。

如下所示：

v_db 和v_table均是变量传值动态生成。

create table v_table like  v_db.v_table;
1

直接执行将提示错误。

---

【动态SQL】

动态SQL示例如下(在存储过程中使用)：

CREATE  PROCEDURE `proc_copy_table`(IN v_table VARCHAR(20),IN v_db VARCHAR(20),OUT o_result int(4))
BEGIN
    DECLARE exit HANDLER FOR SQLEXCEPTION  
    begin
        rollback; -- 有异常，进行回滚
        set o_result = -500;
    end;

    START TRANSACTION;
        SET @sql1 = CONCAT('drop table if EXISTS ',v_table,';');
        -- drop table if EXISTS v_table;
      prepare stmt from @sql1;  -- 预处理需要执行的动态SQL，
        EXECUTE stmt;
        deallocate prepare stmt;     -- 释放掉预处理段


        set @sql2 = CONCAT('create table ',v_table,' like ',v_db,'.',v_table,';');
        -- create table v_table like  v_db.v_table;
        prepare stmt from @sql2;  -- 预处理需要执行的动态SQL，
        EXECUTE stmt;
        deallocate prepare stmt;     -- 释放掉预处理段

        set @sql3 = CONCAT('insert into  ',v_table,' select * from  ',v_db,'.',v_table,';');
        -- insert into v_table select * from v_db.v_table;
        prepare stmt from @sql3;  -- 预处理需要执行的动态SQL，
        EXECUTE stmt;
        deallocate prepare stmt;     -- 释放掉预处理段
  COMMIT;
    set o_result :=0;
end
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

这里动态SQL的生成说明如下：

使用concat拼接，将变量传值进去：

SET @sql1 = CONCAT('drop table if EXISTS ',v_table,';');

-- 效果如下：
 drop table if EXISTS [v_table实际变量值];
1
2
3
4

---

【预处理语句】

MySQL官方将prepare、execute、deallocate统称为PREPARE STATEMENT。

即，预处理语句。

其用法十分简单：

//获取预处理语句
PREPARE stmt_name FROM preparable_stmt;

//执行预处理语句(可传入用户变量)
EXECUTE stmt_name
    [USING @var_name [, @var_name] ...];

//释放掉预处理资源
{DEALLOCATE | DROP} PREPARE stmt_name;
1
2
3
4
5
6
7
8
9

使用PAREPARE STATEMENT可以减少每次执行SQL的语法分析，

比如用于执行带有WHERE条件的SELECT和DELETE，或者UPDATE，或者INSERT，只需要每次修改变量值即可。

同样可以防止SQL注入，参数值可以包含转义符和定界符。

PREPARE … FROM可以直接接用户变量：

    SET @sql1 = CONCAT('drop table if EXISTS ',v_table,';');

      prepare stmt from @sql1;  -- 预处理需要执行的动态SQL，
1
2
3

每一次执行完EXECUTE时，养成好习惯，须执行DEALLOCATE PREPARE …语句，这样可以释放执行中使用的所有数据库资源（如游标）。

不仅如此，如果一个session的预处理语句过多，可能会达到max_prepared_stmt_count的上限值。

预处理语句只能在创建者的会话中可以使用，其他会话是无法使用的。而且在任意方式（正常或非正常）退出会话时，之前定义好的预处理语句将不复存在。

如果在存储过程中使用，如果不在过程中DEALLOCATE掉，在存储过程结束之后，该预处理语句仍然会有效。