saveOrUpdate

INSERT into mob_distribute_agent_acct_login_status (acct_id,status,token_id)VALUES(2,4,3)

on DUPLICATE KEY UPDATE token_id=5,status = 5

2014-11-14 14:15 385人阅读 评论(0) 收藏 举报

之前写ibatis要实现saveorupdate都是在业务上进行控制,发现mysql 的replace into 语句支持类似的功.

原理是根据唯一索引去判断新插入数据合法性,合法(不存在)则插入,存在则删除.

这在自增主键情况下会使autoincrement +2, 并且原来的数据全部清空存在数据不安全的隐患.

后来又看到了

INSERT INTO TABLE (a,b,c) VALUES 
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

也是根据唯一索引去判断,存在冲突则执行update

有这两个mysql的语法,实现saveorupdate可以更方便安全一些

向数据库插入记录时，有时会有这种需求，当符合某种条件的数据存在时，去修改它，不存在时，则新增，也就是saveOrUpdate操作。这种控制可以放在业务层，也可以放在数据库层，大多数数据库都支持这种需求，如Oracle的merge语句，再如本文所讲的MySQL中的INSERT ... ON DUPLICATE KEY UPDATE语句。

       该语句是基于唯一索引或主键使用，比如一个字段a被加上了unique index，并且表中已经存在了一条记录值为1，下面两个语句会有相同的效果：

[sql] view plain copy print ?

1. INSERT INTO table (a,b,c) VALUES (1,2,3)  
2.   ON DUPLICATE KEY UPDATE c=c+1;  
3.   
4. UPDATE table SET c=c+1 WHERE a=1;  

       ON DUPLICATE KEY UPDATE后面可以放多个字段，用英文逗号分割。使用ON DUPLICATE KEY UPDATE，最终如果插入了一个新行，则受影响的行数是1，如果修改了已存在的一行数据，则受影响的行数是2。

       如果字段b也被加上了unique index，则该语句和下面的update语句是等效的：

[sql] view plain copy print ?

1. UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;  

       如果a=1 OR b=2匹配了多行，则只有一行会被修改。通常的，在ON DUPLICATE KEY UPDATE语句中，我们应该避免多个唯一索引的情况。如果需要插入或更新多条数据，并且更新的字段需要根据其它字段来运算时，可以使用如下语句：

[sql] view plain copy print ?

1. INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)  
2.   ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);  

       在ON DUPLICATE KEY UPDATE后面使用VALUES()方法，这个语句等同于下面的两个语句：

[sql] view plain copy print ?

1. INSERT INTO table (a,b,c) VALUES (1,2,3)  
2.   ON DUPLICATE KEY UPDATE c=3;--1+2  
3. INSERT INTO table (a,b,c) VALUES (4,5,6)  
4.   ON DUPLICATE KEY UPDATE c=9;--4+5  

       如果一个表中包含了一个auto_increment的字段，每次insert数据后，可以通过last_insert_id()方法返回最后自动生成的值，如果通过INSERT ... ON DUPLICATE KEY UPDATE语句修改了一条数据，那么再通过last_insert_id()方法获取的值将不正确，实际测试中是多了一个数，比如向表中增加了3条数据，那么通过last_insert_id()方法得到的值是3，但是通过该语句修改了一条数据后，通过last_insert_id()方法得到的值是4。如果想解决该问题，可以通过如下语句：

[sql] view plain copy print ?

1. INSERT INTO table (a,b,c) VALUES (1,2,3)  
2.   ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;  

       重点是这句id=LAST_INSERT_ID(id)。

       英文原文：https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

       本文来自： 高爽|Coder，原文地址： http://blog.csdn.net/ghsau/article/details/23557915，转载请注明。