Mysql存储过程不接受表名作为参数。

Question

我编写了一个存储过程。它的工作正常，除了需要将表名作为输入参数。

让我们来看看我在MySQL中的存储过程：

DELIMITER $$
USE `db_test`$$

DROP PROCEDURE IF EXISTS test_proc$$

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `test_proc`(IN serviceName VARCHAR(10),IN newsInfoTable VARCHAR(100))
BEGIN                  
    SELECT COUNT(*) FROM newsInfoTable WHERE newsServiceName=serviceName;           
END$$

DELIMITER ;

存储过程调用参数：

USE db_test;
CALL test_proc('abc','tbl_test_news');

这里的服务名称参数运行正常。但是如果我将newsInfoTable变量作为表输入参数包含在内，就会出现错误。

表'db_test.newsinfotable'不存在

为什么只有表参数会出现这种情况？如何从这个错误中恢复或者

我如何将表名作为参数传递给存储过程？

- riad

Answer 1

由于动态表名的存在，某些数据库（包括MySQL）不能对存储过程进行优化。

解决这个问题的一种方法是使用动态SQL。

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_proc`(IN serviceName VARCHAR(10),IN newsInfoTable VARCHAR(100))
BEGIN                  
    SET @sql = CONCAT('SELECT COUNT(*) FROM ',newsInfoTable,' WHERE newsServiceName=?;'); 
    PREPARE s1 from @sql;
    SET @paramA = serviceName;
    EXECUTE s1 USING @paramA;
END$$

- Jeff Parker

---

+1，但要小心SQL注入。您可能希望将表名与预先批准的表列表进行比较，以防用户操纵表名为 atable where (1=1) union all select username from mysql.user; '-- 或类似的语句。我会创建一个名为“approvedtables”的表，并添加一个测试，例如 select id into approvedid from approvedtables where tablename like newsinfotable limit 1，只有在approved不为空时才执行动态查询。 - Johan

我甚至可以说每个表都应该有一个存储过程...这样不仅可以保护表名免受注入攻击，而且性能也会更好，即使只是微妙的差别。尽管如此，我的任务不是去思考为什么，而是去做并发布，接受一些批评，感到受伤，然后制作一杯冰沙以减轻疼痛，过上幸福的生活，最后在装满芝士蛋糕的游泳池里死去。 - Jeff Parker

你好，根据你上面的例子，我写了一个存储过程。但是它报错了：错误代码: 1327 未声明变量: Third。请看下面的存储过程：DROP PROCEDURE IF EXISTS test_proc$$ CREATE DEFINER=root@localhost PROCEDURE test_proc(IN newsInfoTable VARCHAR(100)) BEGIN
SET @sql_stam = CONCAT('SELECT news INTO ', @news,' FROM ',newsInfoTable,' WHERE ',CURDATE(),'=?;');
PREPARE s1 FROM @sql_stam;
SET @where_param = DATE_FORMAT(date_time,'%Y-%m-%d');
EXECUTE s1 USING @where_param;
SELECT @news;
END$$ DELIMITER ; - riad

亲爱的@Jeff Parker：您能帮我解决这个错误吗？按照您的指示，我编写了一个存储过程，它可以正常执行，但是在调用存储过程时出现错误。因此，我恳请您查看我的新问题，链接为http://stackoverflow.com/questions/6612530/error-when-send-table-as-parameter-in-mysql-store-procedure。 - riad

@riad 看起来有人比我先解决了这个问题...但如果他们的解决方案不起作用，请告诉我 :) - Jeff Parker

Answer 2

您可以使用EXECUTE IMMEDIATE来实现“少即是多”的解决方案（对我而言，代码越少越好）。

CREATE PROCEDURE test_proc(IN serviceName VARCHAR(10), IN newsInfoTable VARCHAR(100))
BEGIN                  
    EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ',newsInfoTable,' WHERE newsServiceName=''', serviceName, ''''); 
END

- Bohemian

Answer 3

虽然可能不是你想要的，但可以考虑使用条件语句和准备语句。

DELIMITER $$
CREATE PROCEDURE select_count(IN table_name VARCHAR(20))
BEGIN
  IF table_name = 'xxx' THEN
     SELECT * FROM xxx;
  ELSEIF table_name = 'yyy' THEN
     ...
  ENDIF
END$$

- ken

Answer 4

查询的那部分不能是动态的。

您可以考虑将其实现为在运行时动态执行的字符串。

- Randy