mysql“数据不存在插入，存在则更新”实现

参考文章:Mysql:如果数据存在则更新，不存在则插入

场景

工作中有遇到需要配置一些指定的字段数据，但数据量大，不清楚之前是否有配置过，正确的思路应该是如果有这条数据了，那么更新数据的值，如果没有这条数据，那么应该插入一条数据。
比如需要根据不同国家进行不同功能的开启或者关闭，表结构如下：

CREATE TABLE `region_config_info` (
  `id` varchar(64) NOT NULL,
  `create_time` bigint(20) DEFAULT NULL COMMENT '创建时间',
  `func_desc` varchar(128) DEFAULT NULL COMMENT '功能说明',
  `func_enable` bit(1) DEFAULT b'0' COMMENT '0-未开启,1-已开启',
  `func_type` int(4) NOT NULL COMMENT '功能类型',
  `region_code` varchar(8) NOT NULL COMMENT '国家的alpha2码',
  `update_time` bigint(20) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `region_config_info_region_code_IDX` (`region_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1
2
3
4
5
6
7
8
9
10
11

插入三条数据：

INSERT INTO region_config_info
(id, create_time, func_desc, func_enable, func_type, region_code, update_time)
VALUES(UUID(), current_timestamp(), '是否支持二维码分享', b'0', 0, 'FR', current_timestamp());

INSERT INTO region_config_info
(id, create_time, func_desc, func_enable, func_type, region_code, update_time)
VALUES(UUID(), current_timestamp(), '免费抽奖活动', b'0', 1, 'FR', current_timestamp());

INSERT INTO region_config_info
(id, create_time, func_desc, func_enable, func_type, region_code, update_time)
VALUES(UUID(), current_timestamp(), '打折活动', b'1', 2, 'FR', current_timestamp());
1
2
3
4
5
6
7
8
9
10
11

插入后，表数据如图所示：

更新/插入数据

知道唯一索引的值

mysql语法支持数据存在更新，不存在插入。判断的依据是唯一索引的字段是否存在，对于本文中的例子就是主键id。
如果已知主键id，那么可以使用DUPLICATE、REPLACE INTO方式处理数据

DUPLICATE

语法

INSERT INTO 表名(唯一索引列, 列2, 列3) VALUE(值1, 值2, 值3) ON DUPLICATE KEY UPDATE 列=值, 列=值
1

测试的sql如下：

• 对于aa4b49ff-661b-11ed-ae64-6c4b90aa5ada这条数据，如果没有则插入，如果有，则更新func_enable为关闭且更新update_time。

INSERT INTO region_config_info(id, create_time, func_desc, func_enable, func_type, region_code, update_time) 
VALUE("aa4b49ff-661b-11ed-ae64-6c4b90aa5ada", current_timestamp(), '打折活动', b'0', 2, 'FR', current_timestamp()) 
ON DUPLICATE KEY UPDATE func_enable= b'0',update_time=current_timestamp()
1
2
3

执行结果如下：结果为有本条数据，因此更新了func_enable和update_time

• 对于1这条数据，如果没有则插入，如果有，则更新

INSERT INTO region_config_info(id, create_time, func_desc, func_enable, func_type, region_code, update_time) 
VALUE("1", current_timestamp(), '打折活动', b'1', 2, 'CN', current_timestamp()) 
ON DUPLICATE KEY UPDATE func_enable= b'1',update_time=current_timestamp()
1
2
3

执行结果如下：结果为新插入一条id为1的数据

REPLACE INTO

语法

REPLACE INTO 表名称(列1, 列2, 列3) VALUES(值1, 值2, 值3)
1

测试的sql如下：

• 对于1这条数据，如果没有则插入，如果有，则更新。

REPLACE INTO region_config_info(id, create_time, func_desc, func_enable, func_type, region_code, update_time)  
VALUES("1", current_timestamp(), '打折活动', b'1', 2, 'IN', current_timestamp()) 
1
2

执行结果如下：结果为有本条数据，数据都按照新的数值插入的，create_time和update_time都更新了

测试的sql如下：

• 对于2这条数据，如果没有则插入，如果有，则更新。

REPLACE INTO region_config_info(id, create_time, func_desc, func_enable, func_type, region_code, update_time)  
VALUES("2", current_timestamp(), '免费抽奖活动', b'1', 1, 'IN', current_timestamp()) 
1
2

执行结果如下：结果为无本条数据，插入一条id为2的数据

并不知道唯一索引的值，只想插入/更新符合某些条件的数据

如果不知道主键id的情况下，根据部分条件查找进行插入或更新数据，比如：配置国家IN的“是否支持二维码分享”活动为开启，但并不知道这条数据在或者不在，也不知道主键id的情况，该如何处理呢？

插入语句

INSERT INTO region_config_info(id, create_time, func_desc, func_enable, func_type, region_code, update_time)  
SELECT UUID(), current_timestamp(), '是否支持二维码分享', b'1', 0, 'IN', current_timestamp()
from DUAL  
where not exists(select id from region_config_info where region_code = 'IN' and func_type = 0); 
1
2
3
4

执行结果：不存在region_code为IN且func_type是0的数据，因此插入了这条数据。可以多次运行这条数据，并没有新插入数据，且原来数据的create_time、update_time都没有更新。
如图：

更新语句

update region_config_info c set c.func_enable = 1, c.update_time = current_timestamp()
where id in 
(select a.id from
(select * from region_config_info where func_type = 0 and region_code in ('IN','FR') and func_enable != 1) as a)
1
2
3
4

执行结果：更新了FR国家的func_type=0的开启状态，IN的由于已经是1了，所以无需更新，可以看到结果中的update_time是有改变的。此语句可以批量更新一些指定条件的数据。
如图：