南大通用 GBase 8s 中Insert into on duplicate key update 使用介绍

一、概述

在数据库操作中，插入和更新数据是常见的需求。GBase 8s 提供语法  INSERT INTO ... ON DUPLICATE KEY UPDATE ，它能够在一条 SQL 语句中智能地根据唯一性约束（主键或唯一索引）的冲突情况，实现“如果记录不存在则插入，如果存在则更新”的功能。本文将介绍这一语法的使用方法，并通过实例展示其在实际应用中的优势。

INSERT INTO ... ON DUPLICATE KEY UPDATE，在一个SQL语句内，智能地根据目标表上的唯一性约束（主键或唯一索引）的冲突情况，“如果记录不存在则插入，如果存在则更新”。

传统实现：先查询（SELECT），然后根据查询结果分支处理（INSERT 或 UPDATE）。
对比传统实现，这种方式减少了数据库交互和网络开销，简化代码，高效简洁。

二、语法实现

INSERT INTO table_name (col1, col2, ..., colN)
VALUES (value1, value2, ..., valueN)
ON DUPLICATE KEY UPDATE
   col1 = value1_update,
   col2 = value2_update,
...;
**INSERT INTO ... VALUES ...：标准的插入语句,支持单值，多值，子查询。
**ON DUPLICATE KEY UPDATE：触发更新的子句。value_update 可以是具体的值（eg：col1 ='a';col1 =1），函数表达式，也可以是引用原插入值的表达式（eg：col2 = values(col2)）。

示例：

INSERT INTO user_score (user_id, score, update_time)
VALUES (123, 10, NOW())
ON DUPLICATE KEY UPDATE
    score = score + VALUES(score), -- 将新分值累加到原有分值上
    update_time = VALUES(update_time); -- 将更新时间更新为“当前时间”

三、使用举例

创建测试表并插入数据：

set environment sqlmode 'mysql';
-- 创建示例表
CREATE TABLE IF NOT EXISTS `users` (
 `id` INT PRIMARY KEY,           -- 主键
 `username` VARCHAR(50) UNIQUE,  -- 唯一索引
 `score` INT DEFAULT 0,
 `login_count` INT DEFAULT 0,
 `last_login` DATETIME,
 `status` VARCHAR(20)
);
-- 初始化一些测试数据
INSERT INTO `users` (`id`, `username`, `score`) VALUES
(1, 'zhangsan', 100),
(2, 'lisi', 80);

示例1：单值插入与直接更新

-- 用户ID=3不存在则插入，存在则更新分数和状态
INSERT INTO `users` (`id`, `username`, `score`, `status`) 
VALUES (3, 'wangwu', 60, 'active')
ON DUPLICATE KEY UPDATE
   `score` = 80,                     -- 直接指定固定值
   `status` = 'inactive';            -- 直接指定固定值
    
SELECT * FROM `users` ORDER BY `id` where id=3;

示例2：使用VALUES()函数引用插入值

-- 用户ID=1已存在，更新时使用VALUES()引用欲插入的值
INSERT INTO `users` (`id`, `username`, `score`) 
VALUES (1, 'zhangsan_new', 150)
ON DUPLICATE KEY UPDATE
   `username` = VALUES(`username`),  -- 引用VALUES中的'zhangsan_new'
`score` = VALUES(`score`);        -- 引用VALUES中的150

示例3：更新值为表达式或函数

-- 用户ID=2已存在，更新时使用表达式和函数
INSERT INTO `users` (`id`, `username`, `login_count`) 
VALUES (2, 'lisi', 1)
ON DUPLICATE KEY UPDATE
   `login_count` = `login_count` + 1,  -- 在原值基础上加1
   `last_login` = NOW(),               -- 使用函数更新当前时间
`score` = `score` + VALUES(`login_count`) * 10;  -- 复杂表达式

示例4：从其他表获取插入值

--创建user_backup表
create table user_backup as select * from users where 2=1;
Insert into user_backup(id,username,score) values(1,'wanglaowu',100),(2, 'bajie',80);
--从user_backup表中恢复数据
INSERT INTO `users` (`id`, `username`, `score`)
SELECT `id`, `username`, `backup_score` 
FROM `user_backup` 
WHERE `score` is not null
ON DUPLICATE KEY UPDATE
   `username` = VALUES(`username`),
   `score` = GREATEST(`score`, VALUES(`score`));  -- 取原分与备份分中较大者
    

以上演示数据库版本：GBase8sV8.8_AEE_3.6.5

INSERT INTO ... ON DUPLICATE KEY UPDATE  语法为数据库操作提供了高效简洁的解决方案，减少了数据库交互和网络开销，同时简化了代码逻辑。希望本文的介绍能帮助你在实际开发中更好地利用这一功能。如果你在使用过程中遇到任何问题，欢迎随时在社区中提问，我们在这里为你提供支持！