Additional languages
MySQL 3.23, 4.0, 4.1 Reference Manual :: 8 Client and Utility Programs :: 8.7 mysql — The MySQL Command-Line Tool
« 8.6 myisampack — Generate Compressed, Read-Only MyISAM Tables
8.7.1 mysql Options »
Section Navigation      [Toggle]

8.7. mysql — The MySQL Command-Line Tool

8.7.1. mysql Options
8.7.2. mysql Commands
8.7.3. mysql Server-Side Help
8.7.4. Executing SQL Statements from a Text File
8.7.5. mysql Tips

mysql is a simple SQL shell (with GNU readline capabilities). It supports interactive and non-interactive use. When used interactively, query results are presented in an ASCII-table format. When used non-interactively (for example, as a filter), the result is presented in tab-separated format. The output format can be changed using command options.

If you have problems due to insufficient memory for large result sets, use the --quick option. This forces mysql to retrieve results from the server a row at a time rather than retrieving the entire result set and buffering it in memory before displaying it. This is done by returning the result set using the mysql_use_result() C API function in the client/server library rather than mysql_store_result().

Using mysql is very easy. Invoke it from the prompt of your command interpreter as follows: 

shell> mysql db_name

Or: 

shell> mysql --user=user_name --password=your_password db_name

Then type an SQL statement, end it with ‘;’, \g, or \G and press Enter.

You can execute SQL statements in a script file (batch file) like this: 

shell> mysql db_name < script.sql > output.tab

User Comments

Posted by [name withheld] on March 16 2004 7:47am[Delete] [Edit]

If you specify '--unbuffered' as one of the options then this problem goes away. its not quite the same as the mysql command line. for example:
'show tables;' for an empty database will print 'Empty Set' for the command line mysql, and nothing for interactive mode.

I also use '--force' which stops mysql from exiting when I make typing mistakes.

(M-x customize-group SQL)

Posted by [name withheld] on April 2 2004 7:47am[Delete] [Edit]

Regarding Mysql 4.0.15 and maybe other versions:

It seems that using the -X command line option for exporting the data in XML format produces invalid XML.

Mysql only encloses the query results in XML element tags, but doesn't do XML-encoding of the contents inside the tags.

In XML, if you want to use one of the characters <, >, &, ', or " inside an element tag is not valid. If you want to use one of those characters, you have to use the respective entity instead.

Mysql doesn't seem to do that, so when selecting tagged data or markup like "<foo>red & green" with the -X command line option will always lead to invalid XML.

An uncool workaround would be to perform some string replacements for every selected column when using the -X option:
replace all & by &
replace all < by &lt;
replace all > by &gt;
replace all " by &quot;
replace all ' by &apos;

Other stuff, like language specific characters (umlauts etc.) has to be encoded as well or has to be handled by defining or applying a different character set when post processing the XML output.

Posted by jon cline on June 17 2004 6:28pm[Delete] [Edit]

regarding the above comment, it seems mysql assumes you have already escaped chars inserted into the db. for example, we use XML extensively and therefore insert all invalid chars with their entity replacement:

> = &gt;
< = &lt;
" = &quot;
' = &apos;
& = &
á = &aacute;
...

some must be replaced again within your XSL or other display tranformation as IE does not understand &apos; for example.

one question I do have is whether or not anyone has gotten this CLI ability into a JDBC interface? I would love to retrieve all my results as XML through JDBC rather than convert them through query loops once received.

thx.

Add your own comment.