mysql增加惟一索引_mysql 延迟添加唯一索引

MySQL [test]> create table tbl_keyword (

-> id int not null auto_increment primary key,

-> keyword varchar(256) not null

-> )

-> ;

Query OK, 0 rows affected (0.06 sec)

MySQL [test]> insert into tbl_keyword(keyword) values ("aa"),("bb"), ("cc"), ("aa"),("bb");

Query OK, 5 rows affected (0.05 sec)

Records: 5 Duplicates: 0 Warnings: 0

MySQL [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| tbl_keyword |

+----------------+

1 row in set (0.03 sec)

MySQL [test]> desc tbl_keyword;

+---------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| keyword | varchar(256) | NO | | NULL | |

+---------+--------------+------+-----+---------+----------------+

2 rows in set (0.03 sec)

MySQL [test]> alter table tbl_keyword add idempotent_id char(32);

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

MySQL [test]> alter table tbl_keyword add unique index idempotent_id(idempotent_id);

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

MySQL [test]> desc tbl_keyword;

+---------------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| keyword | varchar(256) | NO | | NULL | |

| idempotent_id | char(32) | YES | UNI | NULL | |

+---------------+--------------+------+-----+---------+----------------+

3 rows in set (0.03 sec)

MySQL [test]> select * from tbl_keyword;

+----+---------+---------------+

| id | keyword | idempotent_id |

+----+---------+---------------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | aa | NULL |

| 5 | bb | NULL |

+----+---------+---------------+

5 rows in set (0.03 sec)

MySQL [test]> insert into tbl_keyword(keyword, idempotent_id) values ("aa", md5("aa")), ("bb", md5("bb")), ("cc", md5("cc"));

Query OK, 3 rows affected (0.03 sec)

Records: 3 Duplicates: 0 Warnings: 0

MySQL [test]> select * from tbl_keyword;

+----+---------+----------------------------------+

| id | keyword | idempotent_id |

+----+---------+----------------------------------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | aa | NULL |

| 5 | bb | NULL |

| 11 | aa | 4124bc0a9335c27f086f24ba207a4912 |

| 12 | bb | 21ad0bd836b90d08f4cf640b4c298e7c |

| 13 | cc | e0323a9039add2978bf5b49550572c7c |

+----+---------+----------------------------------+

8 rows in set (0.03 sec)

MySQL [test]> insert into tbl_keyword(keyword, idempotent_id) values ("aa", md5("aa")), ("bb", md5("bb"));

ERROR 1062 (23000): Duplicate entry '4124bc0a9335c27f086f24ba207a4912' for key 'idempotent_id'

MySQL [test]> select * from tbl_keyword;

+----+---------+----------------------------------+

| id | keyword | idempotent_id |

+----+---------+----------------------------------+

| 1 | aa | NULL |

| 2 | bb | NULL |

| 3 | cc | NULL |

| 4 | aa | NULL |

| 5 | bb | NULL |

| 11 | aa | 4124bc0a9335c27f086f24ba207a4912 |

| 12 | bb | 21ad0bd836b90d08f4cf640b4c298e7c |

| 13 | cc | e0323a9039add2978bf5b49550572c7c |

+----+---------+----------------------------------+

8 rows in set (0.03 sec)