MySQL存储过程：表名为变量

最新推荐文章于 2024-07-22 03:15:38 发布

转载最新推荐文章于 2024-07-22 03:15:38 发布 · 688 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/hengbao666/blog/1574659

文章标签：

#数据库 #python

本文介绍了一种在存储过程中动态使用表名的方法，通过传入表名参数并结合使用 PREPARE 语句实现多表更新操作。此方法适用于需要针对不同表执行相同 SQL 操作的场景。

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.

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