极客教程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)
阅读更多：MySQL 教程