- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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 −
shell> mysql < batch-file | more
The output can be stored in a location and can be further processed −
shell> mysql < batch-file > mysql.out
The script can also be shared/distributed with others so that they can execute the statements.
- Related Articles
- How can we run MySQL statements in batch mode?
- How to get the MySQL interactive output format in batch mode also?
- What would be the difference between default output format when running MySQL in batch mode or interactively?
- While running MySQL statements in batch mode, how can we print, along with output, which statements are getting executed?
- How to batch update MySQL table?
- How to do a batch insert in MySQL?
- Batch Inserts Using JDBC Statements
- Batch Inserts Using JDBC Prepared Statements
- How to install MSI file using batch file in PowerShell?
- MySQL query to select rows one batch at a time
- Batch statement in Cassandra
- Using fast mode in Sybase bcp
- How to multiple insert or batch insert at a time in MySQL query?
- How do I disable Strict Mode in MySQL?
- Perform MySQL delete under safe mode?