Introduction to the use of MySQL source command

Thoughts triggered by an online question

When I was at work today, a development colleague brought me a .zip compressed file and said that he wanted to import the data in it into the database. I thought it was a complete SQL and I just needed to copy and paste it and import 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, which was about 30M. After decompressing it, I found that the content inside was a .sql data file with a size of about 645M. It would be too 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 in the command line. So I opened it and looked at the contents, and found that this was a standard sql file exported by Navicat, which was full of various insert statements. At this point, I was relieved, because such a sql file can be directly imported into the database through the MySQL source command. Because the amount of data is large, in order to avoid mistakes, I re-looked at the syntax of the source and briefly explained it here.

MySQL source command

The mysql source command is mainly used to import very large SQL files. In daily work, we often encounter the situation of importing large data files. In MySQL, the mysql source command can be used to 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 into which we want to import data, then set the default character set, and then use the source command, followed by the absolute path to our .sql file. If we have 5 files, we can import them into the database one by one using this method. Remember to select the correct database name.

Consider such a requirement. If we have 10 such files, and if we add them one by one manually, some errors are inevitable. At this time, we can adopt a method, which is to write the source commands we want to write in a file, like this:

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 source this file and execute these commands sequentially. This method seems to have solved our problem, but there is a new problem with this way of operation. If there are 100 such files and their names have a certain pattern, it would be very troublesome for us to write commands one by one. At this time, we can write a script or use the column editing mode of various editors to generate command lines one by one, and 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 front input box, as shown below:

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

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

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

Finally, a reminder that the source command needs to be used in the mysql command line, unlike mysqldump which can be used directly in the command line.

That’s all I’ll write today.

The above is the detailed content 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 on 123WORDPRESS.COM!