Some tips on MySQL command line "useful: multi-screen display, formatted output, etc."

1. Output results in HTML format
Using the MySQL client's parameter –html or-T, all SQL query results are automatically made into the table code of the HTML
$ mysql-u Root--html
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 3286
Server Version:5.1.24-rc-log MySQL Community Server (GPL)
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Mysql> select * from Test.test;
<table border=1><tr><th>i</th></tr><tr><td>1</td></tr> <TR><TD>2</TD></TR></TABLE>
2 rows in Set (0.00 sec)
2. Output results in XML format
Similar to the above, using the – an XML or-x option, you can export the results to XML format
$ mysql-u Root--xml
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 3287
Server Version:5.1.24-rc-log MySQL Community Server (GPL)
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Mysql> select * from Test.test;
<?xml version= "1.0"?>
<resultset statement= "select * from Test.test;"
xmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance" >
<row>
<field name= "I" >1</field>
</row>
<row>
<field name= "I" >2</field>
</row>
</resultset>
2 rows in Set (0.00 sec)
3. Modify the command prompt
You can modify the prompt by using the MySQL –prompt= option, or by using the prompt command after entering the MySQL command line environment
mysql> prompt \[email protected]\d>
PROMPT set to ' \[email protected]\d> '
[Email protected] (none) >use MySQL
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a
Database changed
[Email protected]>
Where \u represents the currently connected user, \d represents the currently connected database, and more options can refer to man MySQL
4. Use \g to display results vertically by row
If the line is long and needs to be shown, it is very uncomfortable to look at the results. Use \g instead of semicolons to end the SQL statement or command, and you can output the value of each row vertically. This is probably one of the most familiar features of MySQL that distinguishes it from other database tools.
Mysql> SELECT * from Db_archivelog\g
1. Row ***************************
Id:1
Check_day:2008-06-26
Db_name:tbdb1
arc_size:137
arc_num:166
per_second:1.6
avg_time:8.7

5. Use pager to set the display mode
If the select has more than a few screens out of the result set, the previous result is flash and cannot be seen. Using pager, you can set the more or less display query results that call the OS, and use more or less to view large files in the OS.
Use more
Mysql> Pager More
PAGER set to ' more '
Mysql> \p More
PAGER set to ' more '
Use less
Mysql> Pager Less
PAGER set to ' less '
Mysql> \p Less
PAGER set to ' less '
Revert to stdout
Mysql> Nopager
PAGER set to stdout
6. Save run results to file using Tee
This spool function, similar to Sqlplus, allows you to save the results from the command line to an external file. If you specify a file that already exists, the result is appended to the file.
Mysql> Tee output.txt
Logging to file ' Output.txt '
Or
mysql> \ Output.txt
Logging to file ' Output.txt '
Mysql> notee
Outfile disabled.
Or
mysql> \ t
Outfile disabled.
7. Execute OS Commands
mysql> system uname
Linux
mysql> \! Uname
Linux
8. Execute the SQL file
Mysql> Source Test.sql
+----------------+
| Current_date () |
+----------------+
| 2008-06-28 |
+----------------+
1 row in Set (0.00 sec)
Or
mysql> \. Test.sql
+----------------+
| Current_date () |
+----------------+
| 2008-06-28 |
+----------------+
1 row in Set (0.00 sec)
There are some other features that can be obtained by help or by getting some commands supported by the MySQL command line.
Transferred from: http://hi.baidu.com/lushaojin/blog/item/acacc8fc6de6d2fafc037f89.html

Some tips on MySQL command line "useful: multi-screen display, formatted output, etc."