3.5 use mysql in batch mode

3.5 use mysql in batch mode

In the previous chapter, you interactively use mysql to enter the query and view the results. You can also run mysql in batch mode. To do this, place the command you want to run in a file and tell mysql to read its input from the file:

shell> mysql < batch-file

If you are running in Windows and your file contains some special characters that may cause problems, you can do this:

C:\> mysql -e "source batch-file"

If you need to specify the connection parameter on the command line, the command should be:

shell> mysql -h host-u user-p < batch-fileEnter password: ********

In this case, you first create a script file and then execute the script.

If you want to continue executing this script, even if the statements in the script may produce some errors, you need to use the-force command line option.

Why script? There are many reasons:

If you need to run the query repeatedly (for example, every day or every week), you can compile it into a script, so you do not have to re-type it during each execution. You can copy and edit the script file to generate a new query from a similar existing query. When you are writing a query, the batch mode is also very useful, especially for the multi-line command or multi-statement command sequence. If you make a mistake, you don't have to re-enter all the content. You just need to edit the script to correct the error and then tell mysql to execute the script again. If you have a query that generates multiple outputs, you can run the output through a page splitter instead of staring at it and turning it to the top of the screen:

shell> mysql < batch-file| more

You can capture the output in the file for further processing:

shell> mysql < batch-file> mysql.out

You can distribute your scripts to other people, so they can also run these statements. In some cases, interactive queries are not allowed, for example, when you run a query from a cron task. In this case, you must use the batch mode.

When you run mysql in batch mode, the default output format is different (more concise) than when you use it interactively ). For example, when you run select distinct species FROM pet interactively, the output should be:

+---------+| species |+---------+| bird || cat || dog || hamster || snake |+---------+

However, when running in batch mode, the output should be:

speciesbirdcatdoghamstersnake

If you want to obtain the interactive output format, use mysql-t. to echo the command to output the executed command, use mysql-vvv.

You can also use the source command or. Command to use the script from the mysql prompt:

mysql> source filename;mysql> \. filename

For more information, see
Section 4.5.1.5, "Executing SQL Statements from a Text File"