MySQL存储过程不以表名作为参数 [英] Mysql stored procedure don't take table name as parameter
问题描述
我已经写了一个存储过程.除了将表名作为输入参数外,它都工作正常.
I've written a stored procedure. It's working fine except taking the table name as input parameter.
让我们在MySQL中查看我的进程:
Let see my proc in 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变量作为表输入参数,则会显示错误.
Here the service name parameter is working fine. But if I include the newsInfoTable variable as table input parameter then a error shows.
表'db_test.newsinfotable'不存在
Table 'db_test.newsinfotable' doesn't exist
为什么仅对表参数会发生这种情况?如何从此错误或
Why does this happen only for table parameter? How can I retrieve from this error or
如何将表名作为参数传递给存储过程?
How I pass a table name into a stored procedure as a parameter?
推荐答案
无法使用动态表名优化SP,因此许多数据库(包括MySQL)不允许动态指定表名.
An SP cannot be optimized with a dynamic table name, so many DBs, MySQL included, don't allow table names to be specified dynamically.
解决此问题的一种方法是使用动态SQL.
One way around this is to use Dynamic 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$$
这篇关于MySQL存储过程不以表名作为参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!