联合主键中ON DUPLICATE KEY UPDATE的使用

一，ON DUPLICATE KEY UPDATE 一般使用

ON DUPLICATE KEY UPDATE 是mysql 中的特有的语言 在插入单条数据时如果不存在则正常插入如果引起主键冲突或违反唯一约束则执行更新操作，大多数应用情景就是改变count的值。

// 先插入一条数据 role_id 为主键
INSERT INTO role(role_id,role_name,role_state) VALUES (6,'测试',0);

这个时候我们再插入role_id = 6 的数据就会插入失败。但是执行以下语句就不会有问题，他在插入失败之后会执行后面的语句

INSERT INTO role(role_id,role_name,role_state) VALUES (6,'测试',0) 
ON DUPLICATE KEY UPDATE role_state = 1;
//上面语句等效于
UPDATE role SET role_state =1 WHERE role_id=6;

二，在发生多出冲突时的问题

上面阐述了一出违反主键约束时的情况，那么当违反多处唯一约束时 where 子句又会是怎么样的呢？

1. 给表的role_name 加上唯一约束

2. 已有数据为
   ；

3. 现在执行

    INSERT INTO role(role_id,role_name,role_state) VALUES (6,'总经理',0) 
    ON DUPLICATE KEY UPDATE role_state = 0;
   

   那么由2的样例数据可知 冲突了第一行的role_name = '总经理’和第五行的role_id = 6
   那么在执行update 会生效在哪一行？
   
   可以看出仅仅是第六行发生了改变。 由网上资料说这种多行冲突 sql 相当于

   UPDATE role SET role_state=0 WHERE role_id = 6 OR role_name ='总经理'  LIMIT 1;
   

   所以再多行冲突的情况下应该避免使用这种操作，否则会有出乎意料的结果

三，联合主键中发生冲突的情况

1. 改变表结构，把role_name 由唯一约束改成第二个主键与role_id 组成联合主键
   
2. 由于联合主键 只有在完全匹配的情况下才会冲突 这里我们把 6 测试 改成 2 测试
   
3. 先在再执行

    INSERT INTO role(role_id,role_name,role_state) VALUES (2,'测试',1) 
    ON DUPLICATE KEY UPDATE role_state = 1;
   

   会不会和上面的

   UPDATE role SET role_state=1 WHERE role_id = 2 OR role_name ='测试'  LIMIT 1;			
   

   语句一样呢？
   结果并不是。这种情况经测试两种sql语句结果并不一样也就谈不上等价了。具体过程就不展示图片了，要是有兴趣可以自己尝试。现在出现的这种情况与二的情况不相同倒是与一有点类似。这里我个人觉得可以先暂时理解为

   UPDATE role SET role_state=1 WHERE role_id = 2 AND role_name ='测试'  LIMIT 1;
   

   这样的sql (如果理解不正确，感谢评论指正。谢谢！)

结语

1. 通过以上三种情况的描述 针对于单条插入或者更新是需要分情况选择使用ON DUPLICATE KEY UPDATE 的。如果是简单的单条数据主键或者唯一约束冲突可以使用。
2. 如果是主键和唯一约束引起的多起冲突会产生 or 的逻辑关系会导致数据更新在无法预测的行里。
3. 但是因为联合主键引起的冲突，由网上资料和看似也会产生 or 的逻辑关系，但是在多次试验测试之后 条件之间是 and 的逻辑关系。可以由于联合主键完全匹配的唯一约束而指定到唯一的一行里。可以明确数据更新的位置，是可以放心大胆的使用。 如果是主键和唯一约束的多起冲突会产生or 的逻辑关系会导致数据更新在无法预测的行里。但是因为联合主键引起的冲突，看似也会产生or 的逻辑关系，但是在多次试验测试之后 条件之间是 and 的逻辑关系。可以由于联合主键完全匹配的唯一约束而指定到唯一的一行里。可以明确数据更新的位置，是可以放心大胆的使用的。