mysql only_full_group_by 问题

女爷i 2022-06-05 10:41 143阅读 0赞

# sql\_mode=only\_full\_group\_by研读 #

MySQL 5.7.5后only\_full\_group\_by成为sql\_mode的默认选项之一，这可能导致一些sql语句失效。

比如下表game：

<table style="border-collapse:collapse; border-spacing:0px; max-width:100%; background-color:transparent; width:860px; border:1px solid rgb(238,238,238)"> 
 <thead style=""> 
  <tr style=""> 
   <th style="text-align:left; padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> id</th> 
   <th style="text-align:left; padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> group_id</th> 
   <th style="text-align:left; padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> name</th> 
   <th style="text-align:left; padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> score</th> 
  </tr> 
 </thead> 
 <tbody style=""> 
  <tr style=""> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 1</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> A</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 小刚</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 20</td> 
  </tr> 
  <tr style=""> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 2</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> B</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 小明</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 19</td> 
  </tr> 
  <tr style=""> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 3</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> B</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 小花</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 17</td> 
  </tr> 
  <tr style=""> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 4</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> C</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 小红</td> 
   <td style="padding:8px; line-height:20px; vertical-align:top; border:1px solid rgb(238,238,238)"> 18</td> 
  </tr> 
 </tbody> 
</table>

执行sql:   
`select name, group_id from game group by group_id`（记为sql\_make\_group）   
返回：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'game.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
       
        
         1

### 解决方法 ###

1.  把group by字段`group_id`设成primary key 或者 unique NOT NULL。这个方法在实际操作中没什么意义。
2.  使用函数`any_value`把报错的字段`name`包含起来。如，`select any_value(name), group_id from game group by group_id`。
3.  在配置文件my.cnf中关闭sql\_mode=ONLY\_FULL\_GROUP\_BY.。msqyl的默认配置是`sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION`。可以把`ONLY_FULL_GROUP_BY`去掉，也可以去掉所有选项设置成`sql_mode=`，如果你确信其他选项不会造成影响的话。

> [mysql官方文档][mysql]有详细解译。

执行成功后，返回结果应该是

### 为什么默认设置ONLY\_FULL\_GROUP\_BY限制？ ###

对于上述的报错信息，我的理解是`select`字段里包含了没有被`group by`条件唯一确定的字段name。

因为执行sql\_make\_group语句实际上把两行纪录`小明`和`小花`合并成一行，搜索引擎不知道该返回哪一条，所以认为这样的sql是武断的(arbitrary).

解决办法2和3都是禁止检查返回结果的唯一性。

### 进阶讨论 ###

上述办法可以解决报错的问题，但也说明sql语句本身有逻辑问题。`name`字段不应该出现在返回结果，因为它是不确定的。

考虑这样的需求：按`group_id`分组后，找出每组得分最少的人。

执行sql: `select any_value(name) as name, group_id, min(score) as score from game group by group_id order by min(score)`

返回结果

B组的`name`是`小明`（因为`小明`的id更小），而期望结果应该是`小花`。

所以单纯使用`group by`无法实现这样的需求。可以使用临时表的方法：

`select id, name,t.group_id, t.score from (select group_id, min(score) as score from game group by group_id order by min(score)) t inner join game on t.group_id=game.group_id and t.score=game.score`

如果要实现更为复杂的需求，可以考虑使用`group_concat`，请参考[这里][Link 1]。

### Laravel的相关配置 ###

把my.cnf中的`ONLY_FULL_GROUP_BY`去掉后，laravel的运行结果仍然报错。翻过代码后发现，laravel在每一次请求都会设定sql\_mode。实现代码在`Illuminate\Database\Connectors\MySqlConnector`，不同版本稍有不同。

5.2和5.3可以在config/databse.php修改配置。

[
    'connections' =>
        'msyql' => [
            'strict' => true,
            'modes' => [
                'option1',
                'option2',
                ...
            ]
        ],
    ]   
       
        
         1
         2
         3
         4
         5
         6
         7
         8
         9
         10
         11

当`strict=true`(默认)即采用mysql的默认配置，当然也包含`ONLY_FULL_GROUP_BY`。   
当`strict=false`即设置`sql_mode=NO_ENGINE_SUBSTITUTION`。   
也可以自定义，在`modes`下添加相应的配置项。`modes`的优先级比`strict`高。

转载链接[http://blog.csdn.net/allen\_tsang/article/details/54892046][http_blog.csdn.net_allen_tsang_article_details_54892046]

[mysql]: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value%20mysql%E5%AE%98%E7%BD%91
[Link 1]: http://www.cnblogs.com/zhwbqd/p/4205821.html?utm_source=tuicool&utm_medium=referral
[http_blog.csdn.net_allen_tsang_article_details_54892046]: http://blog.csdn.net/allen_tsang/article/details/54892046