This page will explain how to use MySQL in batch mode. This means executing SQL commands that are contained in a text file, a script. We also describe how to load data from a comma-separated file using the LOAD command. The examples here are all standard MySQL, so they should run at the mysql prompt on any platform, or in the MySQL Query Browser you can install. At the end of this page I've appended a text version of the relevant sections from the MySQL Reference Manual. I have not included a lengthy discussion of the LOAD command. Instead, I'll just give examples of its use in simple situations. Scripts are used a lot and are handy for repetitive tasks, debugging (just edit the script). LOAD is only used in loading a table with data from an external file, typically only when setting up the database. In fact, LOAD in fact, could be called from within a script. The examples below are all based on the CAP database, the primary example used in the O'Neils' textbook. All the examples are called "queries", that is, they are all SQL commands sent at the mysql prompt (or from the MySQL Query Browser).
Using a script to create and load a set of tables: I will assume that the database already exists - on the CCIS CGI Server, only a single database exist. Its name is your login name and it is already created for you. In the same way, I'll work with a database named futrelle, my CCIS login name. The examples below were done on my MacBook. Note that comments in SQL scripts use the C convention: /* .... */. Assume that the following script is in a file futrelle1.sql. Note that the CSV files (comma-separated values) are grabbed from the ONeils' book site.
/* Script for mysql by RPF 9/2/2007 creates customers table for CAP database in 'futrelle' database and loads csv data into it. To use this, execute source futrelle1.sql; */ /* if you run a script multiple times to debug syntax, it's useful to include 'if exists' and 'if not exists' where necessary, else you may get an error. */ use futrelle; drop table if exists customers; create table if not exists customers (cid char(4) not null, cname char(13), city char(20), discnt real, primary key (cid)); show fields from customers; load data infile '/Users/robertfutrelle/mysqldemo/custs.csv' into table customers fields terminated by ','; select * from customers; /* shows table contents */
Furthermore, assume that the customers.csv file contains the following:
c001,Tiptop,Duluth,10.00 c002,Basics,Dallas,12.00 c003,Allied,Dallas,8.00 c004,ACME,Duluth,8.00 c006,ACME,Kyoto,0.00
To run the script from the mysql prompt, execute:
source futrelle1.sql;
The output you'll see from executing the script should be:
Database changed Query OK, 0 rows affected (0.09 sec) Query OK, 0 rows affected (0.00 sec) +--------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+----------+------+-----+---------+-------+ | cid | char(4) | NO | PRI | | | | cname | char(13) | YES | | NULL | | | city | char(20) | YES | | NULL | | | discnt | double | YES | | NULL | | +--------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) Query OK, 5 rows affected (0.00 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 +------+--------+--------+--------+ | cid | cname | city | discnt | +------+--------+--------+--------+ | c001 | Tiptop | Duluth | 10 | | c002 | Basics | Dallas | 12 | | c003 | Allied | Dallas | 8 | | c004 | ACME | Duluth | 8 | | c006 | ACME | Kyoto | 0 | +------+--------+--------+--------+ 5 rows in set (0.00 sec)
To create the entire set of tables for the CAP database, add additional table definitions for them and load the data into them. This will be a homework exercise.
Below are examples from the textbook on how SQL can operate with/on a database. There may be slight differences in the syntax between the two systems. The commands below all worked
in mysql, version:
Ver 14.12 Distrib 5.0.45, for apple-darwin8.5.1 (i686)
The output of the commands is not shown.
3.5. Using mysql in Batch Mode In the previous sections, you used mysql interactively to enter queries and view the results. You can also run mysql in batch mode. To do this, put the commands you want to run in a file, then tell mysql to read its input from the file: shell> mysql < batch-file If you are running mysql under Windows and have some special characters in the file that cause problems, you can do this: C:\> mysql -e "source batch-file" If you need to specify connection parameters on the command line, the command might look like this: shell> mysql -h host -u user -p < batch-file Enter password: ******** When you use mysql this way, you are creating a script file, then executing the script. If you want the script to continue even if some of the statements in it produce errors, you should use the --force command-line op- tion. Why use a script? Here are a few reasons: • If you run a query repeatedly (say, every day or every week), making it a script allows you to avoid retyping it each time you ex- ecute it. • You can generate new queries from existing ones that are similar by copying and editing script files. • Batch mode can also be useful while you're developing a query, particularly for multiple-line commands or multiple-statement se- quences of commands. If you make a mistake, you don't have to retype everything. Just edit your script to correct the error, then tell mysql to execute it again. • If you have a query that produces a lot of output, you can run the output through a pager rather than watching it scroll off the top of your screen: shell> mysql < batch-file | more • You can catch the output in a file for further processing: shell> mysql < batch-file > mysql.out • You can distribute your script to other people so that they can also run the commands. • Some situations do not allow for interactive use, for example, when you run a query from a cron job. In this case, you must use batch mode. The default output format is different (more concise) when you run mysql in batch mode than when you use it interactively. For ex- ample, the output of SELECT DISTINCT species FROM pet looks like this when mysql is run interactively: +---------+ | species | +---------+ | bird | | cat | | dog | | hamster | | snake | +---------+ In batch mode, the output looks like this instead: species bird cat dog hamster snake If you want to get the interactive output format in batch mode, use mysql -t. To echo to the output the commands that are executed, use mysql -vvv. You can also use scripts from the mysql prompt by using the source command or \. command: mysql> source filename; mysql> \. filename See Section 7.8.4, “Executing SQL Statements from a Text File”, for more information.
Here is the Section 7.8.4 they mentioned - seems redundant:
7.8.4. Executing SQL Statements from a Text File The mysql client typically is used interactively, like this: shell> mysql db_name However, it is also possible to put your SQL statements in a file and then tell mysql to read its input from that file. To do so, create a text file text_file that contains the statements you wish to execute. Then invoke mysql as shown here: shell> mysql db_name < text_file If you place a USE db_name statement as the first statement in the file, it is unnecessary to specify the database name on the com- mand line: shell> mysql < text_file If you are already running mysql, you can execute an SQL script file using the source command or \. command: mysql> source file_name mysql> \. file_name Sometimes you may want your script to display progress information to the user. For this you can insert statements like this: SELECT '' AS ' '; The statement shown outputs .
Return to CSU430 Fall 2007 homepage. or RPF's Teaching Gateway or homepage