MySQL常用函数总结

一、DATE_FORMAT

用于以不同的格式显示日期/时间数据。
语法

DATE_FORMAT(date,format)

date为查询的时间，format 为要获得的时间格式
其中format可为：
%a ：返回缩写星期名
%b ：返回缩写月份名
%c ：返回月份的数值
%D：返回带有英文前缀的月中的天
%d ：返回月份的天（00-31）
%e ：返回月份的天（0-31）
%f ：返回微秒
%H：返回小时（00-23）

二、FIND_IN_SET函数

FIND_IN_SET('name','张三,李四,james')

通过内部函数的转换可以装转为类似于 name in（'张三','李四','james'）语句
不过在数据较多的查询中使用FIND_IN_SET函数效率没有使用in效率高

三、GROUP_CONCAT()函数

select id,group_concat(name) from aa group by id;//默认将以，号分割 
或者：select id,group_concat(name separator ',') from aa group by id;//指定分隔符

这个函数可以将查询结果进行拼装，
对时间进行处理得到想要的年份等类型数据

四、时间函数

NOW()：查取现在时间：2020-09-10 17:47:04
CURDATE()：2020-09-10
CURTIME()：17:47:04

五、字符串转换为数字

1、使用conert()进行数据类型转换

SELECT
    dfp.device_id,
    dfp.device_name,
    dfp.device_type,
    dfp.project_id,
    ibp.`project_name ` 
FROM
    device_config dfp
LEFT JOIN project ibp ON CONVERT(dfp.project_id , SIGNED) = ibp.id

2、cast进行转换

SELECT
    dfp.device_id,
    dfp.device_name,
    dfp.device_type,
    dfp.project_id,
    ibp.`project_name`
FROM
    device_config dfp
LEFT JOIN project ibp ON CAST(dfp.project_id AS SIGNED INTEGER) = ibp.id

六、any_value()函数

MySQL5.7之后，sql_mode中ONLY_FULL_GROUP_BY模式默认设置为打开状态。
在此模式下，target list中的值要么是来自于聚合函数(sum、avg、max等)的结果，要么是来自于group by list中的表达式的值。
使用any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝

SELECT any_value(id),any_value(price),brand FROM goods GROUP BY brand;

注意点：当有多个字段查询且分组时可能造成获取结果错误

七、按照汉语拼音排序

mysql使用的默认utf8编码时，如果直接order by 中文字段排序，排序结果会不正确，将字段先转换成gbk编码再排序

CONVERT(fname USING gbk) DESC

八、强制索引

当发现优化查询器使用的索引不是最优索引时可使用此函数强制指定使用什么索引

SELECT
    customer,
    count( 1 ) c 
FROM
    upv_ ** FORCE INDEX ( idx_created ) 
WHERE
    created BETWEEN "2015-07-06" 
    AND "2015-07-07" 
GROUP BY
    customer 
HAVING
    c > 15 
ORDER BY
    c DESC