@echo off
rem 用户名和密码
@set user=root
@set password=root
echo ---------------------------------------
@set temp=show databases;
echo %temp% > temp.sql
rem 把所有的数据库表名输出到文件
mysql -u %user% --password=%password% < temp.sql > db.txt
echo ---------------------------------------
rem 创建或清理一下clean.sql文件
echo ---------- > clean.sql
rem 从文件中循环读取数据库名称
for /f "tokens=1 delims==" %%i in (db.txt) do (
if /i not %%i==Database (
if /i not "%%i"=="information_schema" (
if /i not "%%i"=="mysql" (
if /i not "%%i"=="test" (
rem 拼接查询数据库所有表的名称的sql,并且拼接处 truncate table 的sql
echo SELECT CONCAT("TRUNCATE TABLE ",TABLE_NAME,";"^) FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '%%i' > temp.sql
rem 写入引用数据库的语句到clean.sql
echo use %%i; >> clean.sql
rem 将所有的清理语句打印到clean.sql
mysql -u %user% --password=%password% -N < temp.sql >> clean.sql
)
)
)
)
)
echo ----------------------------
rem 执行清理数据库
mysql -u %user% --password=%password% < clean.sql >> temo.txt
rem 是否清理缓存文件(y/n)
@set cleanTemp=y
if %cleanTemp% EQU y (
del temp.sql
del db.txt
del clean.sql
)
pause&exit