sql_mode案例一则
2023-03-07 09:13:14 时间
和群友讨论,测试了一下ONLY_FULL_GROUP_BY,供大家参考(基于mysql 8.0.27)。
mysql> create database aaa;
Query OK, 1 row affected (0.00 sec)
mysql> use aaa;
Database changed
mysql> create table aa( a int, b varchar(50) );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into aa(a,b) value(1,"aa");
Query OK, 1 row affected (0.01 sec)
mysql> insert into aa(a,b) value(2,"ba");
Query OK, 1 row affected (0.01 sec)
mysql> select b, group_concat(b) from aa;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'aaa.aa.b'; this is incompatible with sql_mode=only_full_group_by
mysql> select b, group_concat(b) from aa group by b;
+------+-----------------+
| b | group_concat(b) |
+------+-----------------+
| aa | aa |
| ba | ba |
+------+-----------------+
2 rows in set (0.00 sec)
mysql> show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set global sql_mode=' ';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> select b, group_concat(b) from aa ;
+------+-----------------+
| b | group_concat(b) |
+------+-----------------+
| aa | aa,ba |
+------+-----------------+
1 row in set (0.00 sec)
# 情况2
mysql> show create table aa\G
*************************** 1. row ***************************
Table: aa
Create Table: CREATE TABLE `aa` (
`a` int DEFAULT NULL,
`b` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> alter table aa add column c varchar(10) default 'c';
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select b, c, group_concat(b) from aa group by a,b;
+------+------+-----------------+
| b | c | group_concat(b) |
+------+------+-----------------+
| aa | c | aa |
| ba | c | ba |
+------+------+-----------------+
2 rows in set (0.00 sec)
mysql> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%sql_mode%';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select b, c, group_concat(b) from aa group by a,b;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'aaa.aa.c' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
ps: 后台回复"技术群",可加本公众号交流群。
相关文章
- 加拿大政府部门使用人工智能驱动的聊天机器人为公共部门员工和组织提供服务
- 使用Fluent Bit与Amazon OpenSearch Service构建日志系统
- java icache_java手写多级缓存
- 在 Amazon SageMaker 上微调与部署语音分离模型
- 新增功能 – 适用于具有更高网络带宽的内存密集型工作负载的 Amazon EC2 X2idn 和 X2iedn 实例
- 使用 Amazon Timestream 和 Amazon Manage Grafana 对Amazon CloudFront 性能进行可视化监控
- 打破偏见 AWS 开发人员关系团队的女性成员
- 基于AWS Cloud Map 的混合微服务架构
- AWS 一周回顾 – 2022 年 3 月 7 日
- 在海外区域使用AWS Client VPN 访问云上VPC 内网资源
- 亚马逊云科技WAF部署小指南(四) 使用Log Hub 自动部署方案进行WAF安全运营
- Amazon DeepRacer 模型训练指南及标准硬件配置流程
- fabric java_Fabric Java SDK最新教程
- 使用 Amazon Connect 搭建电话语音通知服务
- 亚马逊云科技WAF部署小指南(三) 使用OpenSearch进行WAF安全调查
- 亚马逊云科技WAF部署小指南(二) 使用经济实用的Log Insights进行日志分析
- 亚马逊云科技 WAF 部署小指南(一) WAF原理、默认部署及日志存储
- Amazon Ads 功能揭秘
- 借助 AWS Panorama 实现 Edge(边缘)的计算机视觉
- 教妹学Java:Java 发展简史