显示索引 mysql_MySQL显示索引命令

很长一段时间，我无法记住如何显示MySQL数据库表的索引，只需使用SHOW INDEX命令，如下所示：show index from node;

这个例子可以被读作，"显示名为node的数据库表的索引"，假设你已经选择了数据库表，因此实际上，要显示MySQL数据库表索引的完整命令如下所示：# log into your mysql database

$ mysql -u root -p# once in mysql, select your database

mysql> use drupal;Database changed

# now show the index on the node table in the drupal database

mysql> show index from node;

或者如果你愿意，也可以不必切换到数据库，就可以在SHOW INDEX查询中包含数据库的名称，如下所示：show indexes from node from drupal;

语法变化

你可能已经注意到，在最后一个例子中，你不必键入SHOW INDEX ； 以下三个命令中都给出相同的结果：show index from node;show indexes from node;show keys from node;

输出MySQL的SHOW INDEX+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

| term_data | 0 | PRIMARY | 1 | tid | A | 3224 | NULL | NULL | | BTREE | |

| term_data | 1 | taxonomy_tree | 1 | vid | A | 2 | NULL | NULL | | BTREE | |

| term_data | 1 | taxonomy_tree | 2 | weight | A | 2 | NULL | NULL | | BTREE | |

| term_data | 1 | taxonomy_tree | 3 | name | A | 3224 | NULL | NULL | | BTREE | |

| term_data | 1 | vid_name | 1 | vid | A | 2 | NULL | NULL | | BTREE | |

| term_data | 1 | vid_name | 2 | name | A | 3224 | NULL | NULL | | BTREE | |

+-----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

6 rows in set (0.00 sec)

key_name列显示表中索引的名称，因此在本例中，我的term_data表有以下索引：PRIMARY

taxonomy_tree

vid_name

顾名思义，PRIMARY键是表的主键，Seq_in_index列告诉你索引中每个字段的序列，因此taxonomy_tree索引是一个复合索引，键中有以下3个字段：vid

weight

name

查看MySQL索引的另一种方法

这次使用show create table语法，在term_data表上运行时，它看起来像这样：show create table term_data;

以下是该命令的输出：term_data | CREATE TABLE `term_data` (

`tid` int(10) unsigned NOT NULL auto_increment,

`vid` int(10) unsigned NOT NULL default '0',

`name` varchar(255) NOT NULL default '',

`description` longtext,

`weight` tinyint(4) NOT NULL default '0',

PRIMARY KEY (`tid`),

KEY `taxonomy_tree` (`vid`,`weight`,`name`),

KEY `vid_name` (`vid`,`name`)

) ENGINE=MyISAM AUTO_INCREMENT=3228 DEFAULT CHARSET=utf8

从输出中可以看到：KEY `taxonomy_tree` (`vid`,`weight`,`name`),

taxonomy_tree索引在前面显示的序列中有三个字段。

帮助记住这些命令

如果(像我一样)在记住这些MySQL SHOW命令时遇到困难，那么可以记住这个MySQL帮助命令：help show;

在MySQL 5.x中，此帮助命令显示以下输出，包括本文中介绍的两个命令：mysql> help show;Name: 'SHOW'

Description:

SHOW has many forms that provide information about databases, tables,

columns, or status information about the server. This section describes

those following:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

SHOW CREATE DATABASE db_name

SHOW CREATE FUNCTION funcname

SHOW CREATE PROCEDURE procname

SHOW CREATE TABLE tbl_name

SHOW DATABASES [LIKE 'pattern']

SHOW ENGINE engine_name {LOGS | STATUS }

SHOW [STORAGE] ENGINES

SHOW ERRORS [LIMIT [offset,] row_count]

SHOW FUNCTION CODE sp_name

SHOW FUNCTION STATUS [LIKE 'pattern']

SHOW GRANTS FOR user

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW INNODB STATUS

SHOW PROCEDURE CODE sp_name

SHOW PROCEDURE STATUS [LIKE 'pattern']

SHOW [BDB] LOGS

SHOW MUTEX STATUS

SHOW PRIVILEGES

SHOW [FULL] PROCESSLIST

SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]

SHOW PROFILES

SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern']

SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TRIGGERS

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

SHOW WARNINGS [LIMIT [offset,] row_count]

The SHOW statement also has forms that provide information about

replication master and slave servers and are described in [HELP PURGE

MASTER LOGS]:

SHOW BINARY LOGS

SHOW BINLOG EVENTS

SHOW MASTER STATUS

SHOW SLAVE HOSTS

SHOW SLAVE STATUS

If the syntax for a given SHOW statement includes a LIKE 'pattern'

part, 'pattern' is a string that can contain the SQL `%' and `_'

wildcard characters. The pattern is useful for restricting statement

output to matching values.

Several SHOW statements also accept a WHERE clause that provides more

flexibility in specifying which rows to display. See

http://dev.mysql.com/doc/refman/5.0/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.0/en/show.html