SQL

1. 分组取最值

select *
from (select * from t_user having 1 ORDER BY created desc) a
GROUP BY spbh

2. 时间戳格式化

SELECT
FROM_UNIXTIME(auto.update_time / 1000, '%Y-%m-%d %H:%i:%S') as createTime

3. 库的磁盘占用

select TABLE_SCHEMA,concat(truncate(sum(data_length) / 1024 / 1024 / 1024, 2), 'GB') as data_size,concat(truncate(sum(index_length) / 1024 / 1024, 2), 'MB')       as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;

4. 表的磁盘占用

select TABLE_NAME,
concat(truncate(data_length / 1024 / 1024 / 1024, 2), 'GB') as data_size,
concat(truncate(index_length / 1024 / 1024 / 1024, 2), 'GB') as index_size
from information_schema.tables
where TABLE_SCHEMA = 'tp'
group by TABLE_NAME
order by data_length desc;

5. 流量转化

select case
when useAmount < 1024 then concat(useAmount, 'b')
when useAmount > 1024 and useAmount <= (1024 * 1024) then concat(round(useAmount / 1024, 2), 'Kb')
when useAmount >= (1024 * 1024) and useAmount < (1024 * 1024 * 1024)
then concat(round(useAmount / (1024 * 1024), 2), 'Mb')
when useAmount > (1024 * 1024 * 1024) and useAmount < (1024 * 1024 * 1024 * 1024)
then concat(round(useAmount / (1024 * 1024 * 1024), 2), 'Gb')
when useAmount > (1024 * 1024 * 1024 * 1024)
then concat(round(useAmount / (1024 * 1024 * 1024 * 1024), 2), 'Tb') end useAmount

Mybaties

1. 条件判断

select CASE typeWHEN 0 THEN '(Auto)'WHEN 2 THEN '(StandBy)'WHEN 3 THEN '(Private)'WHEN 4 THEN '(Other)'ELSE '' END as platForm
from router_instance_auto

2. 循环

方案1 , 字符串分割循环

auto.public_IP in
<foreach collection="ip.split(',')" open="(" separator="," close=")" item="item" index="index">#{item}
</foreach>

方案2 list 循环, 可能会有空指针

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

方案3 list 不会产生空指针

where peer_id in ('-1'<foreach item="item" collection="futureList" open="," separator=",">#{item}</foreach>
)

4. 模糊查询

like concat('%',#{email},'%')