A little-known technique in MySQL database

MySQL is easy to learn and easy to use, with a wealth of technical documentation, these two factors make it widely used. However, with the rapid development of MySQL, even a MySQL veteran will sometimes marvel 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 the 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, and here's an example:

Table A

shell> MySQL--xml

Mysql> SELECT * from Test.stories;

1

This is a test

2005-07-28 00:14:57

2

This is the second Test

2005-07-28 00:15:11

2 rows 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 workaround is to use the Repair Table command, which is the following 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 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, the CSV format can be easily imported into most programs by telling MySQL to create an output file in CSV format. 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.

Reduce the appearance of "bad" data by activating strict mode

MySQL servers can run in many different modes, and each is optimized for a specific purpose. By default, no mode is set. However, by adding the following options on the server command line, you can easily change the settings of the schema and run MySQL in "strict" mode:

shell> mysqld--sql_mode= "Strict_all_tables" &

In "strict" mode, the server's automatic correction function is invalidated by executing and returning an error through MySQL's abort query. Similarly, more stringent time checks will be performed in this mode.