为什么在我的`INSERT ... ON DUPLICATE KEY UPDATE`中有2行受到影响? [英] Why are 2 rows affected in my `INSERT ... ON DUPLICATE KEY UPDATE`?
本文介绍了为什么在我的`INSERT ... ON DUPLICATE KEY UPDATE`中有2行受到影响?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在为下表中的PRIMARY KEY
做INSERT ... ON DUPLICATE KEY UPDATE
:
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.
这篇关于为什么在我的`INSERT ... ON DUPLICATE KEY UPDATE`中有2行受到影响?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文