为什么在我的`INSERT ... ON DUPLICATE KEY UPDATE`中有 2 行受到影响?
我正在为下表中的 PRIMARY KEY 执行 INSERT ... ON DUPLICATE KEY UPDATE:
I'm doing an INSERT ... ON DUPLICATE KEY UPDATE for a PRIMARY KEY in the following table:
DESCRIBE users_interests;
+------------+---------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------------------+------+-----+---------+-------+
| uid | int(11) | NO | PRI | NULL | |
| iid | int(11) | NO | PRI | NULL | |
| preference | enum('like','dislike','ignore') | YES | | NULL | |
+------------+---------------------------------+------+-----+---------+-------+
但是，即使这些值应该是唯一的，我看到有 2 行受到影响.
However, even though these values should be unique, I'm seeing 2 rows affected.
INSERT INTO users_interests (uid, iid, preference) VALUES (2, 2, 'like')
ON DUPLICATE KEY UPDATE preference='like';
Query OK, 2 rows affected (0.04 sec)
为什么会这样?
编辑
为了比较，请看这个查询:
For comparison, see this query:
UPDATE users_interests SET preference='like' WHERE uid=2 AND iid=2;
Query OK, 1 row affected (0.44 sec)
Rows matched: 1 Changed: 1 Warnings: 0
推荐答案
来自 手册:
使用 ON DUPLICATE KEY UPDATE，如果每行受影响的行值为 1该行作为新行插入，并且 2如果更新了现有行.
With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated.
相关文章