2.5. Using Script Files with mysql

2.5. Using Script Files with mysql

When used interactively, mysql reads queries entered at the keyboard. mysql can also accept input from a file. An input file containing SQL statements to be executed is known as a "script file" or a "batch file." A script file should be a plain text file containing statements in the same format that you would use to enter the statements interactively. In particular, each statement must end with a terminator.

One way to process a script file is by executing it with a SOURCE command from within mysql:

 mysql> SOURCE input_file; 

Notice that there are no quotes around the name of the file.

mysql executes the queries in the file and displays any output produced.

The file must be located on the client host where you're running mysql. The filename must either be an absolute pathname listing the full name of the file, or a pathname that's specified relative to the directory in which you invoked mysql. For example, if you started mysql on a Windows machine in the C:\mysql directory and your script file is my_commands.sql in the C:\scripts directory, both of the following SOURCE commands tell mysql to execute the SQL statements in the file:

 mysql> SOURCE C:\scripts\my_commands.sql; mysql> SOURCE ..\scripts\my_commands.sql; 

The other way to execute a script file is by naming it on the mysql command line. Invoke mysql and use the < input redirection operator to specify the file from which to read query input:

 shell> mysql db_name < input_file 

If a statement in a script file fails with an error, mysql ignores the rest of the file. To execute the entire file regardless of whether errors occur, invoke mysql with the --force or -f option.

A script file can contain SOURCE commands to execute other files, but be careful not to create a SOURCE loop. For example, if file1 contains a SOURCE file2 command, file2 should not contain a SOURCE file1 command.