批处理sql

数据集成批处理过程中需要对重复记录进行更新操作，通过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中实现逻辑，使批处理速度提升