在MySQL表中显示索引 [英] Show indexes in MySQL table
问题描述
如果我在两个字段上定义了一个MySQL索引，我如何找出哪两个字段在一起(使用MySQL命令).
If I define a MySQL index over two fields, how do I find out, which two belong together (using MySQL commands).
这是一个示例表:
mysql> DESCRIBE lansuite_wiki_versions;
+-----------+-----------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+-------------------+-----------------------------+
| versionid | int(11) | NO | PRI | 0 | |
| postid | int(11) | NO | PRI | 0 | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| userid | mediumint(8) unsigned | NO | MUL | 0 | |
| text | text | NO | MUL | NULL | |
| test1 | int(11) | NO | MUL | NULL | |
| test2 | int(11) | NO | | NULL | |
+-----------+-----------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)
此表的索引定义如下:
- versionid + postid
- 用户名
- test1 + test2
- 文本
我知道这一点，因为我已经分配了它们并在phpmyadmin中看到了它们. 但是我也想在我的应用程序中看到它. 所以我找到了这个mySQL命令:
I know this, because I have assigned them and see them in phpmyadmin. But I want to see it in my application as well. So I found this mySQL command:
mysql> SHOW INDEX FROM lansuite_wiki_versions;
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| lansuite_wiki_versions | 0 | PRIMARY | 1 | versionid | A | NULL | NULL | NULL | | BTREE | |
| lansuite_wiki_versions | 0 | PRIMARY | 2 | postid | A | 144 | NULL | NULL | | BTREE | |
| lansuite_wiki_versions | 1 | userid | 1 | userid | A | 4 | NULL | NULL | | BTREE | |
| lansuite_wiki_versions | 1 | test | 1 | test1 | A | 1 | NULL | NULL | | BTREE | |
| lansuite_wiki_versions | 1 | test | 2 | test2 | A | 1 | NULL | NULL | | BTREE | |
| lansuite_wiki_versions | 1 | text | 1 | text | NULL | 1 | NULL | NULL | | FULLTEXT | |
+------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.00 sec)
但是我如何看到versionid + postid已连接? 我可以看到Seq_in_index向上计数.所以我可以依靠那个versionid和postid形成一个公共索引，仅仅因为它们在此输出中排成一行并且Seq_in_index递增了吗? 还是有其他命令显示给我定义了哪些索引?
But how do I see versionid + postid is connected? I can see Seq_in_index counting up. So can I rely on that versionid and postid form a common index, just because they are standing in rows next to each other in this output and the Seq_in_index countin up? Or is there an other command, that shows me which indexes are defined?
推荐答案
每个索引的键名都是唯一的；属于同一索引的列在此表中将具有相同的名称.
The Key_name will be unique for each index; columns which are part of the same index will have the same name in this table.
seq_in_index为您提供顺序.
seq_in_index gives you the sequence.
如果您查看包含索引的INFORMATION_SCHEMA表(请参阅文档)，可能会更有意义.
It may make more sense if you look at the INFORMATION_SCHEMA table containing indexes (look at documentation).
我假设您正在编写一种以编程方式检查数据库结构的工具.
I assume you are writing a tool to programmatically inspect the database structure.
如果您是人类，并且想查看表结构，建议您改用SHOW CREATE TABLE.
If you are a human and want to see the table structure, I recommend SHOW CREATE TABLE instead.
这篇关于在MySQL表中显示索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
