在从MySQL迁移数据库到达梦数据库中,原来的MySQL中的mybatis语句:ON DUPLICATE KEY UPDATE 更新或插入进行改造可以参考一下形式:
1、针对一条数据的更新或者插入可以这样执行:
MERGE INTO t_person T1 USING ( SELECT 1 id, 'test' mc, 1 sex, 25 age, FROM dual ) T2 ON (T1.id = T2.id) WHEN NOT MATCHED THEN INSERT(id, mc, sex, age) VALUES (T2.id, T2.mc, T2.sex, T2.age) WHEN MATCHED THEN UPDATE SET T1.mc = T2.mc,T1.sex = T2.sex,T1.age = T2.age
2、针对多条数据进行批量更新或插入操作可以这样执行:
MERGE INTO t_person T1 USING ( <foreach collection="list" item="item" index="index" separator="UNION ALL"> SELECT #{item.id} id, #{item.mc} mc, #{item.sex} sex, #{item.age} age FROM dual </foreach> ) T2 ON (T1.id = T2.id ) WHEN NOT MATCHED THEN INSERT(id, mc, sex, age) VALUES (T2.id, T2.mc, T2.sex, T2.age) WHEN MATCHED THEN UPDATE SET T1.mc = T2.mc,T1.sex = T2.sex,T1.age = T2.age
对于达梦数据库的更新merge into语句,可以替换MySQL的ON DUPLICATE KEY UPDATE,方便数据迁移。