(Daily question) Solution ideas for batch execution of mysql database

I. Overview

Application scenario: Many mysql files need to be executed in batches. It is too time-consuming to execute the sql files of the mysql database one by one.

Debugging environment: local windows10, the database to be executed is on the centos server in the LAN.

Mysql needs to be installed locally. My version is mysql5.7.

MYSQL download:

After downloading and installing the Linux environment variables, after adding the path to the environment variables, the mysql command used previously can be used.

1. Check whether the windows command line supports the mysql command

Use the command line to log in to mysql in cmd. If it prompts "not an internal or external command", you need to configure it

image.png

You need to add the bin directory under the mysql installation directory to the system variables. Please find out how to operate Baidu.

After the configuration is completed, reopen cmd and use the mysql command line directly.

2. Solution ideas 3. Specific implementation

1. Traverse the directory and generate a new batch execution sql file batch.sql

Create a new sqlscripts folder in the root directory, and load all scripts to be executed into the sqlscripts folder. The batch command will traverse and add to the new sql in sequence.

2. Create new createsql.bat

Create a new txt file in the root directory and rename it to:creatsql.bat, and then copy the following content into it. This batch process will be traversed and added to batch.sql in sequence.

@echo off & setlocal EnableDelayedExpansion
del batch.sql
echo 开始执行...
@set source=.
for %%i in (sqlscripts*.sql) do (
　　set file=%%~fi
　　set file=!file:/=/!
　　echo %source% !file!
　　echo %source% !file! >> batch.sql
)
echo 执行完毕!
pause

Note: I encountered encoding problems during the test, so all batch files were edited with notepad and the encoding format was changed to ANSI instead of utf-8;

3. Create new update.bat

Create a new txt file in the root directory and rename it to: update.bat, and then copy the following content into it. This batch process will connect to the database and execute the newly generated batch.sql file.

::echo off
@echo off
:: 指定MySQL安装路径
:: C:
:: cd C:Program FilesMySQLMySQL Server 5.7bin
:: 数据库主机
@set host=192.10.120.101
:: 数据库名称
@set db=test
:: 用户名
@set userName=root
:: 密码
@set password=123456
:: 获取文件夹所在绝对路径
@set basePath=%~dp0
:: 要执行的sql脚本
@set sqlpath=%basePath%batch.sql
echo 开始执行数据库脚本...
:: 连接MySQL数据库并执行sql脚本 -f 脚本执行过程中，出现错误继续执行 --default-character-set指定导入数据的编码（与数据库编码相同）
mysql -f -h %host% -u %userName% --password=%password% %db% < %sqlpath% --default-character-set=gbk
:: 执行完成后，不立刻关闭dos窗口
echo 所有脚本执行完毕。
del batch.sql
pause

Note: You need to change the variable values ​​in this batch linux batch execution script, such as host, database name/password and other information.

4. Execute batch processing

Execute createsql.bat, and check whether batch.sql correctly executes update.bat, and checks whether it is executed normally. IV. Specific implementation plan 2

刚才介绍的思路是分为两步，先遍历并整合成一个sql脚本去执行。似乎还有一种方法就是逐字遍历的时侯linux 批量执行脚本，每取出一条就去执行一条。这样写一个批处理就可以搞定了。

具体实现：

新建update_each.bat，内容如下

::echo off
@echo off
:: 指定MySQL安装路径
:: C:
:: cd C:Program FilesMySQLMySQL Server 5.7bin
:: 数据库主机
@set host=192.10.120.101
:: 数据库名称
@set db=test
:: 用户名
@set userName=root
:: 密码
@set password=123456
:: 获取文件夹所在绝对路径
@set basePath=%~dp0
:: 要执行的sql脚本
@set sqlpath=%basePath%all.sql
echo 开始执行数据库脚本...
for %%i in (sqlscripts*.sql) do (
echo 正在执行 %%i 请稍后...
echo set names utf8;>all.sql
echo source %%i>>all.sql
:: 连接MySQL数据库并执行sql脚本 -f 脚本执行过程中，出现错误继续执行 --default-character-set指定导入数据的编码（与数据库编码相同）
mysql -f -h %host% -u %userName% --password=%password% %db% < %sqlpath% --default-character-set=gbk
echo %%i 执行完毕。
)
del all.sql
echo 所有脚本执行完毕。
pause

非常提示编码问题suse linux 下载，批处理命令是ANSI编码格式；sql脚本是UTF-8编码格式。

结束

The above is the detailed content of (Daily question) Solution ideas for batch execution of mysql database. For more information, please follow other related articles on the PHP Chinese website!