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);

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

标签： mysql

Answer 1

您可以使用STATISTICSdb中的表information_schema，如下所示：

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