MySQL中INSERT，UPDATE和REPLACE的区别与用法

MySQL中INSERT，UPDATE和REPLACE的区别与用法

在涉及到DataBase的开发的过程，经常遇到如下的场景:
业务逻辑需要向数据库插入一条新数据，但是需要做如下的判断:
1. 判断数据库里是否已经存在这样一条记录（有特定的判断依据）；
2.1 如果数据库里没有这条记录，那么全新创建这条记录；
2.2 如果数据库里已有这条记录，那么更新这条记录；

一般情况下，会想到的处理方式如下:
编程代码级控制数据的插入和更新:
synchronized void insertNewRecord(Object data){
Object data = “select * from table where clientId = ‘abc’”;
if(data == null) {
// insert into table;
} else {
// update table;
}
}
这时候考虑到线程安全的问题，给整个方法加上了synchronized关键字，从而保证整个方法的多个步骤形成一个原子操作。 这时候所带的问题也显而易见：
1. 需要通过细心的编程来保证这个操作在多线程下的正确性；
2. 通过synchronize关键字对整个方法进行同步，对系统性能无疑会产生影响；
3. 能数据的操作是通过两次操作的完成的。

那么有没什么方法可以解决上面的问题，将对数据库的两次操作变成一次呢？
在MySql里可以通过以下两种SQL语句来完成：
1. INSERT ON DUPLICATE KEY UPDATE
如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：
mysql>INSERT INTO table (a,b,c) VALUES (1,2,3)
->ON DUPLICATE KEY UPDATE c=c+1;
mysql>UPDATE table SET c=c+1 WHERe a=1;
如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2。
注释：如果列b也是唯一列，则INSERT与此UPDATE语句相当：
mysql>UPDATE table SET c=c+1 WHERe a=1 OR b=2 LIMIT 1;
如果a=1 OR b=2与多个行向匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY子句。
2. REPLACE INTO VALUES
使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。
在使用REPLACE时，表中必须有唯一索引，而且这个索引所在的字段不能允许空值，否则REPLACE就和INSERT完全一样的。
在执行REPLACE后，系统返回了所影响的行数，如果返回1，说明在表中并没有重复的记录，如果返回2，说明有一条重复记录，系统自动先调用了DELETE删除这条记录，然后再记录用INSERT来插入这条记录。如果返回的值大于2，那说明有多个唯一索引，有多条记录被删除和插入。
REPLACE的语法和INSERT非常的相似，如下面的REPLACE语句是插入或更新一条记录。
REPLACE INTO users (id,name,age) VALUES(123, '赵本山', 50);

注: REPLACE和INSERT ON DUPLICATE的区别，在于REPLACE会影响多条结果。比如在表中有超过一个的唯一索引。在这种情况下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后插入这条新记录。假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引。
CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);
假设table1中已经有了3条记录
a b c
1 1 1
2 2 2
3 3 3
下面我们使用REPLACE语句向table1中插入一条记录。
REPLACE INTO table1(a, b, c) VALUES(1,2,3);
返回的结果如下
Query OK, 4 rows affected (0.00 sec)
在table1中的记录如下
a b c
1 2 3
我们可以看到，REPLACE将原先的3条记录都删除了，然后将（1, 2, 3）插入。

最后： 上述两种方法都作用在唯一性索引上，如果你判断重复的列不是唯一性索引，那么这两种方法必然是不适用的，那么这时候还想作为一次操作就必须作用SQL的组合语句了:
INSERT INTO table VALUES (*, * , *, …) WHERe * NOT IN (SELECT * FROM WHERe *=*);
好文要顶 关注我 收藏该文

insert是插入数据 update是更新数据 择列表 选择列表（select_list）指出所查询列，它可以是一组列名列表、星号、表达式、变量（包括局部变量和全局变量）等构成。 1、选择所有列 例如，下面语句显示testtable表中所有列的数据： select *　from testtable 2、选择部分列并指定它们的显示次序 查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。 3、更改列标题 在选择列表中，可重新指定列标题。定义格式为： 列标题=列名　列名 列标题 如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：　select 昵称=nickname，电子邮件=email　from testtable。 4、删除重复行 select语句中使用all或distinct选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为all。使用distinct选项时，对于所有重复的数据行在select返回的结果集合中只保留一行