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)