What is the query statement of mysql index

The query statement of mysql index is "SHOW INDEX", which can return the index information associated with the table in the current database or the specified database. The complete syntax is "SHOW INDEX FROM table name [FROM database name];"; where , the "FROM database name" option is optional. If it is omitted, the index information associated with the table in the current database will be returned. If it is not omitted, the index information of the table in the specified database will be returned.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

After the index creation is completed, you can use SQL statements to view the existing indexes. In MySQL, you can use the SHOW INDEX statement to view the indexes created in a table.

MySQL SHOW INDEX statement

The SHOW INDEX statement can return index information associated with a table in the current database or a specified database.

The syntax format for viewing the index is as follows:

SHOW INDEX FROM 表名 [ FROM 数据库名];

The syntax description is as follows:

• Table name: Specify the index to be viewed Data table name.

• Database name: Specify the database where the data table where the index needs to be viewed is located, which can be omitted. For example, SHOW INDEX FROM student FROM test; statement means to view the index of the student data table in the test database.

  If you are not connected to any database, or you want to obtain index information for tables in other databases, you can specify the database name.

Return SHOW INDEXES to the following information:

##             Parameter           illustrate Table           Indicates the name of the data table where the index is created, here is the tb_stu_info2 data table.           Non_unique             Indicates whether the index is a unique index. If it is not a unique index, the value of this column is 1; if it is a unique index, the value of this column is 0.           Key_name             Represents the name of the index.           Seq_in_index             Indicates the position of the column in the index. If the index is a single column, the value of this column is 1; if the index is a combined index, the value of this column is the order of each column in the index definition.           Column_name           Represents the column field that defines the index.           Collation           Indicates the order in which columns are stored in the index. In MySQL, ascending order displays the value "A" (ascending order), if displayed as NULL, it means no classification.           Cardinality           An estimate of the number of unique values ​​in the index. Cardinality counts against statistics that are stored as integers, so even for small tables, the value does not need to be exact. The larger the cardinality, the greater the chance that MySQL will use the index when doing joins.           Sub_part             Represents the number of indexed characters in the column. If the column is only partially indexed, the value of the column is the number of characters indexed; if the entire column is indexed, the value of the column is NULL.           Packed           Indicates how keywords are compressed. If not compressed, the value is NULL.           Null           Used to display whether the index column contains NULL. If a column contains NULL, the value of the column is YES. If not, the value of this column is NO.           Index_type             Shows the type and method used by the index (BTREE, FULLTEXT, HASH, RTREE).           Comment           Show comments.

过滤索引信息

要过滤索引信息，请使用以下WHERE子句：

SHOW INDEXES FROM table_name
WHERE condition;

MySQL的SHOW INDEXES例子

我们将创建一个新表名为contacts演示SHOW INDEXES命令：

CREATE TABLE contacts(
    contact_id int AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    PRIMARY KEY(contact_id),
    UNIQUE(email),
    INDEX phone(phone),
    INDEX name(first_name,last_name) comment '按名字和姓氏'
);

以下命令返回表中的所有索引信息contacts：

SHOW INDEXES FROM contacts;

【相关推荐：mysql视频教程】

The above is the detailed content of What is the query statement of mysql index. For more information, please follow other related articles on the PHP Chinese website!