在MySQL中索引作为表附属的一部分,无法对索引进行单独维护,都用alter table的方式来进行。
--创建索引
mysql> use test;
Database changed
mysql> alter table department
-> add index dept_name_idx (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
--查看表的索引信息(主键自动生成索引)
mysql> show index from department \G
*************************** 1. row ***************************
Table: department
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: dept_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: department
Non_unique: 1
Key_name: dept_name_idx
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
--删除一个索引
mysql> alter table department
-> drop index dept_name_idx;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from department \G
*************************** 1. row ***************************
Table: department
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: dept_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
mysql>
其他数据库的索引维护方式
Oracle
create index dept_name_idx on department (name);
drop index dept_name_idx;
MySQL、SQL Server
--创建索引
mysql> create index dept_name_idx on department (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
--删除索引
mysql> drop index dept_name_idx on department;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
唯一索引
MySQL 有两种方式
--方式1
mysql> alter table department add unique dept_nme_idx (name);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index dept_nme_idx on department;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
--方式2
mysql> create unique index dept_name_idx
-> on department (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
--查看
mysql> show index from department \G
*************************** 1. row ***************************
Table: department
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: dept_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: department
Non_unique: 0
Key_name: dept_name_idx
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
mysql>