Easy to learn and use MySQL features unknown

Source: Internet
Author: User
Tags mysql command line

This article will introduce you to these unknown features.

View query results in XML format

By using the traditional-xml option to call the MySQL command line client program, 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:


Shell> mysql -- xml

Mysql> SELECT * FROM test. stories;

This is a test

This is the second test

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

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

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

Mysql> 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 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 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>

From this document, you can obtain a complete list that supports MySQL client program changes.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.