MySQL复合主键下ON DUPLICATE KEY UPDATE语句失效问题

最新推荐文章于 2025-06-17 13:22:47 发布

转载最新推荐文章于 2025-06-17 13:22:47 发布 · 834 阅读

文章标签：

#java #数据库

本文探讨了MySQL中使用INSERT...ON DUPLICATE KEY UPDATE语句进行数据操作的方法，并介绍了如何通过建立唯一索引来解决联合主键更新时的问题。

问题的起因，假设有一张表，里面保存了交易订单，每张订单有唯一的ID，有最后更新时间，还有数据，详情如下：

+-------+----------+------+-----+---------------------+-------+
| 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表，执行语句如下：

INSERT INTO test VALUES (1, '2016-1-1', 10) ON DUPLICATE KEY UPDATE Time='2016-1-1',Data=10;

可以很好的插入或更新数据，一条语句就搞定，至此一直工作得很好。

后来因为查询方式变更，要求将UID和Time两个字段做联合主键，此时表结构如下：

+-------+----------+------+-----+---------------------+-------+
| 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建立唯一索引就可以了。先建索引：

CREATE UNIQUE INDEX IDX_UID ON test(UID);

再测试一下插入：

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;