数据库系统原理与应用教程（037）—— MySQL 的索引（三）：删除索引

• 数据库系统原理与应用教程（037）—— MySQL 的索引（三）：删除索引
• • 一、删除索引的命令
  • 二、使用 alter table 命令删除索引
  • 三、使用 drop index 命令删除索引

如果一个索引不需要了，可以删除。另外，索引一旦创建完成则无法修改，但可以删除索引之后重新创建索引，以达到修改索引的目的。

删除索引有两种方式：（1）使用 alter table 命令。（2）使用 drop index 命令。

删除索引命令的语法格式如下：

ALTER TABLE 表名 DROP INDEX 索引名;
-- 或
DROP INDEX 索引名 ON 表名;

语法格式如下：

ALTER TABLE 表名 DROP INDEX 索引名;

例如：

1、创建表同时创建索引

mysql> create table t51(
    ->     id int primary key auto_increment,
    ->     name char(20),
    ->     birth datetime,
    ->     salary int,
    ->     phone char(20),
    ->     email varchar(50),
    ->     index idx_name(name),
    ->     unique index uq_phone(phone),
    ->     index idx_name_email(name,email(10))
    -> );
Query OK, 0 rows affected (0.04 sec)

2、查看表 t51 中的索引

mysql> show index from t51G
*************************** 1. row ***************************
        Table: t51
   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: t51
   Non_unique: 0
     Key_name: uq_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: 
*************************** 3. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name_email
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name_email
 Seq_in_index: 2
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: 10
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.01 sec)

3、删除索引 idx_name_email

mysql> alter table t51 drop index idx_name_email;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t51G
*************************** 1. row ***************************
        Table: t51
   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: t51
   Non_unique: 0
     Key_name: uq_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: 
*************************** 3. row ***************************
        Table: t51
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

语法格式如下：

DROP INDEX 索引名 ON 表名;

例如：删除表 t51 中的索引 uq_phone

mysql> drop index uq_phone on t51;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t51G
*************************** 1. row ***************************
        Table: t51
   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: t51
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)