问题描述
DELIMITER $$DROP PROCEDURE IF EXISTS `codebush`.`sp_truncate_all_table` $$CREATE PROCEDURE `sp_truncate_all_table`()BEGINdeclare done INT DEFAULT 0;declare tbl varchar(64);declare cur_tbl cursor for select table_name from information_schema.tables where table_schema = 'codebush' and table_type='base table';declare continue handler for not found set done = 1;open cur_tbl;repeat fetch cur_tbl into tbl; truncate table tbl;until done end repeat;close cur_tbl;END $$DELIMITER ;上面的存储过程我一执行 CALL sp_truncate_all_table() 就报 Table 'codebush.tbl' doesn't exist我是想把codebush数据库中的数据清空,循环执行 truncate table tbl; tbl是定义的变量,通过cursor取得表名,然后truncate那个表。mysql把tbl不当作变量赋值,直接把tbl当作了表名,就报了tbl那个表不存在。大家有什么好的解决方法呢?
解决方案
试试:SET @STMT := CONCAT("truncate table ", tbl, ";"); PREPARE STMT FROM @STMT; EXECUTE STMT;