存在就更新,不存在就插入
可以使用INSERT...ON DUPLICATE KEY UPDATE
语句。
先来简单的看一下表的结构:
列名 | 类型 |
---|---|
id | VARCHAR |
stu_id | VARCHAR |
name | VARCHAR |
password | VARCHAR |
其中id
是主键。
那么对于user
表,如果我们需要查询某条数据存不存在,存在的话更新,不存在的话插入,我们一般会这样做:
SELECT COUNT(*) FROM user WHERE id = '1'
- 当第一条语句返回的是
1
时,证明查询到,那么执行更新语句(比如让password
字段加1,这里只是拿password
来举例):UPDATE user SET password = password + 1
- 返回
0
则未查询到,执行插入语句INSERT INTO user VALUES('1', '2', '3', '4')
而如果使用INSERT...ON DUPLICATE KEY UPDATE
,我们只需要一条语句:
INSERT INTO user VALUES('1', '2', '3', '4') ON DUPLICATE KEY UPDATE password = password + 1
这条语句等价于:
UPDATE user SET password = password + 1 WHERE id = '1'
需要注意的一点是:该条语句所涉及的列必须有一个UNIQUE索引或者主键列
而判重的时候就是根据UNIQUE索引列或主键列为依据进行判重。
当判重时,如果UNIQUE索引列或主键列存在,则执行更新,否则执行插入。
如果有重复值,则返回的受影响行数为2,否则受影响行数为1。
在Mybatis
中可以用@Update
来调用:
@Update("INSERT INTO user VALUES('1','2','3','4') ON DUPLICATE KEY UPDATE password = password + 1")
public int updateUser();
来看控制台的打印:
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@55071497] will not be managed by Spring
==> Preparing: INSERT INTO user VALUES('1','2','3','4') ON DUPLICATE KEY UPDATE password = password + 1
==> Parameters:
<== Updates: 1
成功插入了一条数据,当我们再次执行时,控制台的打印情况:
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@55071497] will not be managed by Spring
==> Preparing: INSERT INTO user VALUES('1','2','3','4') ON DUPLICATE KEY UPDATE password = password + 1
==> Parameters:
<== Updates: 2
受影响的行数为2,也就是说表中已经有主键列为1的行了,将该行的数据进行了更新。
存在就删除,不存在就插入
我们可以考虑使用REPLACE
关键字,用法跟INSERT
一样。
如果说INSERT...ON DUPLICATE KEY UPDATE
将UPDATE
和INSERT
合二为一了,那么REPLACE
就是将DELETE
和INSERT
合二为一。
同样以上述表来举例,对于某条数据,如果我们想存在就删除,不存在就插入的话,可以这么写:
REPLACE INTO user VALUES('1','2','3','4')
需要注意的是,与上述一样,该关键字同样需要表中存在UNIQUE索引/主键列,并且该UNIQUE索引所在列不允许为null
如果有重复值,则返回的受影响行数为2,否则受影响行数为1。
在Mybatis
中,我们可以用@Insert
注解来调用:
@Insert("REPLACE INTO user VALUES('1','2','3','4')")
public int insertUser();
第一次调用时,该表中已存在 id='1'
的行,所以删除该行:
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@32a033b6] will not be managed by Spring
==> Preparing: REPLACE INTO user VALUES('1','2','3','4')
==> Parameters:
<== Updates: 2
第二次调用,因为表中没有该行,所以执行插入:
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@55071497] will not be managed by Spring
==> Preparing: REPLACE INTO user VALUES('1','2','3','4')
==> Parameters:
<== Updates: 1
存在则不操作,不存在则插入
Mysql提供了IGNORE
关键字来避免重复插入,其中一个应用是:以UNIQUE索引/主键列为依据,如果该主键/UNIQUE索引列已经存在,则不做任何操作,不存在才插入。
以上述为例,具体的语法为:
INSERT IGNORE INTO user VALUES('1','2','3','4')
同样,该关键字需要表中存在UNIQUE索引/主键列,下面我们来看在Mybatis中的使用:
@Insert("INSERT IGNORE INTO user VALUES('1','2','3','4')")
public int insertUser();
当进行第一次调用的时候,因为表中不存在该记录,所以插入,受影响行数为1:
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@312a738d] will not be managed by Spring
==> Preparing: INSERT IGNORE INTO user VALUES('1','2','3','4')
==> Parameters:
<== Updates: 1
而当第二次调用时,因为表中已经存在该记录,所以不做任何操作,受影响行数为0:
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@55071497] will not be managed by Spring
==> Preparing: INSERT IGNORE INTO user VALUES('1','2','3','4')
==> Parameters:
<== Updates: 0
该关键字另一个应用场景为:对于复制表的操作,避免重复记录。
比如我要复制另一张表到第一张表中。
user
表已经存在id='1'
和id='2'
的记录,如果我们直接复制会怎么样呢?
mysql> INSERT INTO user SELECT * FROM user_copy;
1062 - Duplicate entry '1' for key 'PRIMARY'
会提示,主键列不允许重复插入,此时我们就可以使用IGNORE
关键字来避免这点:
mysql> INSERT IGNORE INTO user SELECT * FROM user_copy;
Query OK, 3 rows affected
Records: 7 Duplicates: 4 Warnings: 4
插入成功。