MySQL 10 special Tips

MySQL is easy to learn and easy to use, with a wealth of technical documentation, these two factors make it widely used. However, as MySQL grows faster, even a MySQL veteran will sometimes sigh at the unexpected features of the software. This article will introduce you to these unknown features.

　　View query results in XML format

By invoking the MySQL command-line client using the traditional-xml option, you can view it in XML format instead of the traditional list form.

　　MySQL Query results

This technique is useful if you plan to integrate the query output with other programs, and here's an example:

Table A

shell> MySQL--xml

Mysql> SELECT * from Test.stories;

1

This is a test

2

This is the second Test

2rows in Set (0.11 sec)

　　Quick Rebuild Index

Typically, if you want to change the Full-text search variable for the server, you need to re-establish the 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 fix.

The method is to use the Repair Table command, which is the following demo procedure:

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 a certain table type

If you're dealing with a read-only MyISAM form, MySQL allows you to compress it to save disk space. This can be used including Myisampack, as follows:

Table C

Shell> myisampackmovies. Myi

Compressing movies. MyD: (146 Records)

-Calculating statistics

-Compressing file

41.05%

　　Using traditional SQL

MySQL supports the traditional use of SQL queries and supports 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 = ' Joe ';

+----------+

| usertype |

+----------+

| admin|

+----------+

1 row in Set (0.00 sec)

　　Output tabular data in CSV format

The MySQL output file contains a list of all the SQL commands. This is useful if you want to import output files into MySQL, but this approach will not work if the target program (such as Excel) cannot communicate with SQL. In this case, you can create an output file in CSV format by telling MySQL that the CSV format is easy to import into most programs. This demonstrates the operation of the mysqldump:

Shell> mysqldump-t.

--fields-terminated-by= "," mydbmytable

This generates a text file in the current directory containing a comma-separated record from the mydb.mytable list.