1.mysql 批量insert

<insert id="insertBatch" parameterType="List">
  insert into zhiyi_item_property (id, item_id, shop_id,
  brand, root_category_id, root_category_name,
  category_id, category_name, season,
  sale_time, style, pic_url, property_json)
  values
  <foreach collection="list" item="item" separator=",">
    (#{item.id,jdbcType=INTEGER}, #{item.itemId,jdbcType=BIGINT}, #{item.shopId,jdbcType=BIGINT},
    #{item.brand,jdbcType=VARCHAR}, #{item.rootCategoryId,jdbcType=INTEGER}, #{item.rootCategoryName,jdbcType=VARCHAR},
    #{item.categoryId,jdbcType=INTEGER}, #{item.categoryName,jdbcType=VARCHAR}, #{item.season,jdbcType=VARCHAR},
    #{item.saleTime,jdbcType=TIMESTAMP}, #{item.style,jdbcType=VARCHAR}, #{item.picUrl,jdbcType=VARCHAR},
    #{item.propertyJson,jdbcType=LONGVARCHAR})
  </foreach>
</insert>

2.mysql 批量update

动态表名用$,不会自动加上引号

<update id="markFail" >
  update ${tableName}
  set record_state='N',version=version+1
  where record_state='Y'
  and ${key} in (
  <foreach collection="list" item="item" index="index" open="" close="" separator=",">
    #{item}
  </foreach>
  )
</update>

3.mysql 批量delete

<delete id="commonDelete">
  delete from  ${tableName} where ${key} in (
  <foreach collection="list" item="item" index="index" open="" close="" separator=",">
    ${item}
  </foreach>
  )
</delete>

4.mysql 查询 where in

select
<include refid="Base_Column_List" />
from hw_app_shop_market_skc ib
<trim prefix="WHERE" prefixOverrides="AND | OR" >
  ib.record_state='Y'
  <if test="year != null and year.size > 0 " >
    and ib.year_code in
    <foreach collection="year" item="item" open="(" separator="," close=")">
      #{item}
    </foreach>
  </if> 
  <if test="skc != null and skc != ''" >
    and ib.skc= #{skc,jdbcType=VARCHAR}
  </if>
</trim>
order by  app_shop_type,skc