mysql prepare in方法_MySQL PREPARE statement in stored procedures

问题

I have this sql file:

USE mydb;

DROP PROCEDURE IF EXISTS execSql;

DELIMITER //

CREATE PROCEDURE execSql (

IN sqlq VARCHAR(5000)

) COMMENT 'Executes the statement'

BEGIN

PREPARE stmt FROM sqlq;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

When I try to run it with

# cat file.sql | mysql -p

I get

ERROR 1064 (42000) at line 6: You have an error in your SQL syntax; check the manual

that corresponds to your MySQL server version for the right syntax to use near 'sqlq;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END' at line 5

What am I doing wrong?

回答1:

you can only prepare and execute sql that's a string literal or a user variable that contains the text of the statement. try

USE mydb;

DROP PROCEDURE IF EXISTS execSql;

DELIMITER //

CREATE PROCEDURE execSql (

IN sqlq VARCHAR(5000)

) COMMENT 'Executes the statement'

BEGIN

SET @sqlv=sqlq;

PREPARE stmt FROM @sqlv;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

END //

DELIMITER ;

回答2:

You can use Concatenate, PREPARE and EXECUTE statements as bellow..

CREATE DEFINER=`products`@`localhost` PROCEDURE `generateMeritList`(

IN `mastercategory_id` INT(11),

IN `masterschools_id` INT(11)

)

NO SQL

begin

declare total int default 0;

declare conditions varchar(255) default '';

declare finalQuery varchar(60000) default '';

if mastercategory_id > 0 THEN

SET conditions = CONCAT(' and app.masterschools_id = ', mastercategory_id);

end if;

SET @finalQuery = CONCAT(

"SELECT * FROM applications app INNER JOIN masterschools school ON school.id = app.masterschools_id

WHERE

(app.status = 'active'", conditions, " LIMIT ",total);

PREPARE stmt FROM @finalQuery;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

end

来源：https://stackoverflow.com/questions/5895383/mysql-prepare-statement-in-stored-procedures