数据集成批处理过程中需要对重复记录进行更新操作,通过ON DUPLICATE KEY UPDATE来实现,
第一段sql关于在ON DUPLICATE KEY UPDATE之后使用<when>判断的方法
<insert id="insertByBatch" parameterType="java.util.List" >
insert into mf_push_memeber_d (brand_code, if_member_id,
member_code, mem_name, gender,
mobile_phone, email, identity_card,
counter_code_belong, baCodebelong, birth_year,
birth_day, birth_date, join_date,
join_time, member_level_code, total_point,
data_source, create_time, etl_proc_id,
wx_name, wx_nick_name,
wx_avatar, wx_invite, wx_level,
wx_point, wx_usercp, interest_tag,
wx_age_level, wx_gender, wx_province,
wx_city, wx_constellation, wx_channelSource,
wx_platformSource, wx_makeupRate, wx_makeupPurpose,
wx_makeupFavor,
match_flag, push_flag, open_id,
wx_phone_num, subscribe_time, subscribe,
is_register, add_time, first_flag,
level_update_flag, last_update_time,integration_id)
values
<foreach collection="list" item="item" index="index"
separator=",">
(
#{item.brandCode}, #{item.iFMemberId},
#{item.memberCode}, #{item.memName}, #{item.gender},
#{item.mobilePhone}, #{item.email}, #{item.identityCard},
#{item.counterCodeBelong}, #{item.bacodebelong}, #{item.birthYear},
#{item.birthDay}, #{item.birthDate}, #{item.joinDate},
#{item.joinTime}, #{item.memberLevelCode}, #{item.totalPoint},
#{item.dataSource}, now(), #{item.etlProcId},
#{item.wxName}, #{item.wxNickName},
#{item.wxAvatar}, #{item.wxInvite}, #{item.wxLevel},
#{item.wxPoint}, #{item.wxUsercp}, #{item.interestTag},
#{item.wxAgeLevel}, #{item.wxGender}, #{item.wxProvince},
#{item.wxCity}, #{item.wxConstellation}, #{item.wxChannelsource},
#{item.wxPlatformsource}, #{item.wxMakeuprate}, #{item.wxMakeuppurpose},
#{item.wxMakeupfavor},
#{item.matchFlag}, 'Y', #{item.openId},
#{item.wxPhoneNum}, #{item.subscribeTime}, #{item.subscribe},
#{item.isRegister}, #{item.addTime}, #{item.firstFlag},
#{item.levelUpdateFlag}, #{item.lastUpdateTime},#{item.integrationId,jdbcType=VARCHAR}
)
</foreach>
ON DUPLICATE KEY UPDATE
<choose>
<when test="item.brandCode !=null and item.brandCode !='' ">
brand_code = VALUES (brand_code),
</when>
<when test="item.iFMemberId !=null and item.iFMemberId !='' ">
if_member_id = VALUES (if_member_id),
</when>
<when test="item.memName != null and item.memName !='' and item.memName != 'null'">
mem_name = VALUES (mem_name),
</when>
</choose>
mem_name = VALUES (mem_name),
gender = VALUES (gender),
member_code = VALUES (member_code),
total_point = VALUES (total_point),
data_source = VALUES (data_source),
etl_proc_id = VALUES (etl_proc_id),
member_level_code = VALUES (member_level_code),
email = VALUES (email),
identity_card = VALUES (identity_card),
counter_code_belong = VALUES (counter_code_belong),
baCodebelong = VALUES (baCodebelong),
birth_year = VALUES (birth_year),
birth_day = VALUES (birth_day),
birth_date = VALUES (birth_date),
join_date = VALUES (join_date),
join_time = VALUES (join_time),
mobile_phone = VALUES (mobile_phone),
push_flag = 'Y',
first_flag = VALUES (first_flag),
level_update_flag = VALUES (level_update_flag),
last_update_time = now()
</insert>
第一段sql关于在ON DUPLICATE KEY UPDATE之后使用if函数的方法
<insert id="insertByBatch" parameterType="java.util.List" >
insert into mf_push_memeber_d (brand_code, if_member_id,
member_code, mem_name, gender,
mobile_phone, email, identity_card,
counter_code_belong, baCodebelong, birth_year,
birth_day, birth_date, join_date,
join_time, member_level_code, total_point,
data_source, create_time, etl_proc_id,
wx_name, wx_nick_name,
wx_avatar, wx_invite, wx_level,
wx_point, wx_usercp, interest_tag,
wx_age_level, wx_gender, wx_province,
wx_city, wx_constellation, wx_channelSource,
wx_platformSource, wx_makeupRate, wx_makeupPurpose,
wx_makeupFavor,
match_flag, push_flag, open_id,
wx_phone_num, subscribe_time, subscribe,
is_register, add_time, first_flag,
level_update_flag, last_update_time,integration_id)
values
<foreach collection="list" item="item" index="index"
separator=",">
(
#{item.brandCode}, #{item.iFMemberId},
#{item.memberCode}, #{item.memName}, #{item.gender},
#{item.mobilePhone}, #{item.email}, #{item.identityCard},
#{item.counterCodeBelong}, #{item.bacodebelong}, #{item.birthYear},
#{item.birthDay}, #{item.birthDate}, #{item.joinDate},
#{item.joinTime}, #{item.memberLevelCode}, #{item.totalPoint},
#{item.dataSource}, now(), #{item.etlProcId},
#{item.wxName}, #{item.wxNickName},
#{item.wxAvatar}, #{item.wxInvite}, #{item.wxLevel},
#{item.wxPoint}, #{item.wxUsercp}, #{item.interestTag},
#{item.wxAgeLevel}, #{item.wxGender}, #{item.wxProvince},
#{item.wxCity}, #{item.wxConstellation}, #{item.wxChannelsource},
#{item.wxPlatformsource}, #{item.wxMakeuprate}, #{item.wxMakeuppurpose},
#{item.wxMakeupfavor},
#{item.matchFlag}, 'Y', #{item.openId},
#{item.wxPhoneNum}, #{item.subscribeTime}, #{item.subscribe},
#{item.isRegister}, #{item.addTime}, #{item.firstFlag},
#{item.levelUpdateFlag}, #{item.lastUpdateTime},#{item.integrationId,jdbcType=VARCHAR}
)
</foreach>
ON DUPLICATE KEY UPDATE
<choose>
<when test="item.brandCode !=null and item.brandCode !='' ">
brand_code = VALUES (brand_code),
</when>
<when test="item.iFMemberId !=null and item.iFMemberId !='' ">
if_member_id = VALUES (if_member_id),
</when>
</choose>
mem_name = if(mem_name!=VALUES (mem_name),VALUES (mem_name),mem_name),
gender = VALUES (gender),
member_code = VALUES (member_code),
total_point = VALUES (total_point),
data_source = VALUES (data_source),
etl_proc_id = VALUES (etl_proc_id),
member_level_code = VALUES (member_level_code),
email = VALUES (email),
identity_card = VALUES (identity_card),
counter_code_belong = VALUES (counter_code_belong),
baCodebelong = VALUES (baCodebelong),
birth_year = VALUES (birth_year),
birth_day = VALUES (birth_day),
birth_date = VALUES (birth_date),
join_date = VALUES (join_date),
join_time = VALUES (join_time),
mobile_phone = VALUES (mobile_phone),
push_flag = 'Y',
first_flag = VALUES (first_flag),
level_update_flag = VALUES (level_update_flag),
last_update_time = now()
</insert>
第三条,first_flag和level_update_flag是标 记,插入则first_flag 为‘Y’,更新时判断member_level_code会员等级是否发生变化,发生变化则设置level_update_flag为‘Y’,注意此处三行代码的排列顺序,firstflag和level_update_flag的更新语句必须在member_level_code更新语句之前
<insert id="insertByBatch" parameterType="java.util.List" >
insert into mf_push_memeber_d (brand_code, if_member_id,
member_code, mem_name, gender,
mobile_phone, email, identity_card,
counter_code_belong, baCodebelong, birth_year,
birth_day, birth_date, join_date,
join_time, member_level_code, total_point,
data_source, create_time, etl_proc_id,
wx_name, wx_nick_name,
wx_avatar, wx_invite, wx_level,
wx_point, wx_usercp, interest_tag,
wx_age_level, wx_gender, wx_province,
wx_city, wx_constellation, wx_channelSource,
wx_platformSource, wx_makeupRate, wx_makeupPurpose,
wx_makeupFavor,
match_flag, push_flag, open_id,
wx_phone_num, subscribe_time, subscribe,
is_register, add_time, first_flag,
level_update_flag, last_update_time,integration_id)
values
<foreach collection="list" item="item" index="index"
separator=",">
(
#{item.brandCode}, #{item.iFMemberId},
#{item.memberCode}, #{item.memName}, #{item.gender},
#{item.mobilePhone}, #{item.email}, #{item.identityCard},
#{item.counterCodeBelong}, #{item.bacodebelong}, #{item.birthYear},
#{item.birthDay}, #{item.birthDate}, #{item.joinDate},
#{item.joinTime}, #{item.memberLevelCode}, #{item.totalPoint},
#{item.dataSource}, now(), #{item.etlProcId},
#{item.wxName}, #{item.wxNickName},
#{item.wxAvatar}, #{item.wxInvite}, #{item.wxLevel},
#{item.wxPoint}, #{item.wxUsercp}, #{item.interestTag},
#{item.wxAgeLevel}, #{item.wxGender}, #{item.wxProvince},
#{item.wxCity}, #{item.wxConstellation}, #{item.wxChannelsource},
#{item.wxPlatformsource}, #{item.wxMakeuprate}, #{item.wxMakeuppurpose},
#{item.wxMakeupfavor},
#{item.matchFlag}, 'Y', #{item.openId},
#{item.wxPhoneNum}, #{item.subscribeTime}, #{item.subscribe},
#{item.isRegister}, #{item.addTime}, 'Y',
'N', #{item.lastUpdateTime},#{item.integrationId,jdbcType=VARCHAR}
)
</foreach>
ON DUPLICATE KEY UPDATE
<choose>
<when test="item.brandCode !=null and item.brandCode !='' ">
brand_code = VALUES (brand_code),
</when>
<when test="item.iFMemberId !=null and item.iFMemberId !='' ">
if_member_id = VALUES (if_member_id),
</when>
</choose>
mem_name = if(member_level_code!=VALUES (member_level_code),VALUES (mem_name),mem_name),
gender = VALUES (gender),
member_code = VALUES (member_code),
total_point = VALUES (total_point),
data_source = VALUES (data_source),
etl_proc_id = VALUES (etl_proc_id),
first_flag = if(member_level_code!=VALUES (member_level_code),'N','Y'),
level_update_flag = if(member_level_code!=VALUES (member_level_code),'Y','N'),
member_level_code = VALUES (member_level_code),
email = VALUES (email),
identity_card = VALUES (identity_card),
counter_code_belong = VALUES (counter_code_belong),
baCodebelong = VALUES (baCodebelong),
birth_year = VALUES (birth_year),
birth_day = VALUES (birth_day),
birth_date = VALUES (birth_date),
join_date = VALUES (join_date),
join_time = VALUES (join_time),
mobile_phone = VALUES (mobile_phone),
push_flag = 'Y',
last_update_time = now()
</insert>
以上三条的合理使用可以避免在java中实现逻辑,使批处理速度提升