Using MySQL in Batch Mode

MySQL can be run in batch mode. To perform this, the statements that need to be executed should be put in a file and then ‘mysql’ should be indicated to read the input from this file. It can be done as shown below −

shell> mysql < batch−file

If mysql is running on Windows, and there are certain special characters on the file that could potentially create problems, the below line of code can be run −

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

If the connection parameters need to be specified on the command line, the below line of code needs to be executed −

shell> mysql −h host −u user −p < batch−file
Enter password: ********

When mysql is run in this way, a script file is created, and it is further executed. It is important to understand why the script needs to be used −

If a query has to be run frequently, creating a script would avoid having to retype the code every time it needs to be executed.
Note: If the script has to be continued even if certain statements are producing errors, the ‘-- force’ command line option has to be used.
New queries can be generated from existing queries which are similar to one another by simply copying and editing the script files.
The batch mode can be quite useful while developing a query, and this works even well if it is a multi-line statement or a multi-statement sequence. If a mistake occurs, all of the code doesn’t have to be retyped. The script can be simply edited to rectify the error, and ‘mysql’ can be commanded to execute the script file again.

If a query produces a large output, the output can be run through a pager than allowing it to scroll off the top of the screen. This can be done using the below command −