MySQL command line formatted output

MySQL's client command line tool has many user-friendly features, and in some aspects it can even be said to be more user-friendly than Oracle's sqlplus. Of course, overall, sqlplus is more convenient. Maybe it's because I am more familiar with sqlplus. Here are some commonly used features of the MySQL command line.

1. Use G to display the results vertically by row

If a row is very long and this row needs to be displayed, the result will look very uncomfortable. Use G instead of a semicolon after a SQL statement or command to output the value of each row vertically. This may also be the feature that everyone is most familiar with that distinguishes MySQL 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

2. Use pager to set the display method

If the selected result set exceeds several screens, the previous results will pass by in a flash and cannot be seen. Using pager, you can set up calling more or less in os to display query results, which is the same as using more or less in os to view large files.

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’

to restore to stdout

mysql> nopager
PAGER set to stdout

3. Use tee to save the running results to a file

This is similar to the spool function of sqlplus, which can save the results in the command line to an external file. If an already existing file is specified, the results are appended to the file.

mysql> tee output.txt
Logging to file ‘output.txt’

or

mysql> T output.txt
Logging to file ‘output.txt’
mysql> notee
Outfile disabled.

or

mysql> t
Outfile disabled

4. Execute OS commands

mysql> system uname
Linux
mysql> ! uname
Linux

5. Execute SQL files

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 other functions. You can get some commands supported by the MySQL command line through help or ? .
Continue with the above topic and introduce some tips on the mysql command line
1. Output the results in html format
Use the parameter –html or -T of the mysql client, then all SQL query results will be automatically generated as html table codes

$ mysql -uroot –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;
2 rows in set (0.00 sec)

2. Output the results in xml format
Similar to the above, use the –xml or -X option to output the results in xml format

$ mysql -uroot –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;
2 rows in set (0.00 sec)

3. Modify the command prompt
Use the –prompt= option of mysql, or enter mysql You can modify the prompt by using the prompt command in the command line environment

mysql> prompt u@d> PROMPT set to ‘u@d>’ root@(none)>use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed root@mysql>

where u represents the currently connected user and d represents the currently connected database. For more options, please refer to man mysql

Here we will introduce the configuration file to set these parameters on the MySQL command line.

Some operating parameters of the MySQL command line can be set through the [mysql] section of the /etc/my.cnf configuration file. For example:

[mysql] prompt=\u@\d \r:\m:\s> pager=’less -S’ tee=’/tmp/mysql.log’

Display the user name, current database and current time through prompt settings. Note that it is best to use double slashes in the configuration file:

root@poster 10:26:35>

Use less to display query results through pager settings. -S means truncation exceeds the screen width. If a line is too long, the display format of MySQL will appear messy. If you want to see the complete line, it is recommended to use G to output the line vertically. Of course, you can also add more parameters to control the output.

tee saves all the output of MySQL execution to a log file. Even if less -S is used to truncate the super long line, the entire result will still be recorded in the log. In addition, the current time display is set through prompt, so It is also convenient to view the time of each operation in the log file. Since the results of tee are appended to the file, the log file needs to be cleared regularly.