Tips for using MySQL command lines

1.
Mysql -- xml (-X) is used to display MySQL query results in XML format;
Mysql -- html (-H) is used to display MySQL query results in HTML format;
This output makes it easy to integrate with other programs.
(For example, the query results obtained in XML format can be conveniently used by other programs.
Without the need to manually construct data in XML format. Similarly, the results displayed in HTML format can be displayed directly on the webpage, which is very convenient)
In actual use:
Use mysql-u username-p -- xml in the command line
If you create a database connection in PHP, you can use this option -- xml

2.
You can use EXPLAIN to view the execution details of SELECT statements before adding a SELECT statement, so that you can easily understand which columns are indexed to Improve the access speed.

3.
Fast index Reconstruction
Generally, if you want to change the server's full-text search variable, you need to re-create a full-text index in the table to ensure that your updates are mapped. This operation will take a lot of time, especially if you need to process a lot of data. A quick solution is to use the repair table command. The following is a demo process:
Mysql> repair table content QUICK;
+ ----------- + -------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ ----------- + -------- + ---------- +
| Content | repair | status | OK |
+ ----------- + -------- + ---------- +
1 row in set (0.05 sec)

4.
Compress certain table types
If you are processing a read-only MyISAM table, MySQL allows you to compress it to save disk space. You can use myisampack as follows:
Shell> myisampackmovies. MYI
Compressing movies. MYD: (146 records)
-Calculating statistics
-Compressing file
41.05%

5.
You can use the mysqldump program to export data from MySQL, which can be database data or data from a data table in the database.
Many output formats are supported.
The table data is output in CSV format and can be opened in Excel or other applications.
The operation process of mysqldump is demonstrated here:
Shell> mysqldump-T. -- fields-terminated-by = "," mydb. mytable
This will generate a text file in the current directory, containing records from mydb. mytable list with comma as the delimiter.

6.
Obtain help from the MySQL Command Line
On a DOS or Linux terminal, the following command is used to obtain MySQL help:
Mysql_command -- help
For mysql help, enter mysql help.
Mysqladmin -- help
Mysqldump -- help
.
Go to mysql>. The method for obtaining help is
Help or \?
Help contents (get the server-side Command help)
(Note that the help method in DOS is help command, for example, help dir)

7.
Monitor MySQL Server
Mysql> \ s or status
--------------
Mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)
Connection id: 71
Current database:
Current user: root @ localhost
SSL: Not in use
Using delimiter :;
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Uptime: 6 hours 42 min 20 sec
Threads: 1 Questions: 744 Slow queries: 1 Opens: 50 Flush tables: 1 Open ta
Bles: 0 Queries per second avg: 0.031
--------------
Mysql> show status
Show more details

8.
The create table code is returned automatically.
MySQL can obtain the code for creating a table and use the command
Mysql> show create table table_name;

9.
The method for outputting data to a file in DOS is to use redirection.
For example
Shell> dir> dir.txt
Parameters are required for MySQL to output data to a file.
Mysql> \? Or? Or help // get help information
The following describes how to mount the MySQL output to an outfile:
Mysql> \ T or tee [outfile_name] // If outfile_name is omitted, it will be mounted to the outfile of the last mount, and the result will be displayed in outfile_name.
Cancel, prevent output results from being written into the outfile file, and use the notee or \ t command
Use mysqldump to export data from databases and data tables in MySQL.

10.
Prompt for prompt change command
By default, the MySQL command line client program displays a mysql> prompt. You can use prompt to modify the prompt to a specific content, such
Mysql> prompt [new prompt]
If not specified, the default mysql
Example
Mysql> prompt hello>
Will display
PROMPT set to 'Hello>'
Hello>
If you enter prompt directly, restore to mysql>