如何获取 MySQL 索引列表？

让我们首先了解如何从 MySQL 中显示一个索引。为此，请使用 SHOW 命令。

显示索引的查询如下 −

mysql> SHOW INDEX FROM indexingdemo;

以下是输出。

+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table        | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| indexingdemo |          1 | indexName |            1 | Name        | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     |
+--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.17 sec)

你可以使用以下查询获取 MySQL 索引列表。

在此之前，让我们首先了解一下语法。

SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'yourDatabaseName';

现在，让我们应用上面的语法来获取 MySQL 索引列表。

mysql> SELECT DISTINCT
   ->     TABLE_NAME,
   ->     INDEX_NAME
   -> FROM INFORMATION_SCHEMA.STATISTICS
   -> WHERE TABLE_SCHEMA = 'business';

以下是显示索引列表的输出。

+--------------------------+------------------+
| TABLE_NAME               | INDEX_NAME       |
+--------------------------+------------------+
| addcolumntable           | Name_Index       |
| addingautoincrement      | PRIMARY          |
| addingunique             | name             |
| autoincrement            | PRIMARY          |
| autoincrementtable       | PRIMARY          |
| bookindexes              | BookName         |
| childdemo                | ConstChild       |
| clonestudent             | idIndex          |
| clonestudent             | NameStuIndex     |
| college                  | PRIMARY          |
| compositeprimarykey      | PRIMARY          |
| demoauto                 | PRIMARY          |
| demoindex                | PRIMARY          |
| demoschema               | idDemoIndex      |
| duplicatebookindexes     | BookName         |
| employeeinformation      | PRIMARY          |
| foreigntable             | constFKPK        |
| foreigntabledemo         | FKConst          |
| functionindexdemo        | indFirstName     |
| indexingdemo             | indexName        |
| keydemo                  | PRIMARY          |
| lastinsertrecordiddemo   | PRIMARY          |
| multipleindexdemo        | id               |
| nextiddemo               | PRIMARY          |
| parentdemo               | PRIMARY          |
| primarytable             | PRIMARY          |
| primarytable1            | PRIMARY          |
| primarytabledemo         | PRIMARY          |
| schemadatabasemethoddemo | PRIMARY          |
| sequencedemo             | PRIMARY          |
| student                  | idIndex          |
| student                  | NameStuIndex     |
| studentenrollment        | StudCollegeConst |
| tabledemo2               | ConstFK          |
| tabledemo3               | ConstFK          |
| tablepri                 | PRIMARY          |
| tblf                     | ConstFK          |
| tblp                     | PRIMARY          |
| transcationdemo          | PRIMARY          |
| triggedemo               | PRIMARY          |
| uniqueautoid             | id               |
| uniqueconstdemo          | name             |
| uniquedemo               | name             |
| uniquedemo1              | id               |
| updtable                 | PRIMARY          |
| usernameandpassworddemo  | PRIMARY          |
| usernameandpassworddemo  | UserId           |
+--------------------------+------------------+
47 rows in set (0.07 sec)

更新于： 30-Jul-2019

415 浏览量

开启你的职业生涯

完成课程获取认证

开始
广告