6.4 mysql表的索引操作
6.4.1 创建索引
方式一(create):
创建普通索引:create index indexName on tableName(列名)
创建唯一索引:create unique index indexName on tableName(列名)
方式二(alter):
创建普通索引:alter table tableName add index indexName(列名)
创建唯一索引:alter table tableName add unique(列名)
创建主键索引: alter table tableName add primary key (列名)
# 实验一:使用(create)将student表的id设置为索引
mysql> show index from student\G;
Empty set (0.00 sec)
mysql> create index stu_index_id on student(id);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> show index from student\G;
*************************** 1. row ***************************
Table: student
Non_unique: 1
Key_name: stu_index_id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
# 实验二:使用(alter)将student3表的id设置为索引
mysql> show index from student3\G;
Empty set (0.00 sec)
mysql> mysql> alter table student3 add index stu3_index_id (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student3\G;
*************************** 1. row ***************************
Table: student3
Non_unique: 1
Key_name: stu3_index_id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
1 row in set (0.00 sec)
注意:创建唯一索引的时候,该列中不能出现重复值否则索引创建不成功
6.4.2 删除索引
方式一(drop):
删除普通索引和唯一索引:drop index indexName on tableName;
方式二(alter):
删除普通索引和唯一索引:alter table tableName drop index indexName;
删除主键索引:alter table tableName drop primary key
# 实验一 使用方式一删除student索引:
mysql> drop index stu_index_id on student;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> show index from student;
Empty set (0.00 sec)
# 实验三 使用方式二删除student3索引:
mysql> alter table student3 drop index stu3_index_id;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from student3;
Empty set (0.00 sec)