mysql实用的函数

1、RPAD - 填充字符串右边。
RPAD(SUBSTRING(b.NAME,1,1),CHAR_LENGTH(b.NAME),'*') memberName

2、日期格式化
date_format(c.pay_time, '%Y-%m-%d') = date_format(#{date}, '%Y-%m-%d')

3、 CONCAT函数用于连接两个字符串以形成单个字符串

m.invite_id like concat('%',  #{record.account},'%')

4、find_in_set是精确匹配，字段值以英文”,”分隔，find_in_set查询的结果要比like查询更加精确

find_in_set(a.invite_id, x.referrer_ids)

5、常用的in条件为集合

    select * from mall_member
    where  invite_id IN
    <foreach collection = "list" item = "item"  separator=","  open = "(" close = ")" >
        #{item}
    </foreach >
    order by id desc

6、求和判断NUll

sum(IFNULL(a.amount, 0))