在存储过程或者函数中,有时SQL语句是通过变量传值生成的。这时候就需要使用动态SQL,如果直接在SQL语句中植入变量,将提示无该字段或表。
如下所示:
v_db 和v_table均是变量传值动态生成。
create table v_table like v_db.v_table;
直接执行将提示错误。
【动态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
这里动态SQL的生成说明如下:
使用concat拼接,将变量传值进去:
SET @sql1 = CONCAT('drop table if EXISTS ',v_table,';');
-- 效果如下:
drop table if EXISTS [v_table实际变量值];
【预处理语句】
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;
使用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,
每一次执行完EXECUTE时,养成好习惯,须执行DEALLOCATE PREPARE …
语句,这样可以释放执行中使用的所有数据库资源(如游标)。
不仅如此,如果一个session的预处理语句过多,可能会达到max_prepared_stmt_count的上限值。
预处理语句只能在创建者的会话中可以使用,其他会话是无法使用的。而且在任意方式(正常或非正常)退出会话时,之前定义好的预处理语句将不复存在。
如果在存储过程中使用,如果不在过程中DEALLOCATE掉,在存储过程结束之后,该预处理语句仍然会有效。