4.5.1.5. 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 command 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 '<info_to_display>' AS ' ';
The statement shown outputs
<info_to_display>.
You can also invoke mysql with the
--verbose option, which causes
each statement to be displayed before the result that it
produces.
For windows users, use forward slashes for the path delimiters. You also don't need to enclose the path to the file in quotes. E.g., the following works:
mysql> source C:/Documents and Settings/My name here/My Documents/spike_loadingMySQLDB/createTables.sql;
If you are attempting to use a batch file that is UTF8-encoded (which will handle all your accented latin characters as well as chinese, japanese, etc.), make sure that you start 'mysql' with the '--default-character-set=utf8' option or you will end up with whatever the server default is. If the server default is not utf8, your batch file will most likely produce undesireable results.
Posted by theo theunissen on October 21 2009 4:20pm
We use subversion for both code and MySql database changes (script and data).
1. We have created a file /path/to/script/database.sql that contains the database changes. This file is committed 2. We have a bash script to update both code and executes MySQL changes
The script looks like: #/bin/bash svn up <src> <target> mysql -u <user> -p<password> -h <hostname> <database> < /path/to/updated_script/database.sql -- If the file database.sql is empty, then nothing is changed.
Enjoy, Theo Theunissen
Posted by Vimal Srinivasan on December 14 2010 10:47am
I had the following unexpected problem which may be of use to other Windows users.
While running the SOURCE command if you get an error such as "ERROR: unknown command '\j'. ", then repeat the same COMMAND but omit the colon at the end.
Posted by Sergio Terroso Cabrera on July 7 2011 6:18pm
Sometimes you want to run a large query, like I did to do a union select to ~30 tables and then order and limit that result and get the result to a file.
In this case you can put your query to a file called query.sql and run: mysql -B -uUSER -p DATABASE < query.sql > result.txt
Just remember, if the query takes a long time to run, it takes a long time for the file to get any data in it.
© 2012, Oracle Corporation and/or its affiliates
User Comments
To run two sql scripts at a time you can use cat command available in Linux.
cat file1.sql file2.sql | mysql -u USERNAME -p
For windows users, use forward slashes for the path delimiters. You also don't need to enclose the path to the file in quotes. E.g., the following works:
mysql> source C:/Documents and Settings/My name here/My Documents/spike_loadingMySQLDB/createTables.sql;
If you are attempting to use a batch file that is UTF8-encoded (which will handle all your accented latin characters as well as chinese, japanese, etc.), make sure that you start 'mysql' with the '--default-character-set=utf8' option or you will end up with whatever the server default is. If the server default is not utf8, your batch file will most likely produce undesireable results.
We use subversion for both code and MySql database changes (script and data).
1. We have created a file /path/to/script/database.sql that contains the database changes. This file is committed
2. We have a bash script to update both code and executes MySQL changes
The script looks like:
#/bin/bash
svn up <src> <target>
mysql -u <user> -p<password> -h <hostname> <database> < /path/to/updated_script/database.sql
--
If the file database.sql is empty, then nothing is changed.
Enjoy, Theo Theunissen
if you want to run a set of all .sql files from a particular folder, you may wish to navigate to the folder and use the following command.
cat *.* | mysql -u USERNAME -p
something similar to Sanjay's tip. :)
I had the following unexpected problem which may be of use to other Windows users.
While running the SOURCE command if you get an error such as "ERROR: unknown command '\j'. ", then repeat the same COMMAND but omit the colon at the end.
It is good to specify which scheme will they be affecting:
$ mysql -D <database name> -u <username> -p < script.sql
Sometimes you want to run a large query, like I did to do a union select to ~30 tables and then order and limit that result and get the result to a file.
In this case you can put your query to a file called query.sql and run:
mysql -B -uUSER -p DATABASE < query.sql > result.txt
Just remember, if the query takes a long time to run, it takes a long time for the file to get any data in it.
Add your own comment.