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.