要在windows下mysql数据库批量执行sql脚本。
1、提前将要执行的sql脚本(例如:1.sql,2.sql,3.sql等)放到E:\mysql-8.0.23-winx64\bin\ignite\
路径下。在mysql安装目录\bin下面,新建batch.sql脚本,内容如下:
\. E:\mysql-8.0.23-winx64\bin\ignite\1.sql
\. E:\mysql-8.0.23-winx64\bin\ignite\2.sql
\. E:\mysql-8.0.23-winx64\bin\ignite\3.sql
【注意】在包含绝对路径的文件名前面加上 \.
2、在mysql安装目录\bin下面,cmd登录mysql,use database;
切换到指定数据库。
3、执行source batch.sql。
【注意】若导入数据中文乱码,通过 show variables like ‘%character%’ 命令查看编码:
mysql> show variables like '%character%';
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | E:\mysql-8.0.23-winx64\share\charsets\ |
+--------------------------+----------------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql>
查看到三个编码格式为gbk的。因此,需要在batch.sql脚本前面加上三行:
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_results=utf8;
\. E:\mysql-8.0.23-winx64\bin\ignite\RUN08140_CMZTFZ.sql
再次执行batch.sql,导入库表的中文不再乱码,显示正常。