使用mysql Prepared语句执行多个用分号分隔的查询 [英] Execute multiple semi-colon separated query using mysql Prepared Statement
问题描述
我试图在mysql中创建一个存储过程,该存储过程会在每个请求上创建一个新表,并从另一个表中复制内容,并提取所需的数据,最后删除该表.存储过程很大,因此在每次查询后我都无法执行,因此我试图以分号分隔的格式一起执行查询.但是在最终执行时,我得到错误代码:1064. 我尝试的方法是否可行,还是有更好的方法.
I am trying to create a stored procedure in mysql which creates a new table on every request copies the content from another table and extracts the required data and finally drops the table. The stored procedure is quite large so I cant have EXECUTE after every query and thus I am trying to execute the query all together in a semicolon separated format. But on final execution I get Error Code: 1064. Is the approach I am trying possible, or is there a better approach.
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;
推荐答案
否,这是不可能的. PREPARE / EXECUTE stmt
一次只能执行一个查询,许多语句不能合并.
请参见文档: http://dev.mysql.com/doc /refman/5.0/en/prepare.html
No, it is not possible. PREPARE / EXECUTE stmt
can execute only one query at a time, many statements cannot be combined.
See documentation: http://dev.mysql.com/doc/refman/5.0/en/prepare.html
...一个包含SQL语句文本的用户变量.文本必须表示单个语句,而不是多个语句.
无论如何,为了简化您的代码,我将创建一个简单的过程:
Anyway, to simplify your code I would create a simple procedure:
CREATE PROCEDURE exec_qry( p_sql varchar(100))
BEGIN
SET @tquery = p_sql;
PREPARE stmt FROM @tquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
/
我将以这种方式在主过程中调用此过程:
and I would call this procedure in the main procedure, in this way:
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
这篇关于使用mysql Prepared语句执行多个用分号分隔的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!