MySQL下创建索引

root@mysql-master:~# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.21-70.1-log Source distribution


Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ttt
Database changed
mysql> 
mysql> create table t1 (id int);
Query OK, 0 rows affected (1.81 sec)


mysql> insert into t1 values (1),(2),(3),(1);
Query OK, 4 rows affected (0.17 sec)
Records: 4  Duplicates: 0  Warnings: 0


mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    1 |
+------+
4 rows in set (0.13 sec)


mysql> alter table t1 add primary key pk_t1(id);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> 
mysql> 
mysql> create unique index unique_t1 on t1(id);
ERROR 1062 (23000): Duplicate entry '1' for key 'unique_t1'
mysql> 
mysql> create index idx_t1 on t1(id);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `idx_t1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)


说明：

1）主键需要使用alter table XXX add primary key XXX创建，该字段上不能存在重复数据

2）唯一索引创建时，不能存在重复数据

3）当创建普通索引时，可以存在重复数据