- 4.5.1 mysql — The MySQL Command-Line Tool
- 4.5.1.1 mysql Options
- 4.5.1.2 mysql Commands
- 4.5.1.3 mysql Server-Side Help
- 4.5.1.4 Executing SQL Statements from a Text File
- 4.5.1.5 mysql Tips
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
statement as the
first statement in the file, it is unnecessary to specify the
database name on the command line:
db_name
shell> mysql < text_file
If you are already running mysql, you can
execute an SQL script file using the source
command or \. command:
mysql>sourcemysql>file_name\.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.
As of MySQL 5.0.54, mysql ignores Unicode
byte order mark (BOM) characters at the beginning of input
files. Previously, it read them and sent them to the server,
resulting in a syntax error. Presence of a BOM does not cause
mysql to change its default character set. To
do that, invoke mysql with an option such as
--default-character-set=utf8.
For more information about batch mode, see Section 3.5, “Using mysql in Batch Mode”.
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
Under Win32, it is important that mysql ONLY parses ANSI-coded text files correctly (especially for INSERTs with german Umlauts!).
In mysql, results are shown with odd characters, but using MySQL-Front you get the right characters.
It took me an afternoon to find this out :-(
Regarding mysql parsing and ANSI-coded text files, I had the same problem on Linux. Spanish accented characters where shown like '?' and spaces.
I used iconv to solve this problem:
iconv -f iso8859-1 -t UTF8 back2005-01-04.sql | mysql -u <user> -p -D <database>
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.
I'm not paying attention to the type of format that I'm using, but have found that the DELIMITER ; command to return the CLI default delimiter in a script does not function if there is a carrage return on the same line.
If anyone wants to load many scripts you can use the Unix command cat
cat *.sql | mysql -uroot -p
re:Using mysql to execute statements from a text file - Windows XP - Vista:
If you need to specify connection parameters on the command line for using a text file use foward slashes ///. Note: "source" is not a request for a url to your file, it is a parameter so use the word source as seen below.
Example: mysql> source C:/inetpub/wwwroot/your folder/sql.txt; I hope this helps, unfortunately the reference manual does not allude to this it would save a lot of grief.
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
Add your own comment.