可以创建一个存储过程来删除某数据库所有表中的记录。请看下面实例:
DELIMITER //
CREATE PROCEDURE empty_all_tables (dbName varchar(50))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE tbl varchar(500);
DECLARE cur1 CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = dbName;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN cur1;
REPEAT
FETCH cur1 INTO tbl;
IF NOT done THEN
SET @tbl=CONCAT('delete from ',tbl);
PREPARE stmt FROM @tbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
END //
DELIMITER;
然后执行这个存储过程
CALL empty_all_tables('test1'); -- 删除数据库"test1"里所有表中的记录,前提是数据库里的表没有互相建立表间参照完整性
***请慎重运行这个存储过程,否则您的珍贵数据会被删除掉而难于恢复!!!