已存在则更新或跳过，中不存在则插入

最后更新:2023-11-15 05:30:35 | 状态:未完成

在数据中台、数据同步场景中经常有这种需求，如果数量少可以清空后再全量同步一次，这样最省心。也可以插入前检测一下数据是否存在。

在如果数量很大，以方式显然不可取，如果自己实现一般是通过索引(性能)+存储过程(一次交互)实现

当然许多数据库都对这种场景提供了原生支持。用过ORM实现过这种功能的人一定知道，这个SQL并不好写，一种库一种语法，特别是Oracle的SQL那是真不友好。

如果在JAVA和XML中生成这个SQL是不是很机械。后面几个语法仅作参考，编码时不需要自己写。【注意后面的数据库语法仅作为参考，anyline中会自动生成不需要出现在项目代码中】
  • anyline
  • ORM
  • Oracle语法
  • PG语法
  • MySQL语法
  • Apache Ignite语法
  • Apache hive2.3.5+语法
  • VodtDB语法
  • 全部展开
【anyline】

对于这种不是很常用的功能anyline一般是通过ConfigStore来实现 service.insert(table, set, configs);

对于MySQL,PostgreSQL,Oracle等都是一样的操作，但不同的数据库有不同的要求，

如MySQL根据唯一索引，PG可以根据指定列，但必须是唯一索引中的列，

而Oracle虽然不友好但对各种情况的支持却更强一些语法上可以随意指定列

当然无论怎么随便一定要考虑性能，更不能违反唯一约束

ConfigStore configs = new DefaultConfigStore():
configs.override(false, "CODE","TYPE");
configs.override(false, constraint);
configs.override(false, "CODE","TYPE");
configs.override(false, constraint);
false:表示重复时不覆盖直接跳过
CODE,TYPE:表示根据这两列判断是否重复
constraint:表示根据这个约束判断是否重复


如果不满足数据库要求，比如无法创建索引，但还需要实现类似的功能
可以通过DataSet/DataRow提供的setOverride(true|false)实现
DataRow row = new DataRow();
row.put()...
row.setOverride(true)
service.save(row);
参考【关于override】
【ORM】
复制一个MySQL的示例简单瞅一眼吧，有耐心的就看看，这只是XML部分
<insert id="insertDuplicateKeyUpdateList">
    INSERT INTO student(`s_id`,`s_name`,`s_birth`,`s_sex`)
    VALUES
    <foreach collection="students" item="item" separator=",">
        (
            #{item.sId,jdbcType=VARCHAR},
            #{item.sName,jdbcType=VARCHAR},
            #{item.sBirth,jdbcType=VARCHAR},
            #{item.sSex,jdbcType=VARCHAR}
        )
    </foreach>
    ON DUPLICATE  KEY UPDATE
    <trim prefix="" suffixOverrides=",">
        <foreach collection="students" separator="," item="item">
            <if test="item.sId != null and item.sId != ''">
                s_id = VALUES(s_id),
            </if>
            <if test="item.sName != null and item.sName != ''">
                s_name = VALUES(s_name),
            </if>
            <if test="item.sBirth != null and item.sBirth != ''">
                s_birth = VALUES(s_birth),
            </if>
            <if test="item.sSex != null and item.sSex != ''">
                s_sex = VALUES(s_sex)
            </if>
        </foreach>
    </trim>
</insert>

