✅ 一、空值处理函数（接口默认值最常用）
1. coalesce(a, b, c...)
返回第一个不为 null 的值
✔ 项目例子（接口默认头像）
select id, username, coalesce(avatar, 'https://default.png') as avatar
from user;
2. ifnull(a, b)
判断 null，返回默认值
✔ 项目例子（小程序返回手机号为空）
select id, ifnull(phone, '') as phone
from user;
3. nullif(a, b)
相等返回 null，否则返回 a
✔ 项目例子（避免除 0 报错）
select value / nullif(total, 0) as rate
from stats;
✅ 二、字符串处理函数（后端处理文本必用）
1. concat(a, b, ...)
拼接字符串
✔ 项目例子（构造全地址）
select concat(province, city, area) as address
from user_address;
2. concat_ws(sep, a, b...)
带分隔符拼接（最常用）
✔ 项目例子（小程序显示标签）
select concat_ws(',', tag1, tag2, tag3) as tags
from product;
3. substring(str, start, length)
✔ 项目例子（脱敏手机号）
select concat(substring(phone, 1, 3), '****', substring(phone, 8)) as safe_phone
from user;
4. replace(str, from, to)
✔ 项目例子（替换 https）
select replace(url, 'http://', 'https://') as url
from files;
5. trim(str)
✔ 项目例子（去除输入空格）
select trim(username)
from user;
✅ 三、日期时间函数（系统日志、报表必用）
1. now() / curdate() / curtime()
✔ 项目例子（记录登录时间）
update user set last_login = now()
where id = 10;
2. date_format(date, '%Y-%m-%d %H:%i:%s')
✔ 项目例子（接口返回格式化时间）
select date_format(create_time, '%Y-%m-%d %H:%i:%s') as create_time
from orders;
3. unix_timestamp() / from_unixtime()
✔ iot 固件上传记录时间戳
insert into device_log(time) values(unix_timestamp());
4. date_add / date_sub
✔ 查询 7 天内数据
select * from orders
where create_time >= date_sub(now(), interval 7 day);
5. timestampdiff(unit, t1, t2)
✔ 统计用户在线分钟数
select timestampdiff(minute, login_time, logout_time) as online_minutes
from user_online_log;
✅ 四、数学函数（报表、统计常用）
1. sum()
✔ 统计订单金额
select sum(amount) as total_amount
from orders;
2. count()
✔ 统计用户数量
select count(*) from user;
3. avg()
✔ 小程序评分
select avg(score) as avg_score
from comment;
4. max() / min()
✔ 查询设备最大电量记录
select max(power) from device_status;
5. round(x, d)
✔ 显示百分比
select round(success / total * 100, 2) as rate
from stats;
✅ 五、条件函数
1. if(condition, a, b)
✔ 接口返回状态中文
select id, if(status = 1, '正常', '禁用') as status_text
from user;
2. case when then
✔ 复杂条件显示
select
case
when level = 1 then '普通用户'
when level = 2 then 'vip 用户'
else '未知'
end as level_name
from user;
✅ 六、json 函数（你的项目频繁用到）
1. json_object(key, value...)
✔ 返回单个角色结构
select json_object('id', id, 'name', role_name) as role
from roles;
2. json_array(value...)
✔ 构造简易数组
select json_array(id, username) as arr
from user;
3. json_arrayagg(value)
✔ 聚合 user → roles 数组
select
u.id,
json_arrayagg(r.role_name) as roles
from user u
left join user_role ur on ur.user_id = u.id
left join roles r on r.id = ur.role_id
group by u.id;
4. json_objectagg(key, value)
✔ 根据 role_key → role_info
select
u.id,
json_objectagg(
r.role_key,
json_object('id', r.id, 'name', r.role_name)
) as roles
from user u
...
✅ 七、分组函数
1. group_concat()
✔ 标签转字符串
select group_concat(tag_name) as tags
from product_tag
where product_id = 10;
2. distinct()
✔ 不重复设备型号
select distinct(model)
from devices;
✅ 八、加密函数
1. md5(str)
用于签名校验、文件一致性
✔ 校验固件版本 hash
select md5(file_data) from firmware;
2. sha1(str)
✔ 用户 token
select sha1(concat(id, '-', now()));
✅ 九、类型转换
1. cast(x as type)
✔ 字符串数字转换成 int
select cast(amount as signed) from orders;
