1 将查询出的结果为null 的转换为0
COALESCE(SUM(s.num),0)
2 null 赋值
IFNULL(name,0)
2 对二进制位判断位数
status : 二进制字段
AND
s.status & 1 > 0 ( 第一位为1)
AND
s.status & 2 > 0 ( 第二位为1)
AND
s.status & 4 > 0 (第三位为1)
AND
s.status & 8 > 0 (第四位为1)
AND
s.status & 16 > 0 (第五位为1)
3 排除结果null 值
SELECT
filed
FROM
表
WHERE
filed IS NOT null
4 批量更新
<update id="updateBatch">
<foreach collection="list" separator=";" item="item">
UPDATE t_product_stock_warn
<set>
<if test="item.filed1 != null">
filed1 = #{item.filed1,jdbcType=INTEGER},
</if>
<if test="item.filed2 != null">
filed1 = #{item.filed2,jdbcType=INTEGER},
</if>
</set>
WHERE id = #{item.id,jdbcType=INTEGER}
</foreach>
</update>
5 批量插入
INSERT INTO 表(filed1,filed2)
VALUES
<foreach collection="list" item="item" separator=",">
(#{item.filed1,jdbcType=INTEGER},
#{item.filed2,jdbcType=VARCHAR})
</foreach>
6 模糊查询
name LIKE concat('%',#{name,jdbcType=VARCHAR},'%')
7 格式化日期
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
DATE_FORMAT(NOW(),'%m-%d-%Y')
DATE_FORMAT(NOW(),'%d %b %y')
DATE_FORMAT(NOW(),'%d %b %Y %T:%f')
8 特殊符号
& &
< <
> >
<= <=
>= >=
9 判断集合 !=null 并且 != Empty
<if test="list !=null and list.size()>0 ">
AND od.supplier_id IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item,jdbcType=INTEGER}
</foreach>
</if>
10 if else
<choose>
<when test="brandId == 1 ">
.....
</when>
<otherwise>
....
</otherwise>
</choose>
11 分组统计
SELECT
CASE
WHEN real_amount BETWEEN 10 AND 50 THEN '10-50'
WHEN real_amount BETWEEN 60 AND 80 THEN '60-80'
WHEN real_amount BETWEEN 80 AND 200 THEN '80-100'
ELSE 'other'
END AS 'price',
COUNT(real_amount) total
FROM
t_order
WHERE
order_status IN ( '03', '04' )
GROUP BY
price
12 截取字符串
- 10-20 -> 20
substring_index(#{priceRange},'-',-1)
- 10-20 -> 10
substring_index(#{priceRange},'-',1)
13 Mapper中获取当前时间戳(毫秒)
REPLACE(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)),'.','')
14 获取当前时间戳(秒级)
unix_timestamp(now()) = 1346836984
15 时间戳转为日期格式
FROM_UNIXTIME(ROUND(create_time/1000))