upsert(已存在则更新或跳过，中不存在则插入) AnyLine

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

最后更新:2024-09-05 19:09:56 | 状态:未完成

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

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

当然许多数据库都对这种场景提供了原生支持。用过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"); //根据CODE,TYPE列判断数据是否已存在，如果数据存在则忽略
configs.override(false, constraint); //根据唯一约束判断数据是否已存在
configs.override(true, "CODE","TYPE"); //如果存在则覆盖，不存在则正常insert
configs.override(true, constraint); //如果存在则覆盖，不存在则正常insert
false:表示重复时不覆盖直接跳过
CODE,TYPE:表示根据这两列判断是否重复
constraint:表示根据这个约束判断是否重复

注意以下说的是通过configs.override不能实现的情况下的处理方式。

如果数据库不支持重复检测或数据不满足数据库要求(比如无法创建索引)，但还需要实现类似的功能
可以通过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语法】

需要提供判断唯一的列名因为后面on(D.CODE = M.CODE)会用到

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语法】

需要提供判断唯一的列名或(唯一)约束名因为后面 ON CONFLICT会用到

#根据列名
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
需要提供判断唯一的列名因为后面on(D.CODE = M.CODE)会用到

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)(?,?,?)