mysql - 从sql语句中获取特定的列

Question

为了显示索引，我们使用以下查询，

show indexes from student;
+---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY               |            1 | ROLL_NO     | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | stu_roll_no_age_index |            1 | ROLL_NO     | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | stu_roll_no_age_index |            2 | AGE         | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------+

现在我尝试只从上面的表格中选择如下，

select Table, Key_name, Column_name,Index_type from (show indexes from student);

我想我可能是错的，但我不知道如何推理？请告诉我。此外，我如何从此类查询的结果中仅获取特定列。

Answer 1

您可以使用来自 db information_schema 的表 STATISTICS，如下所示:

select TABLE_NAME, INDEX_NAME, COLUMN_NAME, INDEX_TYPE
from information_schema.STATISTICS WHERE TABLE_NAME = 'student';