mysql - 如何判断多列索引是否存在

Question

类似于this question我想要做的是确定索引是否存在，但是使用多个列。例如，我有下表的酒店房间:

CREATE TABLE `rooms` (
 `id` mediumint(9) NOT NULL AUTO_INCREMENT,
 `res_num` int(11) NOT NULL,
 `room_id` int(11) NOT NULL,
 `date` date NOT NULL,
 `adults` tinyint(4) NOT NULL DEFAULT '0',
 `children` tinyint(4) NOT NULL DEFAULT '0',
 `young_children` tinyint(4) NOT NULL DEFAULT '0',
 `pets` tinyint(4) NOT NULL DEFAULT '0',
 `smoking` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `room_id` (`room_id`,`date`),
 KEY `res_num` (`res_num`),
 KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=97449 DEFAULT CHARSET=utf8

我想在 room_id 和 date 上添加一个索引，前提是它还不存在。执行 SHOW INDEX 会产生以下输出:

mysql> show index from rooms;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| rooms |          0 | PRIMARY   |            1 | id          | A         |       67399 |     NULL | NULL   |      | BTREE      |         |               |
| rooms |          0 | room_id   |            1 | room_id     | A         |          58 |     NULL | NULL   |      | BTREE      |         |               |
| rooms |          0 | room_id   |            2 | date        | A         |       67399 |     NULL | NULL   |      | BTREE      |         |               |
| rooms |          1 | res_num   |            1 | res_num     | A         |       67399 |     NULL | NULL   |      | BTREE      |         |               |
| rooms |          1 | date      |            1 | date        | A         |        6739 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
9 rows in set (0.01 sec)

我还可以通过以下方式将输出限制为仅包含有问题的列:

SHOW INDEX FROM rooms WHERE Column_name = 'room_id' OR Column_name = 'date';

然而，各个列可能有自己的索引，如上例中的 date 列。如何确定索引存在于属于同一 Key_name 的多个列上？

Answer 1

首先，发出如下命令

SHOW INDEX FROM rooms
WHERE Seq_in_index > 1 
AND Column_name IN ("id", "date")

您将获得基于多个列的索引列表，并且至少包含您需要的一个列。从结果列表中获取 Key_name，然后对每个结果重复查询:

SHOW INDEX FROM rooms WHERE Key_name=?

如果您在结果中得到两行并且您需要的两列都在列表中，那么您的索引存在。

如果有人知道如何在不依赖外部编程语言的情况下在纯 SQL 中执行此操作，或提出更优化的方法，请改进答案