数据库插入数据，有则更新，无则插入

一、Mysql1、若数据表已设置主键或唯一索引（存在则更新，不存在则插入）insert into table123(id,name,create_time) VALUES (10, 'decimal1',now())ON DUPLICATE KEY UPDATE create_time = now();2、条件判断（不存在则插入，存在不作处理）INSERT INTO `table123` SELE

Mr_YDK

4100人浏览 · 2021-07-01 14:55:39

Mr_YDK · 2021-07-01 14:55:39 发布

一、Mysql

1、若数据表已设置主键或唯一索引（存在则更新，不存在则插入）

insert into table123(id,name,create_time) VALUES (10, 'decimal1',now())
ON DUPLICATE KEY UPDATE create_time = now();

2、条件判断（不存在则插入，存在不作处理）

INSERT INTO `table123` SELECT 9, 'decimal', now() FROM DUAL WHERE not exists (SELECT 1 from `table123` WHERE id = '9');

二、plsql

1、INSERT ON CONFLICT不存在则插入，存在则更新
insert into table123 values (1,'hello digoal',now()) on conflict (id) do update set create_time=now()

2、INSERT ON CONFLICT不存在则插入，存在不作处理

insert into table123 values (1,'hello digoal',now()) on conflict (id) do nothing;

三、Oracle和高斯、达梦

MERGE INTO table_3 T1 USING (
SELECT 0 as id,'名字0' as name,0 as age FROM DUAL
) T2 ON ( T1.id=T2.id) WHEN MATCHED THEN UPDATE SET T1.name = T2.name,T1.age = T2.age WHEN NOT MATCHED THEN INSERT (id,name,age) VALUES(T2.id,T2.name,T2.age);

https://blog.csdn.net/ydk888888/article/details/118557906