What are the common application skills in MySQL databases?

The following articles mainly introduce some special practical application skills that we all know in the MySQL database. If you are interested in the related practical application skills, you can click here to view the following articles.

View query results in XML format

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

MySQL database query results

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  
  
  
2  
  
  
This is the second test  
  
  
2rows 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.

The repair table command is used to demonstrate the 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 database 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 = 'joe';  
  
  
+----------+  
  
  
| 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 the MySQL database, 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

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

 
 

  
  
Emysql> 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 database 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 database 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>  
 
 

The above content is a little-known introduction to some of the special skills of the MySQL database. I hope you will have some gains.