colon separated query using mysql Prepared Statement

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;  

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

... a user variable that contains the text of the SQL statement. The text must represent a single statement, not multiple statements .

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');

Take a look at a demo: http://www.sqlfiddle.com/#!2/6649a/6

链接地址: http://www.djcxy.com/p/75924.html

上一篇: 如何本地化Firefox SDK添加

下一篇: 使用mysql Prepared Statement进行冒号分隔查询