MySQL讲义第17讲——索引（INDEX）的删除

如果一个索引不需要了，可以删除。另外，索引一旦创建完成则无法修改，但可以删除索引之后重新创建索引，以达到修改索引的目的。删除索引有两种方式：（1）使用 alter table 命令。（2）使用 drop index 命令。

一、使用 alter table 命令删除索引

语法格式如下：

ALTER TABLE 表名 DROP INDEX 索引名;

举例：

（1）查看 stu 表的索引

mysql> show index from stu; +-------+------------+-----------------+--------------+-------------+----------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation +-------+------------+-----------------+--------------+-------------+----------- | stu | 0 | PRIMARY | 1 | s_id | A | stu | 0 | uq_idx_phone | 1 | phone | A | stu | 1 | idx_sname | 1 | s_name | A | stu | 1 | idx_class_sname | 1 | class | A | stu | 1 | idx_class_sname | 2 | s_name | A +-------+------------+-----------------+--------------+-------------+----------- 5 rows in set (0.00 sec)

（2）删除 idx_sname 和 idx_class_sname 两个索引：

mysql> alter table stu drop index idx_sname; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table stu drop index idx_class_sname; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0

（3）查看 stu 表的索引

mysql> show index from stu\G *************************** 1. row *************************** Table: stu Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: s_id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: stu Non_unique: 0 Key_name: uq_idx_phone Seq_in_index: 1 Column_name: phone Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)

二、使用 drop index 命令删除索引

语法格式如下：

DROP INDEX 索引名 ON 表名;

举例：

（1）查看 book 表的索引

mysql> show index from book; +-------+------------+---------------------+--------------+-------------+----------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation +-------+------------+---------------------+--------------+-------------+----------- | book | 0 | PRIMARY | 1 | id | A | book | 0 | uq_idx_isbn | 1 | isbn | A | book | 1 | idx_name | 1 | name | A | book | 1 | idx_publisher_price | 1 | publisher | A | book | 1 | idx_publisher_price | 2 | price | A +-------+------------+---------------------+--------------+-------------+----------- 5 rows in set (0.00 sec)

（2）删除索引 idx_name 和 idx_publisher_price

mysql> drop index idx_name on book; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index idx_publisher_price on book; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

（3）查看 book 表的索引

mysql> show index from book\G *************************** 1. row *************************** Table: book Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: book Non_unique: 0 Key_name: uq_idx_isbn Seq_in_index: 1 Column_name: isbn Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)