MySQL 常用函数/使用技巧
一、常用函数
1. if
A. 语法：IF(expr1, expr2, expr3) ——>如果表达式expr1成立(真)，返回结果expr2，否则返回结果expr3；
2. ifnull
A. 语法：IFNULL(expr1, expr2) ——>如果expr1的值不为NULL，则返回expr1，否则返回expr2；
3. case when
A. 简单函数：CASE [col_name] WHEN [value1] THEN [result1] … ELSE [default] END，应用场景：枚举这个字段所有可能的值
B. 搜索函数：CASE WHEN [expr] THEN [result1] … ELSE [default] END，应用场景：
C. 举例：
/**
* 查看场所信息状态
* @return
*/
@Query(value = "select case l.status when 1 then '审核中' when 2 then '审核不通过' when 3 then '审核通过' when 4 then '审核同步中' end as status, t2.op_obj, l.id from operate_log l, (select op_obj, max(operate_log_id) as id from operate_log_detail where op_obj in :ids group by op_obj) t2 where l.id = t2.id", nativeQuery = true)
List<Object[]> findAllOperateInfo(@Param("ids") List<Long> ids);
4. substring_index
A. 语法：substring_index (str, delimiter, number) ——> 返回从字符串str的第number个出现的分隔符delimiter之后的子串；
5. concat
A. 语法：concat(str1, str2, ...) ——> 将多个字符串连接成一个字符串；
B. 注意：如果有任何一个参数为null，则返回值为null；
C. 举例：在一个搜索框中模糊匹配name和remark两个字段数据
select category, id from app_web_info where concat(`name`, remark) like concat('%', #{keyword}, '%')
6. group_concat
A. 语法：group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc] [separator '分割符']) ——> distinct可以排除重复值，order by对结果的值进行排序，separator是一个字符串值，缺省为一个逗号，作用是将group by产生的同一个分组中的值连接起来，返回一个字符串结果；
B. 举例：
select app_type as appType, app_id as appId, count(distinct(user_id)) as activeUserNum, count(*) as accessNum, group_concat(distinct user_id) as userIds from access_log group by app_type, app_id
5和6的实例可参考：https://www.cnblogs.com/chLxq/p/11001915.html
7. max
A. 语法：max(expr)：返回字段expr中最大值，是针对数字类型，若是字符串类型，请使用max(expr + 0)，因为字符串类型大小是先比较首字符，然后依次往后比较；
B. 举例：value字段是text类型
SELECT max(CASE WHEN hpr.`name` = 'hostGroupId' THEN hp.VALUE END) AS hostGroupId FROM host_pro hp LEFT JOIN host_pro_ref hpr
ON hpr.id = hp.property_ref where hp.host_id = '1_1.12.109.17'； // 值是5
SELECT max(CASE WHEN hpr.`name` = 'hostGroupId' THEN hp.VALUE + 0 END) AS hostGroupId FROM host_pro hp LEFT JOIN host_pro_ref hpr
ON hpr.id = hp.property_ref where hp.host_id = '1_1.12.109.17'； // 值是48
8. date_format
A. 语法：date_format(date, format) ——> 用于以不同的格式显示日期时间数据
B. format格式：可参考https://www.w3school.com.cn/sql/func_date_format.asp
C. 举例：
select date_format(created_time, '%Y%m') as accessYearMonth, date_format(created_time, '%e') as accessDay date_format(created_time, '%k') as accessHour,
date_format(created_time, '%Y-%m-%d %H') as `time` from access_log group by `time` order by `time`
9. find_in_set
A. 语法：find_in_set(str, strlist)，str代表要查询的字符串，strlist需要查询的字段，参数以逗号分割，用来查询strlist字段值是否包含str的结果
B. 举例：select * from org where FIND_IN_SET('1'，replace(ancestors, '/', ',')) > 0
9. instr
A. 语法：instr(str, substr)，查找一个字符串中是否包含另一个指定的子串。
可参考：MySQL 函数大全
https://blog.csdn.net/qq_30038111/article/details/79611167
二、使用技巧
1. 一个搜索框的值模糊匹配上多个字段
A. select * from 表名 where concat(ifnull('字段1', ''), ifnull( '字段2', ''), ...) like concat('%', 关键字, '%')；
B. ifnull函数的作用是为了解决有任何一个字段参数为null，则该行返回值为null的情况。
2. 查询数据行转列
A. 利用max(case ... when ... then ... else ... end) as ...来实现，注意max函数若查询最大值需要给字段加0；
B. 转换前
select h.id, hp.property_ref, hp.value, hpr.id, hpr.name from host h left join host_properties hp on hp.host_id = h.id left join host_property_reference hpr on hpr.id = hp.property_ref where h.id = '1_127.0.0.0'
C. 转换后
select h.id, max(case hpr.name when 'hostName' then hp.value end) as hostName from host h left join host_properties hp on hp.host_id = h.id left join host_property_reference hpr on hpr.id = hp.property_ref where h.id = '1_127.0.0.0';
3. 运算符优先级
A. and和or联合使用带来的问题，需要用括号解决；
B. 举例：select * from user where a = 1 and b = 2 or c = 3 区别与 select * from user where a = 1 and (b = 2 or c = 3)；
C. 运算符优先级。
4. in关键字查询匹配多个字段：select * from user where (id, name) in ((1, '张三'), (2, '李四'))；
5. 将一张表的数据插入另外一张表
A. 如果两张表的字段一致：insert into table1 select * from table2；
B. 如果只希望导入指定字段：insert into table1 (name1, age1) select name2, age2 from table2。
6. SQL中使用 != 不等于会过滤掉为null的数据，可以使用ifnull(column, '') != 1解决。
