我用你说的实现了。 但是三楼说的方法,我改了下,在我自己的电脑上运行时,提示我,此时不应有%%J,代码被我改了下,是这样的。 FOR /f %%J IN ('mysql -u admin -p admin -N -e "use test;show tables";') do (mysql -u admin -p admin -e "truncate test.%%J")
1 mysqldump -d导出表结构2 删除重建数据库3 导入表结构
FOR /f %%J IN ('mysql -u admin -p admin -N -e "use test;show tables";') do (mysql -u admin -p admind -e "truncate test.%%J")注意在一行中,估计你换行了,测试没有问题
弄一个 sp 的:drop procedure if exists del_all_tb; delimiter $$ create procedure del_all_tb(db char(20)) begin declare done int default 0; declare tb char(100); declare cur cursor for select table_name from infoRmation_schema.tables where table_schema = db; declare continue handler for not found set done = 1; open cur; repeat fetch cur into tb; set @sql := concat("truncate ", tb, ";"); prepare stmt from @sql; execute stmt; deallocate prepare stmt; until done end repeat; close cur; end $$ delimiter ; call del_all_tb("数据库名"); drop procedure if exists del_all_tb;
1、用SP删除;
2、取得所有表名,在EXCEL下生成TRUNCATE 表名;
3、用脚本,
windows:
FOR /f %%J IN ('d:\MYSQL55\BIN\mysql -uroot -p123 -N -e "use aa1;show tables";') do (d:\MYSQL55\BIN\mysql -uroot -p123 -e "truncate aa1.%%J")
我用你说的实现了。
但是三楼说的方法,我改了下,在我自己的电脑上运行时,提示我,此时不应有%%J,代码被我改了下,是这样的。
FOR /f %%J IN ('mysql -u admin -p admin -N -e "use test;show tables";') do (mysql -u admin -p admin -e "truncate test.%%J")
delimiter $$
create procedure del_all_tb(db char(20))
begin
declare done int default 0;
declare tb char(100);
declare cur cursor for select table_name from infoRmation_schema.tables where table_schema = db;
declare continue handler for not found set done = 1;
open cur; repeat
fetch cur into tb;
set @sql := concat("truncate ", tb, ";");
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
until done end repeat;
close cur;
end $$
delimiter ;
call del_all_tb("数据库名");
drop procedure if exists del_all_tb;