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

　　　　C. 可解决in里不能用某个表的字段作为条件（只返回第一条记录）：

　　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';