MySQL is easy to learn and easy to use: unknown features of MYSQL _ MySQL

Easy to learn and use MySQL: MYSQL is easy to learn and use with a wide range of technical documents. These two factors are 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 calling the MySQL command line client program using the traditional xml option, 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