使用MySQL Prepared Statement执行多个分号分隔的查询

Question

我正在尝试在MySQL中创建一个存储过程，该存储过程在每个请求上创建一个新表，复制另一个表的内容并提取所需的数据，最后删除该表。由于存储过程非常大，因此我不能在每个查询之后都有EXECUTE，因此我尝试以分号分隔的格式一起执行查询。但是在最终执行时，我遇到了错误代码：1064。 我尝试的方法可行吗？还是有更好的方法？

SET tableName = (SELECT CONCAT("table",(UNIX_TIMESTAMP(NOW()))));

SET @tquery =CONCAT('CREATE TABLE `',tableName,'` (select pt.* from post_table pt join on user u on pt.user_id=u.id where pt.client="client",pt.group="group");');
SET @tquery = CONCAT(@tquery,' SELECT * FROM ',tableName,';');    
SET @tquery = CONCAT(@tquery,' DROP TABLE ',tableName,';');    
PREPARE stmt FROM @tquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;  

- thickGlass

Answer 1

不可能。 PREPARE / EXECUTE stmt 一次只能执行一个查询，无法合并多个语句。
请参阅文档：http://dev.mysql.com/doc/refman/5.0/en/prepare.html ... 包含 SQL 语句文本的用户变量。该文本必须代表一个语句，而不是多个语句。

无论如何，为了简化您的代码，我会创建一个简单的过程：

CREATE PROCEDURE exec_qry( p_sql varchar(100))
BEGIN
  SET @tquery = p_sql;
  PREPARE stmt FROM @tquery;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END 
/

然后我会在主过程中调用这个过程，方法如下：

CALL exec_qry( 'CREATE TABLE t2 AS SELECT * FROM test');
CALL exec_qry( 'SELECT * FROM t2');
CALL exec_qry( 'SELECT count(*) FROM t2');
CALL exec_qry( 'SELECT avg(x) FROM t2');
CALL exec_qry( 'DROP TABLE t2');

请查看演示：http://www.sqlfiddle.com/#!2/6649a/6

- krokodilko

---

我想我只能这样做了。 - thickGlass

Answer 2

谢谢，@krokodilko。你的回答很有用：）。

我接受了你的建议，并添加了一个循环，可以通过单个字符串参数执行多个查询，传递给存储过程。

我猜这可能在现实世界中没有太多的实用性，但只是为了分享一下。当我学习mysql并在存储过程和函数中反复想要删除然后创建表格时，我浪费了很多时间追逐自己的尾巴，直到意识到使用PREPARE/EXECUTE无法执行多个语句。

总之，就是这样。

# mysql_splitprepareexecutemulti.sql
# 
# combine codebits from these two pages: 
# 1 - https://dev59.com/WWIj5IYBdhLWcg3wUjzG
# 2 - https://sebhastian.com/mysql-split-string/
# 
# Not gonna bother validate the sql at all. May not even handle 
# empty string, no delimiter on end, etc.
# 
# Take query string with presumably 1 or more sql statements
#  - split based on parm string, default to semicolon
#  - loop through parm str sql, prepare each query then execute each.
# 
# 
##############################################
CREATE DEFINER=`root`@`localhost` PROCEDURE `mssp_execStrungQueries`(
  IN str text,
  IN delimiterChar CHAR(1) 
)
BEGIN 
  SET @inputString = str;

  WHILE LOCATE(delimiterChar,@inputString) > 1 DO
    SET @queryString = SUBSTRING_INDEX(@inputString,delimiterChar,1);
    PREPARE stmt FROM @queryString; EXECUTE stmt; DEALLOCATE PREPARE stmt;

  SET @inputString = REGEXP_REPLACE(@inputString, ( 
      SELECT LEFT(@inputString, LOCATE(delimiterChar, @inputString))),'',1,1); 
 select SLEEP(1); #slow down during dev in case conjur infinite loop 
  END WHILE;
END

##############################################
##############################################
### test calls to the SP:
call mssp_execStrungQueries(';',';');
select 'test just semi colon after call' as afterCallSP;

call mssp_execStrungQueries('','');
select 'test empty str after call' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol');
call mssp_execStrungQueries(@str,';');
select 'Test 1 query NO SEMICOLON test after call.' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol;');
call mssp_execStrungQueries(@str,';');
select 'Test 1 query WITH semicolon test after call.' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol;' 
                 ,'select \'TWOthingBBB\' as twoCol;'
                 ,'select \'TREEtingCCCC\' as threeCol;');
call mssp_execStrungQueries(@str,';');
select 'Test 3 statements after call' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol;' 
                 ,'select \'TWOthingBBB\' as twoCol;'
                 ,'select \'TREEtingCCCC\' as threeCol');
call mssp_execStrungQueries(@str,';');
select 'Test 3 stmts NO SEMI on last' as afterCallSP;

SET @str = CONCAT('select \'ONEthingAAA\' as oneCol;' 
                 ,'select \'TWOthingBBB\' as twoCol '
                 ,'select \'TREEtingCCCC\' as threeCol;');
call mssp_execStrungQueries(@str,';');
select 'Test 3 stmts NO SEMI on 2nd' as afterCallSP;

干杯， :)benja

- Benja