在存储过程中打开游标 DECLARE cur1 CURSOR FOR SELECT TABLE_NAME FROM information_schema.`TABLES` where `TABLE_SCHEMA`='mydatabase';然后循环中 PREPARE stmt1 FROM 'TRUNCATE tablename'; EXECUTE stmt1 ; DEALLOCATE PREPARE stmt1;
先把数据备份出来 删除*.myd文件 还原数据 试试看
接口: DROP PROCEDURE IF EXISTS sp_truncate_table; CREATE PROCEDURE `sp_truncate_table`(in i_dbname varchar(50)) begin declare v_i int default 0; declare v_tbname varchar(100); declare cur_test cursor for select table_name from information_schema.tables where table_schema=i_dbname; declare continue handler for not found set v_i=1; open cur_test; fetch cur_test into v_tbname; while v_i=0 do set @str=concat('truncate table ',v_tbname); prepare stmt1 from @str; execute stmt1; deallocate prepare stmt1; fetch cur_test into v_tbname; end while; close cur_test; end; /*----------------*/调用: call sp_truncate_table('库名');
试试看
DROP PROCEDURE IF EXISTS sp_truncate_table;
CREATE PROCEDURE `sp_truncate_table`(in i_dbname varchar(50))
begin
declare v_i int default 0;
declare v_tbname varchar(100);
declare cur_test cursor for select table_name from information_schema.tables where table_schema=i_dbname;
declare continue handler for not found set v_i=1;
open cur_test;
fetch cur_test into v_tbname;
while v_i=0 do
set @str=concat('truncate table ',v_tbname);
prepare stmt1 from @str;
execute stmt1;
deallocate prepare stmt1;
fetch cur_test into v_tbname;
end while;
close cur_test;
end;
/*----------------*/调用:
call sp_truncate_table('库名');
1、如果没有建立库表结构的脚本,则使用mysqldump导出表结构。
2、删除数据库。
3、执行建立库表结构的脚本,建立空的数据库和表。