
lazydays
V1
2023/01/13阅读:17主题:默认主题
sql_mode案例一则
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
作者介绍

lazydays
V1