【Oracle语法】
MERGE INTO CRM_USER M
USING (
SELECT I.ID AS ID, I.CODE AS CODE, I.NAME AS NAME, I.REG_DATE AS REG_DATE, I.REG_TIME AS REG_TIME, I.DATA_VERSION AS DATA_VERSION
FROM( 
	SELECT ? AS ID,? AS CODE,? AS NAME,? AS REG_DATE,? AS REG_TIME,? AS DATA_VERSION FROM DUAL 
	UNION ALL
	SELECT ? AS ID,? AS CODE,? AS NAME,? AS REG_DATE,? AS REG_TIME,? AS DATA_VERSION FROM DUAL 
	UNION ALL
	SELECT ? AS ID,? AS CODE,? AS NAME,? AS REG_DATE,? AS REG_TIME,? AS DATA_VERSION FROM DUAL 
    ) I 
) D ON(D.CODE = M.CODE)
WHEN NOT MATCHED THEN 
INSERT(M.ID,M.CODE,M.NAME,M.REG_DATE,M.REG_TIME,M.DATA_VERSION)VALUES(D.ID,D.CODE,D.NAME,D.REG_DATE,D.REG_TIME,D.DATA_VERSION)
WHEN MATCHED THEN 
UPDATE SET M.ID = D.ID,M.NAME = D.NAME,M.REG_DATE = D.REG_DATE,M.REG_TIME = D.REG_TIME,M.DATA_VERSION = D.DATA_VERSION
【PG语法】
#根据列名
INSERT INTO CRM_USER(CODE,NAME) VALUES (?,?),(?,?),(?,?) ON CONFLICT(CODE) DO UPDATE SET CODE = EXCLUDED.CODE,NAME = EXCLUDED.NAME
#根据约束
INSERT INTO CRM_USER(CODE,NAME) VALUES (?,?),(?,?),(?,?) ON CONFLICT ON CONSTRAINT u_code DO UPDATE SET CODE = EXCLUDED.CODE,NAME = EXCLUDED.NAME
#跳过
INSERT INTO CRM_USER(CODE,NAME) VALUES (?,?),(?,?),(?,?) ON CONFLICT ON CONSTRAINT u_code DO NOTHING
【MySQL语法】
#不覆盖，重复数据忽略 影响行数0
INSERT IGNORE INTO tab_override(CODE1,CODE2,CODE3,QTY) VALUES (?,?,?,?)

#覆盖,重复数据更新 影响行数2
#注意这里执行的是delete+insert 而不是update 所以影响2行
REPLACE INTO tab_override(CODE1,CODE2,CODE3,QTY) VALUES (?,?,?,?)
【Apache Ignite语法】
#相同主键覆盖，实际执行MERGE INTO，这时CODE被更新成新值
MERGE INTO tab_override(ID,CODE) VALUES (?,?)
【Apache hive2.3.5+语法】
参考Oracle
MERGE INTO CRM_USER M
USING (
SELECT I.ID AS ID, I.CODE AS CODE, I.NAME AS NAME, I.REG_DATE AS REG_DATE, I.REG_TIME AS REG_TIME, I.DATA_VERSION AS DATA_VERSION
FROM( 
    SELECT ? AS ID,? AS CODE,? AS NAME,? AS REG_DATE,? AS REG_TIME,? AS DATA_VERSION FROM DUAL 
    UNION ALL
    SELECT ? AS ID,? AS CODE,? AS NAME,? AS REG_DATE,? AS REG_TIME,? AS DATA_VERSION FROM DUAL 
    UNION ALL
    SELECT ? AS ID,? AS CODE,? AS NAME,? AS REG_DATE,? AS REG_TIME,? AS DATA_VERSION FROM DUAL 
    ) I 
) D ON(D.CODE = M.CODE)
WHEN NOT MATCHED THEN 
INSERT(M.ID,M.CODE,M.NAME,M.REG_DATE,M.REG_TIME,M.DATA_VERSION)VALUES(D.ID,D.CODE,D.NAME,D.REG_DATE,D.REG_TIME,D.DATA_VERSION)
WHEN MATCHED THEN 
UPDATE SET M.ID = D.ID,M.NAME = D.NAME,M.REG_DATE = D.REG_DATE,M.REG_TIME = D.REG_TIME,M.DATA_VERSION = D.DATA_VERSION

【VodtDB语法】
注意Voltdb不支持批量插入，需要命令行工具jdbcloader,csvloader或java实现，如果调用AnylineService.save/update/insert时 会自动实现批量操作，即自动添加batch参数=100
如果存在只支持覆盖，不支持跳过
UPSERT CRM_USER(ID,CODE,NAME)(?,?,?)

最近更新 搜索 提交