Windows batch bat connects to local Mysql to create and execute SQL files

Time:2022-6-10

Instance code


@echo off
set path=C:\Program Files\MySQL\MySQL Server 5.7\bin
cd ./
set CURR_PATH=%cd%
mysql -h localhost -u root --password=123456 < operate.txt 
mysql -h localhost -u root -p stcounter < stcounter.sql --password=123456

@Echo off: turns off echo and does not appear in the doc command box.

Set path: set the system environment variable c:\program files\mysql\mysql server 5.7\bin. This path is the path where you install MySQL services in your Windows local address

cd ./
set CURR_ Path=%cd% these two lines are fixed.

mysql -h localhost -u root –password=123456 < operate. Txt means to create a database in the operation file after the account and password are logged in. The operation file contains the following sentence: create database stcounter;

You only need to replace the account and password and create the operate file

mysql -h localhost -u root  -p stcounter < stcounter.sql –password=123456

Stcounter is the name of the database stcounter SQL: here is the SQL script exported from Navicat. Just replace the same account and password

The following is additional one

:: set STR is to put the MySQL path containing spaces in str
set str="C:\JiaFa\Setup\MySQL Server 5.5\bin\"
::%% the two percent signs refer to the above variables

%str%mysql.exe -h localhost -uroot -pjfkjyfb dajian < f:\zzztest\c_bw_list.sql>>f:\zzztest\dblog.txt 2>&1

:: pause is a pause. You can see the execution process. If you succeed, delete pause
pause

Bat file execution SQL file

Batch file Bat writing

@echo off 
set errorlevel=0 
 
//Modify to MySQL installation path  
set path_home_mysql="E:\tools08\MySql5.0\"  
set path_bin_mysql=%path_home_mysql%bin\ 
 
//Modify the data name used for MySQL (test)  
set database_mysql=test 
 
//Change to MySQL login user name and password (root, Sujian)  
set user_mysql=root 
set password_mysql=sujian 
 
//Modify the to be executed SQL file (testsql.sql)  
%path_bin_mysql%mysql -u%user_mysql% -p%password_mysql% < testsql.sql

testsql. SQL write SQL statement

Example: alter table DDD add column ddddd varchar (10);

To sum up:

1. if the database insert, update and delete statements are executed, the database name should be added, such as:

%path_bin_mysql%mysql -u%user_mysql% -p%password_mysql% % database_mysql %< mytestsql.sql

mytestsql. SQL statement written by SQL:

update user set english_name=’bat test’ where id=’e605f35d-cf28-4ee9-abb8-812fe539c37a’;

2. if the database operation statement is executed, such as:

%path_bin_mysql%mysql -u%user_mysql% -p%password_mysql% < createtestdb.sql

createtestdb. Contents of SQL:

create database test;

3. you can change the MySQL command to the mysqldump command to export the database backup, such as:

%path_bin_mysql%mysqldump -u%user_mysql% -p%password_mysql% % database_mysql % > backuptestdb.sql

This is the end of this article about connecting windows batch bat to local mysql, creating and formulating data and executing SQL files. For more information about connecting bat to local MySQL and executing SQL, please search the previous articles of developpaer or continue to browse the following articles. I hope you will support developpaer in the future!

Recommended Today

(C#) Listening to the clipboard

public partial class Form1 : Form { public Form1() { InitializeComponent(); AddClipboardFormatListener(this.Handle); } protected override void WndProc(ref Message m) { if (m.Msg == WM_CLIPBOARDUPDATE) { Console.WriteLine(&quot;Clipboard content changed&quot;); } base.WndProc(ref m); } //——————— public const int WM_CLIPBOARDUPDATE = 0x031D; [DllImport(“user32.dll”, SetLastError = true)] public static extern bool AddClipboardFormatListener(IntPtr hWnd); [DllImport(“user32.dll”, SetLastError = true)] public static […]