MySQL索引入门 普通索引 唯一索引

参考

使用索引

创建索引

创建索引有3种方式

1. 在创建表时指定索引；

CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] (username(length))  
);  

2. 修改表结构时添加索引；

ALTER table tableName ADD INDEX indexName(columnName)

3. 在已有的表上添加索引；

CREATE INDEX indexName ON table_name (column_name)

---

删除索引

直接删除索引

DROP INDEX [indexName] ON tablename;

在修改表结构时删除索引

ALTER TABLE table_name DROP INDEX index_name;

---

显示索引信息

SHOW INDEX FROM table_name;

---

普通索引

仅加速查询，没有任何限制，用的最多；

CREATE INDEX  index_name  on table_name(columnName)  ;

创建索引
mysql> CREATE INDEX index_test on test_user(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

正常操作即可
select * from test_user where id = 2;  
#注意，这里是columnname不是index_name;

---

唯一索引

CREATE UNIQUE INDEX index_name  on table_name(columnname)  ;

mysql> select * from test_user ;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
|  1 | zhangsan |   16 |      1 |
|  2 | zhangsan |   18 |      1 |
|  3 | lisi     |   22 |      2 |
+----+----------+------+--------+
3 rows in set (0.00 sec)

mysql> CREATE UNIQUE INDEX uniqueindex_name  on test_user(name);
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'uniqueindex_name'

mysql> CREATE UNIQUE INDEX uniqueindex_age  on test_user(age);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

可以看到，唯一索引的column必须是唯一的，name属性重复就不能作为唯一索引；

mysql> select * from test_user ;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
|  3 | lisi     |   22 |      2 |
|  5 | zhangsan |   24 |      2 |
+----+----------+------+--------+

mysql> CREATE UNIQUE INDEX uniqueindex_name on test_user(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

#插入重复的数据时报错；
mysql> insert into test_user(name,age,gender) values("zhangsan",22,2);
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'uniqueindex_name'

mysql> insert into test_user(name,age,gender) values("wangwu",22,2);
Query OK, 1 row affected (0.01 sec)

#插入重复的数据时报错，再插入数据时，id直接从5变为7；
mysql> select * from test_user ;
+----+-----------+------+--------+
| id | name      | age  | gender |
+----+-----------+------+--------+
|  3 | lisi      |   22 |      2 |
|  5 | zhangsan  |   24 |      2 |
|  7 | wangwu    |   22 |      2 |
+----+-----------+------+--------+

unique除了在插入重复数据的时候会报错，还会使auto_increment自动增长；

unique与primary key的区别

简单的讲，primary key=unique+not null

一个表最多只有一个主键，但可以有很多唯一键；

---

全文索引

全文索引（fulltext）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

组合索引

将几个列作为一条索引进行检索，使用最左匹配原则。