replace函数sql用法

在涉及到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 *=*);

好文要顶 关注我 收藏该文