问题的起因,假设有一张表,里面保存了交易订单,每张订单有唯一的ID,有最后更新时间,还有数据,详情如下:
1
2
3
4
5
6
7
|
+ -------+----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+ -------+----------+------+-----+---------------------+-------+
| UID | int (11) | NO | PRI | 0 | |
| Time | datetime | NO | | 0000-00-00 00:00:00 | |
| Data | int (11) | YES | | NULL | |
+ -------+----------+------+-----+---------------------+-------+
|
针对这张表会做追加及更新的操作,具体来说就是如果订单不存在就INSERT一条新的,如果已存在就UPDATE。由于入库前无法得知相应记录是否已存在,通常的做法无法以下几种:
1、先SELECT一下,再决定INSERT还是UPDATE;
2、直接UPDATE,如果受影响行数是0,再INSERT;
3、直接INSERT,如果发生主键冲突,再UPDATE;
这几种方法都有缺陷,对MySQL来说其实最好的是直接利用INSERT...ON DUPLICATE KEY UPDATE...语句,具体到上面的test表,执行语句如下 :
1
|
INSERT INTO test VALUES (1, '2016-1-1' , 10) ON DUPLICATE KEY UPDATE Time = '2016-1-1' ,Data=10;
|
可以很好的插入或更新数据,一条语句就搞定,至此一直工作得很好。
后来因为查询方式变更,要求将UID和Time两个字段做联合主键,此时表结构如下:
1
2
3
4
5
6
7
|
+ -------+----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+ -------+----------+------+-----+---------------------+-------+
| UID | int (11) | NO | PRI | 0 | |
| Time | datetime | NO | PRI | 0000-00-00 00:00:00 | |
| Data | int (11) | YES | | NULL | |
+ -------+----------+------+-----+---------------------+-------+
|
但是问题来了:一但Time字段被更新,即使是相同的UID,也被数据库认为是不同的主键,因此不会产生主键冲突,上面的语句就失效了,数据库里出现了很多UID相同的数据。
开始寻找解决办法,其实也简单,按MySQL文档里的说明,ON DUPLICATE KEY UPDATE语句判断是否冲突是依靠主键或唯一索引,因此为UID建立唯一索引就可以了。先建索引:
1
|
CREATE UNIQUE INDEX IDX_UID ON test(UID);
|
再测试一下插入:
1
2
|
INSERT INTO test VALUES (1, '2016-1-1' , 10) ON DUPLICATE KEY UPDATE Time = '2016-1-1' ,Data=10;
INSERT INTO test VALUES (1, '2016-2-1' , 20) ON DUPLICATE KEY UPDATE Time = '2016-2-1' ,Data=20;
|