mysql语句高级用法使用记录和sql_mode=only_full_group

mysql语句高级用法使用记录和sql_mode=only_full_group_by错误解决

最近工作时用到的几种用法记录一下

sql_mode=only_full_group_by 报错

sql出错示例如下
column ‘qnaq.ta.issue_org_code’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在这里插入图片描述
原因分析：
这个错误发生在mysql 5.7.5 版本及以上版本会出现的问题：
mysql 5.7.5版本以上默认的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，这个配置严格执行了"SQL92标准"。
很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。

sql层面
在sql执行时，出现该原因，简单来说就是：
由于开启了ONLY_FULL_GROUP_BY的设置，如果select 的字段不在 group by 中，
并且select 的字段未使用聚合函数（SUM,AVG,MAX,MIN等）的话，那么这条sql查询是被mysql认为非法的，会报错误…

最直观的解决方法是在出错的字段上加上关键字any_value

解决方法1

any_value(字段)使用如下所示：

SELECT any_value(字段) FROM 表名
该方法group by分组 case when 等高级语法都可适用

解决方法2

通过sql语句暂时性修改sql_mode，去掉ONLY_FULL_GROUP_BY，重新设置值

windows系统 在mysql安装目录 my.ini中加入以下配置 保存
两个只选其一！！！ 两个只选其一！！！     我使用的是第一种
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

`GROUP_CONCAT`合并字符几种常规用法

GROUP_CONCAT(数据列)
以列分组，把=name字段的值打印在一行，默认逗号分隔开

SELECT GROUP_CONCAT(check_plan_name) FROM t_aj_check_plan

GROUP_CONCAT(字段 order by asc/desc 表字段) 带排序的合并排序字段列

SELECT health_id,GROUP_CONCAT(health_class ORDER BY health_class DESC) AS “排序” FROM gl_health

GROUP_CONCAT函数拼接字符串默认的分隔符是逗号, 不想用逗号的话，SEPARATOR关键字就派上用场了

GROUP_CONCAT综合使用，使用自定字符分割 + 过滤字段数据值排序用法

SELECT health_id,GROUP_CONCAT(DISTINCT 字段 ORDER BY 字段 DESC SEPARATOR ‘分隔符’) AS “DISTINCT过滤相同数据| 不使用默认，分隔符” FROM 表名

`SUBSTRING_INDEX` 按设定隔符截取字符串

SUBSTRING_INDEX是MySQL中一个很实用的字符串处理函数，它的格式如下所示
SUBSTRING_INDEX('待处理字符串', '分隔符', 'count')

使用SUBSTRING_INDEX可以截取到第count个分隔符的位置之前的子字符串。count指定的是第几个分隔符，如果count是正数则从左往右第count个分隔符位置，如果为负数则从右往左计数

 mysql-> SELECT SUBSTRING_INDEX('xx有限公司(总部)|xx有限公司(分公司)|事业总部|综合管理部|虚拟作业区|虚拟班组', '|', 3) AS 'SUBSTRING_INDEX使用'
result-> xx有限公司(总部)|xx有限公司(分公司)|事业总部
 
 mysql-> SELECT SUBSTRING_INDEX('xx有限公司(总部)|xx有限公司(分公司)|事业总部|综合管理部|虚拟作业区|虚拟班组', '|', -2) AS 'SUBSTRING_INDEX使用' 
result-> 虚拟作业区|虚拟班组

SUBSTRING_INDEX嵌套的获取某两个分隔符之间的内容

 mysql-> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('xx有限公司(总部)|xx有限公司(分公司)|事业总部|综合管理部|虚拟作业区|虚拟班组', '|', 3),'|','-1') AS 
'SUBSTRING_INDEX嵌套使用'
result-> 事业总部

`case when`的使用

语法：CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
代码示例

select 
CASE 字段
when 0 THEN '男'
when 1 THEN '女'
ELSE '未知'
end
as '性别' 
from sys_user t

以上是最近工作使用到高级用法，其他高级用法示例如下

SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
SELECT TIMEDIFF('17:00','16:55') AS TIMEDIFF
SELECT TIMESTAMPDIFF(MINUTE,'22:35','22:40') AS TIMESTAMPDIFF #x 

SELECT TIMESTAMPDIFF(MINUTE,'2023-03-05 22:35','2023-03-05 22:40') AS TIMESTAMPDIFF

SELECT COUNT(age='15' OR NULL) age  FROM a 
SELECT SUM(IF(age=15,1,0)) age  FROM a 


SELECT * FROM `a` LIMIT 2,4
SELECT LEFT(depScore,2) FROM `department`

SELECT IFNULL(EmpName,'null') FROM employee

SELECT id,IFNULL(SUBJECT,'NULL') FROM b 

# date_format(日期字段,'格式')='具体年月..'
select
  count(distinct device_id) did_cnt,
  count(question_id) question_cnt
from
  question_practice_detail
where
  date_format(date,'%Y-%m') = '2021-08'