巧用sql事半功倍

在mybatis里，曾经踩过几次坑，也用了一些mysql的技巧来应用数据库的功能减少编码的复杂性，尤其是批量操作，下面配合mybatis的应用总结一下4个小技巧（1 传0值 2 批量insert 3 批量update 4 批量insert or update) 。先创建一张表order_item（订单表）如下：

CREATE TABLE `order_item` (
   `id` bigint(20)  unsigned NOT NULL AUTO_INCREMENT COMMENT'自增主键',
    `order_date` date NOT NULL  COMMENT  '订单日期(2017-10-10)',
    `order_type`  tinyint(4) NOT NULL  COMMENT  '订单类型',
    `user_id`  varchar(30) NOT NULL DEFAULT  ''  COMMENT  '用户ID',
    `order_no`  varchar(30) NOT NULL DEFAULT  ''  COMMENT  '订单编码',
    `is_deleted`  tinyint(4) NOT NULL DEFAULT  0 COMMENT  '删除标志(0 未删除 1 删除)',
    PRIMARY KEY(`id`),
    UNIQUE KEY `uniq_orderNo` (`order_no`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '订单表';

1 mybatis传值0,很多时候回忽略 0==isDeleted 导致0传值失败

 <if test="isDeleted !=null and isDeleted !='' or  0==isDeleted">
     and is_deleted = #{isDeleted}
 </if>

2 insert批量写入

   <insert id="insertBatch" parameterType="java.util.List"keyProperty="id" useGeneratedKeys="true">
      INSERT INTO order_item (
      order_date,
      order_type,
      user_id,
      order_no,
      is_deleted)
      VALUES
    <foreach collection="list"item="item"index="index"separator=",">
           (#{item.orderDate},
           #{item.orderType},
           #{item.userId},
           #{item.orderNo},
          #{item.isDeleted} )
       </foreach>
     ;
  </insert>

3 update批量更新,巧用"case ... then " 语句

<update id="updateOrderUserIdBatch"parameterType="list">
    update order_item
    <trim  prefix="set"  suffixOverrides=",">
    <trim  prefix="userId=case"  suffix="end,">
    <foreach  collection="list"  item="i"  index="index">
         <if test="i.userId!=null">
             when id=#{i.id} then #{i.userId}
        </if>
    <foreach>
    </trim>
    </trim>
    where
    <foreach  collection="list"  separator="or"  item="i"  index="index">
      id=#{i.id}
   </foreach>
</update>

4 设置unique key,insert 或 update只需一个方法,巧用" ON DUPLICATE KEY "

<insert id="insertOrUpdateBatch"  parameterType="java.util.List"  keyProperty="id" useGeneratedKeys="true">
    INSERT INTO order_item (
    order_date,
    order_type,
    user_id,
    order_no,
    is_deleted)
    VALUES
  <foreach    collection="list"   item="item"   index="index"   separator=",">
    (#{item.orderDate},
    #{item.orderType},
    #{item.userId},
    #{item.orderNo},
    #{item.isDeleted} )
  </foreach>
    ON DUPLICATE KEY
    UPDATE
    order_date=VALUES(orderDate),
    order_type=VALUES(orderType),
    user_id=VALUES(userId),
    order_no=VALUES(orderNo),
    is_deleted=VALUES(isDeleted)
   ;
</insert>