Running SQL Statements Stored in Files

Often groups of SQL statements are stored in a file for reuse. You can run these commands from your operating system's command line easily. Doing this is called running MySQL in batch mode (as opposed to interactively when you connect to the server and type in the commands yourself ).

Create a text file test.sql containing the following two lines:

 INSERT INTO customer(id,first_name,surname) VALUES(5,'Francois','Papo'); INSERT INTO customer(id,first_name,surname) VALUES(6,'Neil','Beneke'); 

You can run these two statements from within your operating system's command line as follows:

% mysql firstdb < test.sql 

Remember to add a hostname, username, and password if required. (This example shows the shortened version for ease of reading.)

If you connect to the MySQL server now, you'll see that these two records have been added:

mysql> SELECT * FROM customer; +------+------------+-------------+ | id   | first_name | surname     | +------+------------+-------------+ |    1 | Yvonne     | Clegg       | |    2 | Johnny     | Chaka-Chaka | |    3 | Winston    | Powers      | |    4 | Patricia   | Mankunku    | |    5 | Francois   | Papo        | |    6 | Neil       | Beneke      | +------+------------+-------------+

If any of the lines in the file contains a SQL error, MySQL will immediately stop processing the rest of the file. Change test.sql to the following. You add the DELETE statement at the top so that if you rerun the set of statements a number of times, you won't be stuck with any duplicate records:

 DELETE FROM customer WHERE id>=6; INSERT INTO customer(id,first_name,surname) VALUES(6,'Neil','Beneke'); INSERT INTO customer(id,first_name,surname) VALUES(,'Sandile','Cohen'); INSERT INTO customer(id,first_name,surname) VALUES(7,'Winnie','Dlamini'); 

When you run this from the command line, you'll see MySQL returns an error:

% mysql firstdb < test.sql ERROR 1064 at line 2: You have an error in your SQL syntax near  ''Sandile','Cohen')' at line 1 

If you look at what the customer table contains now, you'll see that the first record has been correctly inserted, but because the second line contains an error (the id field is not specified), MySQL stopped processing at that point:

mysql> SELECT * FROM customer; +------+------------+-------------+ | id   | first_name | surname     | +------+------------+-------------+ |    1 | Yvonne     | Clegg       | |    2 | Johnny     | Chaka-Chaka | |    3 | Winston    | Powers      | |    4 | Patricia   | Mankunku    | |    5 | Francois   | Papo        | |    6 | Neil       | Beneke      | +------+------------+-------------+

You can force MySQL to continue processing even if there are errors with the force option (see Chapter 2, "Data Types and Table Types," for a full list of MySQL options):

% mysql -f firstdb < test.sql ERROR 1064 at line 2: You have an error in your SQL syntax near  ''Sandile','Cohen')' at line 1

Even though the error is still reported, all the valid records have still been inserted, as you can see if you view the table again:

mysql> SELECT * FROM customer; +------+------------+-------------+ | id   | first_name | surname     | +------+------------+-------------+ |    1 | Yvonne     | Clegg       | |    2 | Johnny     | Chaka-Chaka | |    3 | Winston    | Powers      | |    4 | Patricia   | Mankunku    | |    5 | Francois   | Papo        | |    7 | Winnie     | Dlamini     | |    6 | Neil       | Beneke      | +------+------------+-------------+

Redirecting Output to a File

You can also capture the output in another file. For example, instead of running your SELECT statement from the MySQL command line, you can add it to the original file and output the results of the query to a third file. If you change the test.sql file to the following:

 DELETE FROM customer WHERE id>=6; INSERT INTO customer(id,first_name,surname) VALUES(6,'Neil','Beneke'); INSERT INTO customer(id,first_name,surname) VALUES(7,'Winnie','Dlamini'); SELECT * FROM customer; 

then you can output the results to a file, test_output.txt, as follows:

% mysql firstdb < test.sql > test_output.txt 

The file test_output.txt now contains the following:

id      first_name      surname 1       Yvonne          Clegg 2       Johnny          Chaka-Chaka 3       Winston         Powers 4       Patricia        Mankunku 5       Francois        Papo 7       Winnie          Dlamini 6       Neil            Beneke 

Notice that the output is not exactly the same as it would be if you were running in interactive mode. The data is tab delimited, and there are no formatting lines around them. If you did want the interactive format in the output file, you could use the table option, -t, for example:

 % mysql -t firstdb < test.sql > test_output.txt 

Using Files from within the MySQL Command Line

You can also run SQL statements stored in a file from the command line in MySQL, with the SOURCE command:

mysql> SOURCE test.sql Query OK, 2 rows affected (0.00 sec) Query OK, 1 row affected (0.00 sec) Query OK, 1 row affected (0.00 sec) +------+------------+-------------+ | id   | first_name | surname     | +------+------------+-------------+ |    1 | Yvonne     | Clegg       | |    2 | Johnny     | Chaka-Chaka | |    3 | Winston    | Powers      | |    4 | Patricia   | Mankunku    | |    5 | Francois   | Papo        | |    7 | Winnie     | Dlamini     | |    6 | Neil       | Beneke      | +------+------------+-------------+ 7 rows in set (0.00 sec)

You can delete the records added through the text files, as you will not need them later:

mysql> DELETE FROM customer WHERE id > 4; 

Reasons for using batch mode include the following:

• You can reuse SQL statements you need again.

• You can copy and send files to other people.

• It's easy to make changes to a file if there are any errors.

• Sometimes you have to run in batch mode, such as when you want to run certain SQL commands repeatedly at a certain time each day (for example, with Unix's cron).