MySQL常用函数及Mybatis标签

MySQL常用函数

1.find_in_set(“a”, “a,b,c”):a是否包含在以字符串分隔的一节中
2.FROM_UNIXTIME(create_time, ‘%Y-%m-%d %H:%i:%s’):时间戳转成时间类型
3.DATE_SUB(CURDATE(),INTERVAL 2 DAY):当前时间的前两天
4.date_format(hiredate, ‘%Y-%m-%d %H:%i:%s’):格式化时间
5.ifnull((CASE level WHEN ‘1’ THEN ‘一级’ WHEN ‘2’ THEN ‘二级’ END),‘未评级’) AS level ：为null设置默认值
6.CONCAT(’%’,“a”,’%’):字符串拼接

Mybatis常用标签

if条件判断

<if test="dto.name != null and dto.name !=''">
    AND mha.name LIKE CONCAT('%',#{dto.name},'%')
</if>

foreach循环

<foreach collection="idList" item="item" index="index" open="(" separator="," close=")">
    #{item}
</foreach>

trim自动去掉逗号

insert into ADMININFO
<trim prefix="(" suffix=")" suffixOverrides=",">
	<if test="adminid != null">
		ADMINID,
	</if>
	<if test="adminname != null">
		ADMINNAME,
	</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
		<if test="adminid != null">
			#{adminid},
		</if>
		<if test="adminname != null">
			#{adminname},
		</if>
</trim>

set

update ADMININFO
	<set>
		<if test="adminname != null">
			ADMINNAME = #{adminname},
		</if>
		<if test="adminpsw != null">
			ADMINPSW = #{adminpsw},
		</if>
	</set>
	where ADMINID = #{adminid}

choose

 <choose>
    <when test="isSpecial">
        AND end_time >= CONCAT(#{date}, '10:00:00')
    </when>
    <otherwise>
        AND end_time >= CONCAT(#{date}, '10:00:00')
    </otherwise>
</choose>
AND CONCAT(#{date}, '10:00:00') >= begin_time