MySQL存储过程：表名为变量

2019独角兽企业重金招聘Python工程师标准>>>

如果一个存储过程的变化的部分只有表名的部分，我们可以给存储过程传入这个表名。这就需要我们承接一下传入的参数，然后使用PREPARE了，关于PREPARE，需要参考官方文档来解释一下，这里先贴上解决问题的代码：

DELIMITER $$

USE `reptail`$$

DROP PROCEDURE IF EXISTS `updateTest`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `updateTest`(IN table_name VARCHAR(50))
BEGIN
    SET @tbl_name=CONCAT("",table_name);
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x1` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    PREPARE stmt FROM @statement;
    EXECUTE stmt;
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x2` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    PREPARE stmt FROM @statement;
    EXECUTE stmt;
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x3` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    PREPARE stmt FROM @statement;
    EXECUTE stmt;
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x4` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    PREPARE stmt FROM @statement;
    EXECUTE stmt;
    SET @statement = CONCAT("UPDATE ",@tbl_name," AS c,`x5` AS b SET c.`hello` = b.`world` WHERE c.`id` = b.`uuid`; ");
    END$$

DELIMITER ;

问题解决了，那么，何为PREPARE呢？时至2017年8月24日20:11:48，官方的手册的解释是：

The PREPARE statement prepares a SQL statement and assigns it a name, stmt_name, by which to refer to the statement later. The prepared statement is executed withEXECUTE and released with DEALLOCATE PREPARE.

翻译为中文的大概意思就是：

PREPARE语句准备一个SQL的statement，并且，它会给这个statement一个名字以便我们之后引用。我们可以使用EXECUTE语句执行这个准备好的statement，也可以使用DEALLOCATE PREPARE来释放掉它。

转载于:https://my.oschina.net/hengbao666/blog/1574659