Use dynamic sql statements in mysql stored procedures

步履不停

Release： 2019-06-24 18:24:48

Original

3302 people have browsed it

Use dynamic sql statements in mysql stored procedures Usage of each keyword in a simple stored procedure:

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(in _xnb varchar(50))
BEGIN
	## 定义变量
	DECLARE _num FLOAT(14,6) DEFAULT 0;
	## @表示全局变量 相当于php $
	## 拼接赋值 INTO 必须要用全局变量不然语句会报错
    ## //CONCAT会把'SELECT SUM('和_xnb和') INTO @tnum FROM btc_user_coin'拼接起来,CONCAT的各个参数中间以","号分割
	SET @strsql = CONCAT('SELECT SUM(',_xnb,') INTO @tnum FROM btc_user_coin');
	## 预处理需要执行的动态SQL，其中stmt是一个变量
	PREPARE stmt FROM @strsql;  
	## 执行SQL语句
	EXECUTE stmt;  
	## 释放掉预处理段
	deallocate prepare stmt;
	## 赋值给定义的变量
	SET _num = @tnum;
	SELECT _num
END;;

Copy after login

Mysql Use dynamic sql statements in stored procedures

After Mysql 5.0, dynamic sql statements are supported. We can get the values we want by passing different parameters

Here are two types of dynamic sql in stored procedures

1.set sql = (preprocessed sql statement, you can use concat Spliced statements)

set @sql = sql

PREPARE stmt_name FROM @sql;

EXECUTE stmt_name;

　{DEALLOCATE | DROP} PREPARE stmt_name;

过程过程示例:

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))BEGIN
    
      declare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句

      set SQL_FOR_SELECT = CONCAT("select * from  user  where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'");   -- 拼接查询sql语句

      set @sql = SQL_FOR_SELECT;      PREPARE stmt FROM @sql;       -- 预处理动态sql语句
      EXECUTE stmt ;                -- 执行sql语句
      deallocate prepare stmt;      -- 释放prepareEND;

Copy after login

上述是一个简单的查询用户表的存储过程，当我们调用此存储过程，可以根据传入不同的参数获得不同的值。

但是：上述存储过程中，我们必须在拼接sql语句之前把USER_ID,USER_NAME定义好，而且在拼接sql语句之后，我们无法改变USER_ID,USER_NAME的值，如下：

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))BEGIN
    
       declare SQL_FOR_SELECT varchar(500);  -- 定义预处理sql语句

       set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'");   -- 拼接查询sql语句

       set @sql = SQL_FOR_SELECT;       PREPARE stmt FROM @sql;        -- 预处理动态sql语句
       EXECUTE stmt ;                 -- 执行sql语句
       deallocate prepare stmt;       -- 释放prepare


       set USER_ID = '2'; -- 主动指定参数USER_ID的值
       set USER_NAME = 'lisi';       set @sql = SQL_FOR_SELECT;       PREPARE stmt FROM @sql;       -- 预处理动态sql语句
       EXECUTE stmt ;                -- 执行sql语句
       deallocate prepare stmt;      -- 释放prepareEND;

Copy after login

　我们用call aa('1','zhangsan');来调用该存储过程，第一次动态执行，我们得到了‘张三’的信息，然后我们在第14,15行将USER_ID，USER_NAME改为lisi，我们希望得到李四的相关信息，可查出来的结果依旧是张三的信息，说明我们在拼接sql语句后，不能再改变参数了。

为了解决这种问题，下面介绍第二中方式：

2.set sql = （预处理的sql语句，可以是用concat拼接的语句，参数用？代替）

　set @sql = sql

　PREPARE stmt_name FROM @sql;

set @var_name = xxx;

EXECUTE stmt_name USING [ USING @var_name [, @var_name] ...];

{DEALLOCATE | DROP} PREPARE

stmt _name;

We can change the above code to:

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME`
varchar(36))BEGIN
SQL_FOR_SELECT VARCHAR (500);-Define the pre-processing SQL statement

stet SQL_FOR_SELECT = "Select*from user where user_id =? And user_name =?";
# set @sql = sql_FOR_SELECT;
Prepare Stmt from @sql;-Pre-processing dynamic SQL statement

stet@Parm1 = User_id; ;

EXECUTE stmt USING @parm1, @parm2; --Execute sql statement
deallocate prepare stmt; --Release prepare

set @sql = SQL_F OR_SELECT;
PREPARE stmt FROM @sql;
# EXECUTE stmt USING @parm1 , @parm2; --Execute sql statement
deallocate prepare stmt; --Release prepare
END;

In this way, we can actually use different parameters (of course You can use dynamic sql by generating different parameters through logic in the stored procedure).

A few notes:

　存储动态SQL的值的变量不能是自定义变量，必须是用户变量或者全局变量如：set sql = 'xxx'; prepare stmt from sql;是错的，正确为： set @sql = 'xxx'; prepare stmt from @sql;

即使 preparable_stmt 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。

如果动态语句中用到了 in ,正常写法应该这样：select * from table_name t where t.field1 in (1,2,3,4,...);

则sql语句应该这样写：set @sql = "select * from user where user_id in (?,?,?) "

因为有可能我不确定in语句里有几个参数，所以我试过这么写

set @sql = "select * from user where user_id in (?) "

然后参数我传的是 "'1','2','3'" 我以为程序会将我的动态sql解析出来（select * from user where user_id in ('1','2','3')）但是并没有解析出来，在写存储过程in里面的列表用个传入参数代入的时候，就需要用到如下方式：

1.使用find_in_set函数

select * from table_name t where find_in_set(t.field1,'1,2,3,4');
Copy after login

2.还可以比较笨实的方法，就是组装字符串，然后执行

DROP PROCEDURE IF EXISTS photography.Proc_Test; CREATE PROCEDURE photography.`Proc_Test`(param1 varchar(1000)) BEGIN set @id = param1; set @sel = 'select * from access_record t where t.ID in ('; set @sel_2 = ')'; set @sentence = concat(@sel,@id,@sel_2); -- 连接字符串生成要执行的SQL语句 prepare stmt from @sentence; -- 预编释一下。 “stmt”预编释变量的名称， execute stmt; -- 执行SQL语句 deallocate prepare stmt; -- 释放资源 END;
Copy after login

The above is the detailed content of Use dynamic sql statements in mysql stored procedures. For more information, please follow other related articles on the PHP Chinese website!