在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>
