DROP INDEX 语句 会删除表中的索引。

概要

图片

所需权限

用户必须拥有表的CREATE 权限

参数

Parameter Description
IF EXISTS 如果要删除的索引存在,则删除该索引,如果没有,不会返回错误
table_name 要删除索引所在表的名字. 通过 SHOW TABLES可以查看表名
index_name 要删除索引的名字。通过 SHOW INDEX可以查看索引的名字。

不能删除表的 primary 索引。
CASCADE 删除所有依赖这个索引的对象 (例如 constraints)。 当要删除一个 UNIQUE INDEX, 必须使用 CASCADE.

CASCADE不会列出要删除的对象,请谨慎使用
RESTRICT (Default) 当有对象依赖这个索引时(例如 constraints),不会删除这个索引。

例子

删除一个索引 (无依赖对象)

> SHOW INDEX FROM tbl;
+-------+--------------+--------+-----+--------+-----------+---------+----------+
| Table |     Name     | Unique | Seq | Column | Direction | Storing | Implicit |
+-------+--------------+--------+-----+--------+-----------+---------+----------+
| tbl   | primary      | true   |   1 | id     | ASC       | false   | false    |
| tbl   | tbl_name_idx | false  |   1 | name   | ASC       | false   | false    |
| tbl   | tbl_name_idx | false  |   2 | id     | ASC       | false   | true     |
+-------+--------------+--------+-----+--------+-----------+---------+----------+
(3 rows)
> DROP INDEX tbl@tbl_name_idx;

> SHOW INDEX FROM tbl;
+-------+---------+--------+-----+--------+-----------+---------+----------+
| Table |  Name   | Unique | Seq | Column | Direction | Storing | Implicit |
+-------+---------+--------+-----+--------+-----------+---------+----------+
| tbl   | primary | true   |   1 | id     | ASC       | false   | false    |
+-------+---------+--------+-----+--------+-----------+---------+----------+
(1 row)

使用 CASCADE删除有依赖对象的索引

Warning: CASCADE 删除 所有 依赖这个索引的对象,并且不会列出锁删除的对象, 这样就可能会出现一个不可预料的损失。所以,为了避免这种情况出现, 我们推荐单独删除对象。

> SHOW INDEX FROM orders;
+--------+---------------------+--------+-----+----------+-----------+---------+----------+
| Table  |        Name         | Unique | Seq |  Column  | Direction | Storing | Implicit |
+--------+---------------------+--------+-----+----------+-----------+---------+----------+
| orders | primary             | true   |   1 | id       | ASC       | false   | false    |
| orders | orders_customer_idx | false  |   1 | customer | ASC       | false   | false    |
| orders | orders_customer_idx | false  |   2 | id       | ASC       | false   | true     |
+--------+---------------------+--------+-----+----------+-----------+---------+----------+
(3 rows)
> DROP INDEX orders@orders_customer_idx;
pq: index "orders_customer_idx" is in use as a foreign key constraint
> SHOW CONSTRAINTS FROM orders;
+--------+---------------------------+-------------+------------+----------------+
| Table  |           Name            |    Type     | Column(s)  |    Details     |
+--------+---------------------------+-------------+------------+----------------+
| orders | fk_customer_ref_customers | FOREIGN KEY | [customer] | customers.[id] |
| orders | primary                   | PRIMARY KEY | [id]       | NULL           |
+--------+---------------------------+-------------+------------+----------------+
> DROP INDEX orders@orders_customer_idx CASCADE;

> SHOW CONSTRAINTS FROM orders;
+--------+---------+-------------+-----------+---------+
| Table  |  Name   |    Type     | Column(s) | Details |
+--------+---------+-------------+-----------+---------+
| orders | primary | PRIMARY KEY | [id]      | NULL    |
+--------+---------+-------------+-----------+---------+