MySQL运维实战(4.3) SQL_MODE之ONLY_FULL_GROUP_BY
设置ONLY_FULL_GROUP_BY后，对有group by子句SQL，select的字段要么是group by中的字段，要么对字段进行聚合运算，否则sql执行报错。
不设置ONLY_FULL_GROUP_BY，则对于不在group by字段，不使用聚合函数也不会报错，mysql会随便选取数据返回。
准备测试数据：
mysql> create table t_group(a int, b int); Query OK, 0 rows affected (0.02 sec) mysql> insert into t_group values(1,1),(1,2),(2,2),(2,1); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t_group; +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | 2 | | 2 | 2 | | 2 | 1 |
设置ONLY_FULL_GROUP_BY
mysql> set sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> select a, b from t_group group by a; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_sqlmode.t_group.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
不设置SQL_MODE：
mysql> set sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> select a, b from t_group group by a; +------+------+ | a | b | +------+------+ | 1 | 1 | | 2 | 2 | +------+------+ 2 rows in set (0.00 sec)
上面的SQL虽然能执行，但是并不符合SQL标准。
总结
建议设置ONLY_FULL_GROUP_BY。生产环境修改SQL_MODE需要先进行兼容性测试。
