MySQL is easy to learn and easy to use, and comes with a wide range of technical documents, these two factors make it widely used. However, with the rapid development of MySQL, even a veteran of MySQL sometimes lamented the functionality of the software.
MySQL is easy to learn and easy to use, and comes with a wide range of technical documents, these two factors make it widely used. However, with the rapid development of MySQL, even a veteran of MySQL sometimes lamented the functionality of the software.
This article will introduce you to these unknown features.
View query results in XML format
By using the traditional-xml option to call the MySQL command line client program, you can view MySQL query results in XML format (instead of the traditional list form. This technique is useful if you plan to integrate the query output with other programs. Here is an example:
Table
Shell> mysql -- xml
Mysql> SELECT * FROM test. stories;
1
This is a test
00:14:57
2
This is the second test
00:15:11
2 rows in set (0.11 sec)
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:
Table B
Mysql> repair table content QUICK;
+ ----------- + -------- + ---------- +
| Table | Op | Msg_type | Msg_text |
+ ----------- + -------- + ---------- +
| Content | repair | status | OK |
+ ----------- + -------- + ---------- +
1 row in set (0.05 sec)
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:
Table C
Shell> myisampackmovies. MYI
Compressing movies. MYD: (146 records)
-Calculating statistics
-Compressing file
41.05%
Use traditional SQL
MySQL supports traditional SQL queries and IF and CASE structures. The following is a simple example:
Table D
Mysql> select if (priv = 1, 'admin', 'Guest ') As usertype FROM privs WHERE username = 'job ';
+ ---------- +
| Usertype |
+ ---------- +
| Admin |
+ ---------- +
1 row in set (0.00 sec)
Output table data in CSV format
The MySQL output file contains a list of all SQL commands. If you want to import the output file to MySQL, this function is very practical, but this method will not work if the target program (such as Excel) cannot communicate with SQL. In this case, you can tell MySQL to create an output file in CSV format, which is easy to import to most programs. The operation process of mysqldump is demonstrated here:
Shell> mysqldump-T. -- fields-terminated-by = "," mydbmytable
This will generate a text file in the current directory, containing records from mydb. mytable list with comma as the delimiter.
Use the strict mode to reduce the appearance of "bad" Data
The MySQL server can run in a variety of different modes, and each of them is optimized for specific purposes. By default, no mode is set. However, by adding the following options to the server command line, you can easily change the mode settings and run MySQL in "strict" Mode:
Shell> mysqld -- SQL _mode = "STRICT_ALL_TABLES "&
In "strict" mode, the query is aborted through MySQL and an error is returned. Many automatic correction functions of the server are invalid. Similarly, a stricter time check will be executed in this mode.
Monitoring Server
You can run the show status command to obtain a server running and statistical report, including the number of connections opened, the number of activation queries, and the normal running time of the server. For example:
Table E
Mysql> show status;
+ ------------------ + ------- +
| Variable_name | Value |
+ ------------------ + ------- +
| Aborted_clients | 0 |
| Aborted_connects | 0 |
...
| Uptime | 851 |
+ ------------------ + ------- +
156 rows in set (0.16 sec)
The create table code is returned automatically.
MySQL allows you to automatically obtain SQL commands to recreate a specific table. Simply run the show create table command and view the TABLE creation code, as shown below:
Table F
Mysql> show create table products;
-----------------------------------------------------
| Table | Create Table
+ ---------- + -----------------------------------------
| Products | create table 'products '(
'Id' int (8) not null auto_increment,
'Name' varchar (255) not null default '',
'Price' int (10) default NULL,
Primary key ('id ')
) ENGINE = MyISAM default charset = latin1 |
+ ---------- + -----------------------------------------
1 row in set (0.27 sec)
Create a more useful Command Prompt:
By default, the MySQL command line client program displays a simple mysql> prompt. However, you can use specific modifications to change the prompt to make it more effective, including the current user name, host name, and the selected database. As follows:
Table G
Mysql> prompt \ U:/\ d>
PROMPT set to '\ U:/\ d>'
Root @ localhost:/db1>
From this document, you can obtain a complete list that supports MySQL client program changes.
