MySQL的程序来删除数据库中的所有数据

Question

我试图写的MySQL程序来删除数据库中的所有数据：

MySQL服务器版本5.5.20

它看起来像：

DELIMITER $$ 
CREATE PROCEDURE drop_data_like(pattern VARCHAR(255), db VARCHAR(255)) 
begin 
    DECLARE truncate_table VARCHAR(255) DEFAULT ''; 
    DECLARE done INT DEFAULT 0; 
    DECLARE cur1 CURSOR FOR SELECT CONCAT(db, '.', info.table_name) 
     FROM information_schema.tables AS info 
     WHERE info.table_schema=db AND info.table_name LIKE pattern; 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 

    OPEN cur1; 

    read_loop: LOOP 
     FETCH cur1 INTO truncate_table; 
     IF done THEN LEAVE read_loop; END IF; 

     PREPARE stmt FROM truncate_table; 

     EXECUTE stmt; 
     DROP PREPARE stmt; 
    END LOOP; 
    CLOSE cur1; 
END$$ 
DELIMITER ; 

CALL drop_data_like('%%', DATABASE()); 

DROP PROCEDURE drop_data_like; 

而作为结果，它显示错误：

ERROR 1064 (42000) at line 16: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@truncate_table; 
    IF done THEN LEAVE read_loop; END IF; 

    PREPARE ' at line 13 

我做了什么错？

Answer 1

我解决了这个问题，并it works now

Answer 2

如果你需要保持结构，但只是摆脱数据，那么只用结构做一个mysqldump（-d选项）会更容易，然后删除数据库并用mysqldump输出重新创建它。

mysqldump -d -h localhost -u user -ppassword databasename > dumpfile.sql 

mysqlimport -u root -ppassword databasename dumpfile.sql