为什么不能在存储过程中使用变量作为表名? [英] Why can't I use a variable as the table name in a stored procedure?
本文介绍了为什么不能在存储过程中使用变量作为表名?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我试图在存储过程中将变量用作表名,并将其用作字符串文字而不是实际的表名.为什么是这样?除了在PHP中执行此操作外,还有其他方法可以做到这一点吗?
I'm trying to use a variable as the table name within a stored procedure and it's using it as a string literal instead of as the actual table name. Why is this? Is there another way I should be doing this (aside from just doing it in PHP)?
DROP PROCEDURE IF EXISTS settonull;
DELIMITER //
CREATE PROCEDURE settonull()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE _tablename VARCHAR(255);
DECLARE _columnname VARCHAR(255);
DECLARE cur1 CURSOR FOR SELECT CONCAT(TABLE_SCHEMA, '.', TABLE_NAME) AS table_name, COLUMN_NAME AS column_name FROM information_schema.COLUMNS WHERE IS_NULLABLE = 'YES' AND TABLE_SCHEMA = 'blip_notify' AND table_name = 'notify_queue' LIMIT 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO _tablename, _columnname;
IF done THEN
LEAVE read_loop;
END IF;
UPDATE _tablename SET _columnname = NULL WHERE LENGTH(TRIM(_columnname)) = 0;
END LOOP;
CLOSE cur1;
END//
DELIMITER ;
CALL settonull();
输出:
0 row(s) affected, 1 warning(s)
Execution Time : 0 sec
Transfer Time : 1.094 sec
Total Time : 1.095 sec
Note Code : 1305
PROCEDURE settonull does not exist
---------------------------------------------------
0 row(s) affected
Execution Time : 0.002 sec
Transfer Time : 1.011 sec
Total Time : 1.014 sec
---------------------------------------------------
Query: call settonull()
Error Code: 1146
Table 'blip_notify._tablename' doesn't exist
Execution Time : 0 sec
Transfer Time : 0 sec
Total Time : 0.003 sec
---------------------------------------------------
推荐答案
您需要使用动态sql.是的,嗯.
You need to use dynamic sql. ya, ugh.
SET @s = CONCAT('UPDATE ', _tablename, ' SET ', _columnname, ' = NULL WHERE LENGTH(TRIM(', _columnname, ')) = 0' );
PREPARE stmt FROM @s;
EXECUTE stmt;
这篇关于为什么不能在存储过程中使用变量作为表名?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文