MySQL Batch processing

In the batch processing mode under Use mySQL in the previous chapters, you interactively use the mysql input query and view results. You can also run MySQL in batch mode . To do this, put the command you want to run in a file, and then tell MySQL to read its input from the file:

Batch-file

If you run MySQLunder Windows, and there are some special characters in the file that can cause problems, you can do this:

batch-file"

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

Host User Batch-file

********

When you do this with MySQL , you create a script file and then execute the script.

You should use the--force command-line option if you want to continue executing the script while the statement is in error .

Why use a script? There are a number of reasons:

• If you need to run the query repeatedly (say, daily or weekly), you can compile it into a script, and you don't have to retype it each time you execute it.
• You can generate a new query from a similar existing query by copying and editing the script file.
• Batch mode is also useful when you are developing queries, especially for multi-line commands or multi-statement command sequences. If you make a mistake, you don't have to re-enter everything, just edit the script to correct the error, and then tell MySQL to execute the script again.
• If you have a query that produces multiple outputs, you can run the output by flipping through a pager instead of staring at it at the top of the screen:

shell> MySQL < batch-file | more

• You can capture the output from the file for further processing:

shell> MySQL < batch-file > mysql.out

• You can distribute your feet to other people so that they can also run commands.
• Some cases are not allowed to be used interactively, for example, when you run a query from a cron task. In this case, you must use 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 the 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:

Species

Bird

Cat

Dog

Hamster

Snake

If you want to get the interactive output format in batch mode, use mysql-t. In order to echo the command to output the execution, use mysql-vvv.

You can also use the source code or the \. command to run the script from the MySQL prompt:

source filename;

\. FileName

e.g in d:\1.txt

Show Databses;

SELECT * from Menagerie.pet;

Then by executing mysql-hlocalhost-uroot-p <d:1.txt

The execution results are as follows:

Or we can directly output the batch file to the specified path as a file store,

C:\users\david>mysql-hlocalhost-uroot-p123 <d:\1.txt>mysql.out

The same can be done for ease of operation,

This can also be done,

MySQL Batch processing