mysql存储过程动态sql

mysql存储过程中，变量直接拼接在执行sql上会把变量解析成 '变量执行'，比如：

DECLARE v_condition VARCHAR(4) DEFAULT 'id=1';

select * from t where v_condition

此时不会报错，实际执行的sql是 select * from t where 'id=1'

所以需要动态sql的实现如下，这里动态SQL的生成说明如下：

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

    set pSql = concat('update kqcard set ',vFingerIndex,' = \'', vFingerData, '\' , zkkqEnable = 1 where cardNo=\'', vCardNo ,'\';');
    prepare stmt from @Sql;  -- 预处理需要执行的动态SQL，
    EXECUTE stmt;

   deallocate prepare stmt; -- 释放掉预处理段

 

EXECUTE prod USING替换sql语句中的占位符，后面跟要替换占位符的、占位符变量，(sql语句中的占位符是?号)

delimiter $$

DROP PROCEDURE IF EXISTS proc_sql $$ -- 判断proc_sql存储过程存在删除

CREATE PROCEDURE proc_sql () -- 创建proc_sql存储过程

BEGIN declare p1 int; -- 设置p1变量

set p1 = 11; -- 赋值p1变量等于11

set @p1 = p1; -- 赋值字符串占位符变量等于p1

PREPARE prod FROM 'select * from tb2 where nid > ?'; -- 解析字符串为sql语句

EXECUTE prod USING @p1; -- 执行sql语句，并且用占位符变量替换sql语句中的?号 DEALLOCATE

prepare prod; -- 释放解析和执行sql语句

END

$$ delimiter ;

 

 

【预处理语句】

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掉，在存储过程结束之后，该预处理语句仍然会有效