MySQL中ON DUPLICATE KEY UPDATE的介绍与使用

哈哈丶就这样吧 2024-08-02Mysql 547 个字 3 分钟

TIP

MySQL中使用ON DUPLICATE KEY UPDATE实现批量更新、存在即更新不存在则插入功能

一、介绍ON DUPLICATE KEY UPDATE

• ON DUPLICATE KEY UPDATE是MySQL中一个语法，用于在INSERT INTO语句中，当插入的数据与表中已有的数据存在重复时，更新表中的数据。它在插入新数据时，如果遇到唯一键冲突（即已存在相同的唯一键值），则会执行更新操作，而不是抛出异常或忽略该条数据。这个语法可以大大简化我们的代码，减少不必要的判断和查询操作。
• ON DUPLICATE KEY UPDATE 语句根据唯一键来判断当前插入是否已存在。
• 记录已存在时，只会更新on duplicate key update之后指定的字段。
• 唯一键大小写敏感时，大小写不同的值被认为是两个值，执行插入。参见下文中的大小写敏感问题

二、使用ON DUPLICATE KEY UPDATE

1. 准备表结构,注意：date是唯一键

CREATE TABLE `fa_attendance_group_calendar` (
    `attendance_group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '考勤组',
    `date` date NOT NULL COMMENT '日期',
    `year` varchar(20) NOT NULL DEFAULT '' COMMENT '年',
    `month` varchar(20) NOT NULL DEFAULT '' COMMENT '月',
    `day` varchar(20) NOT NULL DEFAULT '' COMMENT '日',
    `is_work` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否工作:1=工作,2=休息',
    PRIMARY KEY (`date`),
    UNIQUE KEY `date_idx` (`date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考勤组工作排班日历';

2. 准备测试数据

INSERT INTO `fa_attendance_group_calendar` (`attendance_group_id`, `date`, `year`, `month`, `day`, `is_work`) VALUES (1, '2024-08-01', '2024', '8', '1', 1);
INSERT INTO `fa_attendance_group_calendar` (`attendance_group_id`, `date`, `year`, `month`, `day`, `is_work`) VALUES (1, '2024-08-02', '2024', '8', '2', 1);

• 测试数据如下

3. 使用ON DUPLICATE KEY UPDATE批量更新数据

INSERT INTO `fa_attendance_group_calendar` (`attendance_group_id`, `date`, `year`, `month`, `day`, `is_work`) VALUES (1, '2024-08-02', '2024', '8', '2', 2),(1, '2024-09-02', '2024', '9', '2', 2),(1, '2024-09-03', '2024', '9', '3', 2) ON DUPLICATE KEY UPDATE `is_work` = values(is_work);

• 这条Sql语句的意思是，如果插入的数据与表中已有date的数据存在重复，则更新is_work字段的值为values(is_work)，即插入的数据的值。这条语句结果会更新2024-08-02的is_work数据为2，会新增2024-09-02和2024-09-03的数据。

• 测试结果如下