mysql中查询某张表的所有索引的方法

郑全

mysql查询索引的方法  ：

比如想查emp的索引：
可以使用以下语句：

show index from emp;

也可以通过sql语句来查

select tab.name as "table_name",idx.name as "index_name",
       fld.name as "index_column_name",fld.pos as "index_column_pos"
  from
        information_schema.innodb_sys_indexes idx,
        information_schema.innodb_sys_tables tab,
       information_schema.INNODB_SYS_FIELDS fld
  where idx.table_id=tab.table_id
     and idx.index_id=fld.index_id
     and tab.name like  'sztech/emp'

mysql> show index from sztech.emp;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          1 | idx_emp_empid |            1 | empid       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> select tab.name as "table_name",idx.name as "index_name",
    ->        fld.name as "index_column_name",fld.pos as "index_column_pos"
    ->   from
    ->         information_schema.innodb_sys_indexes idx,
    ->         information_schema.innodb_sys_tables tab,
    ->        information_schema.INNODB_SYS_FIELDS fld
    ->   where idx.table_id=tab.table_id
    ->      and idx.index_id=fld.index_id
    ->      and tab.name like  'sztech/emp'
    -> ;
+------------+---------------+-------------------+------------------+
| table_name | index_name    | index_column_name | index_column_pos |
+------------+---------------+-------------------+------------------+
| sztech/emp | idx_emp_empid | empid             |                0 |
+------------+---------------+-------------------+------------------+
1 row in set (0.00 sec)