Introduction to MySQL Source command

Time:2021-10-27

Reflections on an online problem

At work today, my development colleagues took a. Zip compressed package file and said that they wanted to pour the data into the database. Originally, they thought it was formed SQL. They just need to copy and paste it and pour it into the database. When I got it, I found that the problem was not as simple as I thought. The first thing I saw was a compressed package, about more than 30 m. after decompression, I found that the content in it was an. SQL data file with a file size of about 645m. It would be very abnormal to paste such a large file manually. The first reaction is whether this is the backup data from mysqldump. If so, you can restore it directly on the command line. So I opened it and looked at the contents. I found that this is a standard Navicat exported SQL file, which is full of various insert statements. Here, I was relieved, because such SQL files can be directly poured into the database through MySQL’s source command. Because of the large amount of data, in order to avoid errors, I looked again at the source syntax, Here is a brief explanation.

MySQL Source command

The MySQL Source command is mainly used to pour large SQL files. In daily work, we often encounter importing large data files. In mysql, the MySQL Source command can be used to import and easily solve this problem. The basic syntax of MySQL source is as follows:


mysql>use dbtest;
mysql>set names utf8;
mysql>source D:/xxx/xxx/back.sql;

First, we select the database that needs to be poured into the data, then set the default character set, and then use the source command, followed by the absolute path where our. SQL is located. If we have 5 files, we can pour them into the database one by one in this way. Remember to select the correct database name.

Considering such a requirement, if we have 10 such files, it is inevitable that some errors will occur if we add them manually one by one. At this time, we can use a method to write these source commands in a file, as follows:


source D:/xxx/xxx/back0.sql;
source D:/xxx/xxx/back1.sql;
source D:/xxx/xxx/back2.sql;
source D:/xxx/xxx/back3.sql;
source D:/xxx/xxx/back4.sql;
source D:/xxx/xxx/back5.sql;
source D:/xxx/xxx/back6.sql;
source D:/xxx/xxx/back7.sql;
source D:/xxx/xxx/back8.sql;
source D:/xxx/xxx/back9.sql;

Then we go to the source file and directly execute these commands in sequence. This method seems to have solved our problem, but there is still a new problem in this operation mode. If there are 100 such files and their names have certain rules, it must be troublesome for us to write commands one by one. At this time, we can write scripts or use the column editing mode of various editors, First generate such command lines one by one, then paste them into the file again, and finally execute the source command.

It should be noted that when the source command is executed, the query OK command will be printed to the previous input box, as follows:


Query ok;
Query ok;
Query ok;
Query ok;
Query ok;
Query ok;
Query ok;
Query ok;

If you don’t want to see such a result, you can redirect the output result of the source command to a file, so as to avoid continuous output on the console.


mysql>use db_test
mysql>source D:/test.sql > output.log

Finally, let me remind you that the source command needs to be used on the MySQL command line, not directly on the command line like mysqldump.

Just write so much today.

The above is the details of the introduction to the use of MySQL Source command. For more information about the use of MySQL Source command, please pay attention to other related articles of developeppaer!