[MySQL]-[操作指令]-索引

一、创建表时指定

命令

案例

mysql> CREATE TABLE table1(
    -> c1 int,
    -> c2 int,
    -> c3 char(1),
    -> PRIMARY KEY(c1),
    -> INDEX idx_c2_c3 (c2,c3)
    -> );
Query OK, 0 rows affected (0.02 sec)

二、修改表结构增加索引

命令

案例

以步骤一创建的 table1 为例，增加一个字段 c4 ，并为其单独设置索引

## 新增字段
mysql> ALTER TABLE table1 ADD INDEX idx_c4 (c4);
ERROR 1072 (42000): Key column 'c4' doesn't exist in table
mysql> ALTER TABLE table1 ADD c4 char(2);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

## 将新增的字段设置索引
mysql> ALTER TABLE table1 ADD INDEX idx_c4 (c4);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

三、删除索引

命令

案例

删除步骤二创建的 c4 字段对应的索引

mysql> ALTER TABLE table1 DROP idx_c4;
ERROR 1091 (42000): Can't DROP 'idx_c4'; check that column/key exists
mysql> ALTER TABLE table1 DROP INDEX idx_c4;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc table1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
| c2    | int(11) | YES  | MUL | NULL    |       |
| c3    | char(1) | YES  |     | NULL    |       |
| c4    | char(2) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)