好吧,我自己解决了这里是存储过程:)
BEGIN
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE truncatestmnt TEXT; -- this is where the truncate statement will be retrieved from cursor
-- This is the magic query that will bring all the table names from the database
DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName";
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
OPEN c1;
c1_loop: LOOP
FETCH c1 INTO truncatestmnt;
IF `done` THEN LEAVE c1_loop; END IF;
SET @x = truncatestmnt;
PREPARE stm1 FROM @x;
EXECUTE stm1;
END LOOP c1_loop;
CLOSE c1;
END我正在调用给定数据库中的所有表,如果给定数据库中的表没有要遵循的模式,这将有所帮助。
因此,通过调用DECLARE c1 CURSOR FOR SELECT Concat('TRUNCATE TABLE ', TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = "@DatabaseName";并将结果保存到游标中,我可以获取由给定数据库中“n”个数量的表生成的所有TRUNCATE TABLE x语句,然后通过准备和执行游标中的每个语句,它将截断所有表中的内容。给定数据库。
BTW @DatabaseName must be given as parameter to the stored procedure
希望这也可以帮助别人:)
亚历克斯