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.
Monitoring Servers
You can run the show status command to obtain a report of the server running and statistics, including the number of times the connection was opened, the number of times the query was activated, the server uptime, and so on. 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)
automatically return CREATE table code
MySQL allows you to automatically get SQL commands to re-establish a specific table. Simply run the show create TABLE command and view the table to establish the code as follows:
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 (a) 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 displays a simple mysql> prompt. However, you can use specific modifications to change the prompt to make it more efficient, including the current user name, host name, and the currently selected database. As shown below:
Table G
mysql> prompt \u:/\d>
PROMPT set to ' \u:/\d> '
Root@localhost:/db1>
From the documentation file here you can get a complete list of support changes to the MySQL client program.
