本站于2023年9月4日。收到“大连君*****咨询有限公司”通知
说我们IIS7站长博客，有一篇博文用了他们的图片。
要求我们给他们一张图片6000元。要不然法院告我们

为避免不必要的麻烦，IIS7站长博客，全站内容图片下架、并积极应诉
博文内容全部不再显示，请需要相关资讯的站长朋友到必应搜索。谢谢！

另祝：版权碰瓷诈骗团伙，早日弃暗投明。

相关新闻：借版权之名、行诈骗之实，周某因犯诈骗罪被判处有期徒刑十一年六个月

叹！百花齐放的时代,渐行渐远!

---

说明

一、PostgreSQL案例

在PostgreSQL下使用 conflict。

案例1：

表结构

​​

设置唯一键

需要先设置唯一键。（也可以设置多个字段的唯一键）

alter table stu add constraint name_cons unique(name);

如果 没有设置唯一键，错误信息如下:

insert into stu values('王二',35) on conflict(name) do update set age= 35
> ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

> 时间: 0.009s

执行sql

insert into stu values('王二',35) on conflict(name) do update set age=35;

案例2：

		insert into homework.sc_score
			(
				scoreid,
				score,
				fk_question,
				fk_user,
				fk_assignment,
				"createuser",
				createtime
			) 
			values 
			(
				coalesce((SELECT scoreid from homework.sc_score t11 WHERE t11.fk_user = #{stuid} and t11.fk_question = #{fk_test_question} and t11.fk_assignment= #{assignmentid}), #{UUID}),
				#{stuscore},
				#{fk_test_question},
				#{stuid},
				#{assignmentid},
				#{userInfo.GUID},
				to_char(now(), 'YYYY-MM-DD HH24:MI:SS')
			)     
		on conflict (scoreid) do update set     
			score = #{stuscore},
			updateuser = #{userInfo.GUID},
			updatetime = to_char(now(), 'YYYY-MM-DD HH24:MI:SS') 

案例3：

insert into tbl (sid, v1, crt_time) values (:sid, :v1, now())     
on conflict (sid) do update set     
  v1=excluded.v1,     
  crt_time=excluded.crt_time,     
  cnt=tbl.cnt+1,     
  sum_v=case tbl.cnt when 1 then tbl.v1+excluded.v1 else tbl.sum_v+excluded.v1 end,     
  min_v=least(tbl.min_v, excluded.v1),     
  max_v=greatest(tbl.max_v, excluded.v1)      
;     

案例4（批量插入更新）：

INSERT INTO
score.sc_knowledge
(
   "id",
   fk_course,
   fk_grade,
   stu_term,
   parentid,
   nodetext,
   studylevel,
   leaf,
   gradeno,
   fk_stage,
   fk_subject,
   node_type,
   order_number,
   "createuser",
   createtime
)
VALUES
   <foreach collection="dataList" item="item" separator=",">
   <trim prefix="(" suffix=")" suffixOverrides=",">
   #{item.node_id},
   #{item.fk_course},
   #{item.fk_grade},
   #{item.stu_term},
   #{item.node_pid},
   #{item.node_text},
   #{item.studylevel},
   to_number(#{item.node_isleaf},'99'),
   #{item.gradeno},
   #{item.fk_stage},
   #{item.fk_subject},
   #{item.node_type},
   to_number(#{item.order_number},'999999'),
   'sys',
   now()
   </trim>
   </foreach>
   on conflict(id)
   do update set
       fk_course = excluded.fk_course,
       fk_grade = excluded.fk_grade,
       stu_term = excluded.stu_term,
       parentid = excluded.parentid,
       nodetext = excluded.nodetext,
       studylevel = excluded.studylevel,
       leaf = excluded.leaf,
       gradeno = excluded.gradeno,
       fk_stage = excluded.fk_stage,
       fk_subject = excluded.fk_subject,
       node_type = excluded.node_type,
       order_number = excluded.order_number

案例5（批量查询插入更新）：

INSERT INTO learn.sc_sumscore (
"id",
stuname,
fk_examstu,
fk_subject,
score
)
(
SELECT
	fk_examstu,
	stuname,
	fk_examstu,
	fk_subject,
	SUM ( score ) 
FROM
	learn.sc_examstu,learn.sc_score 
WHERE
	sc_examstu.deleteflag = 0 
	AND sc_score.deleteflag = 0 
	AND fk_examstu = sc_examstu.ID 
GROUP BY
	stuname,
	fk_examstu,
	fk_subject
)

ON CONFLICT (fk_examstu) DO UPDATE
SET score = excluded.score

案例6（批量查询插入不更新）：

    INSERT INTO
    exam.sc_score
    (
        "id",
        fk_examstu,
        fk_question,
        uniqueord,
        score,
        "createuser",
        createtime
    )
    (
    select
        nextval('exam.answerscoreid_sequece'),
        sc_examstu.id fk_examstu,
        sc_question.id fk_question,
        uniqueord,
        0,
        'system',
        now()
    from
        exam.sc_examstu,exam.sc_question
    WHERE
        sc_examstu.deleteflag = 0
    and sc_examstu.deleteflag = 0
    and questiontype = 0
    and isparent = 0
    and sc_examstu.fk_paper = sc_examstu.fk_paper
    and submitstatus = 0
    and endtime < to_timestamp(#{currentTime}, 'yyyy-mm-dd HH24:MI:SS')
    )
    on conflict(fk_examstu,fk_question)
    do UPDATE set
    updatetime = now()
    where 1=2

案例7（批量查询插入，数据相同不更新）：

insert into examstu 
(
	idcard,
	stuname,
	age
)
(
select 
	idcard,
	stuname,
	age
from 
	student
)
on conflict(idcard)   
do update   
set   
	stuname=excluded.stuname,
	age=excluded.age
where  
	examstu.stuname is distinct from excluded.stuname 
or  
	examstu.age is distinct from excluded.age

参考：https://blog.csdn.net/weixin_33935505/article/details/89732910
说明：is distinct from 功能是 A和B的数据类型、值完全相同返回 false，否则返回true。详解：https://editor.csdn.net/md/?articleId=115242212

参考：
https://blog.csdn.net/qq_25775675/article/details/105220601

二、Oracle 案例

案例1：

		MERGE INTO SC_EXAMMAPPING T1
		USING (SELECT #{EXAMID} AS FK_EXAM FROM DUAL ) T2
		ON ( T1.FK_EXAM = T2.FK_EXAM)
		WHEN MATCHED THEN
		  UPDATE SET T1.EXAMUSER = #{EXAMUSER}
		WHEN NOT MATCHED THEN 
		  INSERT (EXAMMAPPINGID,FK_EXAM,EXAMUSER) VALUES (#{UUID},#{EXAMID},#{EXAMUSER})

案例2：

MERGE INTO 
			homework.sc_score t1
		USING 
			(SELECT #{stuid} fk_user, #{fk_test_question} as fk_question, #{assignmentid} as fk_assignment ) t2
		ON 
			(t1.fk_user = t2.fk_user and t1.fk_question = t2.fk_question and t1.fk_assignment= t2.fk_assignment)
		when matched then 
			update set 
				t1.score = #{stuscore},
				t1.updateuser = #{userInfo.GUID},
				t1.updatetime = to_char(now(), 'YYYY-MM-DD HH24:MI:SS')
		when  not macthed then 
			insert
			(
				scoreid,
				score,
				fk_question,
				fk_user,
				fk_assignment,
				"createuser",
				createtime
			)
			VALUES
			(
				#{UUID},
				#{stuscore},
				#{fk_test_question},
				#{stuid},
				#{assignmentid},
				#{userInfo.GUID},
				to_char(now(), 'YYYY-MM-DD HH24:MI:SS')
			)

三、MySql 案例

Mysql中有类似merge into 的方法 ON DUPLICATE KEY UPDATE 和 REPLACE INTO

MySQL可以通过如下两个操作语法来实现：
INSERT INTO … ON DUPLICATE KEY UPDATE
或者
REPLACE INTO …

案例1：

FK_EXAM 和 FK_CLASS 是唯一索引或主键，CAUSETEXT 和 UPDATETIME 是要更新的字段。如果发现 CAUSE表里不存在 FK_EXAM 和 FK_CLASS，则进行插入操作，如果存在则进行修改CAUSETEXT 和UPDATETIME 这两个字段。

INSERT INTO CAUSE ( CAUSETEXT, FK_EXAM, FK_CLASS, UPDATETIME ) 
SELECT
	'1' CAUSETEXT,
	'2' FK_EXAM,
	'3' FK_CLASS,
	SYSDATE( ) UPDATETIME 
FROM
	DUAL ON DUPLICATE KEY 
UPDATE 
	CAUSETEXT = VALUES ( CAUSETEXT ),
	UPDATETIME = VALUES ( UPDATETIME )

案例2：
uid是唯一索引或主键

REPLACE INTO test(title,uid) VALUES ('1234657','1001');

cs