[PHP]-Laravel中Group By引发的问题思考_laravel group by

Laravel 和 ThinkPHP 是两个不同的 PHP 框架，它们在底层使用了相同的 SQL 查询语言来与数据库交互。然而，由于框架的设计和实现方式不同，它们在生成 SQL 查询时可能会表现出一些细微的差异，包括对
GROUP BY 子句的处理。

在调用查询公司在线报告接口时，发现我请求的数据室20条每页，但是经查询存在16,17条的都有，并且后几页也不稳定，就一直查询问题
报错信息：

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'gabd3.a.conclusion' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
1

经查询由于mysql中sql_mode中包含：ONLY_FULL_GROUP_BY

1、在RDS控制台的参数设置页面，修改sql_mode参数取值，去掉ONLY_FULL_GROUP_BY条件，忽略GROUP BY的严格检查。修改方法
2、在程序代码中屏蔽该模式

解决方案如下几种：

1、在 Laravel 中手动设置 sql_mode (无效果，可尝试)

如果你不想在服务器级别禁用 only_full_group_by，可以在 Laravel 应用程序中手动设置 sql_mode，例如在 AppServiceProvider 中设置。

use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        DB::statement("SET SESSION sql_mode=''");
    }
}
1
2
3
4
5
6
7
8
9
10

2、在查询业务代码中直接手动设置 sql_mode (无效果，可尝试)

DB::statement("SET SESSION sql_mode= ''");

1
2

3、阿里云RDS中参数设置为空，如果其他mysql服务可以my.ini中配置修改 (无效果，可尝试)

配置 my.cnf 文件，确保 sql_mode 设置正确
确保在 MySQL 的配置文件中（如 my.cnf 或 my.ini），sql_mode 参数已正确设置为空或不包含 only_full_group_by。

步骤：
编辑 MySQL 配置文件（通常位于 /etc/my.cnf 或 /etc/mysql/my.cnf）。

找到 sql_mode 设置，确保 only_full_group_by 没有被包含：

[mysqld]
sql_mode=""
保存文件并重启 MySQL 服务：

sudo systemctl restart mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14

验证mysql中参数设置，查询sql_mode

SELECT @@global.sql_mode
show variables like 'sql_mode%'
1
2

4、检查 SQL 查询本身

确保在使用 GROUP BY 时，所有非聚合列要么包含在 GROUP BY 中，要么在 SELECT 语句中被聚合处理。

由于我的业务要求，不能使用聚合函数处理查询的数据字段。

例如，聚合处理如下的查询：

$results = DB::table('users')
    ->select('name', DB::raw('COUNT(*) as total'))
    ->groupBy('name')
    ->get();
1
2
3
4

确保所有在 SELECT 中的非聚合字段（如 name）都包含在 GROUP BY 子句中。

5、数据库连接时覆盖 SQL_MODE（有效）

在 Laravel 中，你可以通过配置文件 config/database.php 中的 mysql 连接配置选项中显式地设置 sql_mode。

database.php中配置strict模式为false ，然后执行 php artisan config:cache ，就解决

'gene' => [
    'driver'         => 'mysql',
    'url'            => env('DATABASE_URL'),
    'host'           => env('DB_GENE_HOST', '127.0.0.1'),
    'port'           => env('DB_GENE_PORT', '3306'),
    'database'       => env('DB_GENE_DATABASE', 'forge'),
    'username'       => env('DB_GENE_USERNAME', 'forge'),
    'password'       => env('DB_GENE_PASSWORD', ''),
    'unix_socket'    => env('DB_GENE_SOCKET', ''),
    'charset'        => 'utf8mb4',
    'collation'      => 'utf8mb4_unicode_ci',
    'prefix'         => '',
    'prefix_indexes' => true,
    'strict'         => false,
    'engine'         => null,
    'options'        => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
    ]) : [],
],
        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

Thinkphp框架

$data = Db::table('hd_user_rep_inherit')->alias('a')
    ->join('hd_com_inherit b', 'a.did = b.did and b.isshow > 0')
    ->join($table, 'b.did = c.did')
    ->join('hd_com_locinfo d', 'c.locid = d.locid')
    ->where([
        ['a.sid', '=', $sampleId],
        ['a.isshow', '=', 1],
        ['a.conclusion', '<>', -1],
    ])
    ->field('a.id,a.conclusion,a.result,b.chname,c.did,c.dis_inherit_type,c.inhert_type as inherit_type,d.gene_name')
    ->order('a.conclusion desc')
    ->group('c.did')
    ->select();
1
2
3
4
5
6
7
8
9
10
11
12
13

Laravel框架

DB::statement("SET SESSION sql_mode= ''");

 return DB::connection('gene')
        ->table('hd_user_rep_inherit as a')
        ->join('hd_com_inherit as b', 'a.did', '=', 'b.did', 'left')
        ->join($table, 'b.did', '=', 'c.did', 'left')
        ->join('hd_com_locinfo as d', 'c.locid', '=', 'd.locid', 'left')
        ->where([
            ['a.sid', '=', $sid],
            ['a.isshow', '=', 1],
            ['a.conclusion', '<>', -1],
            ['b.isshow', '>', 0]
        ])
        ->select('a.conclusion','b.chname','a.did','c.dis_inherit_type','c.inhert_type as inherit_type','d.gene_name')
        ->orderBy('a.conclusion', 'desc')
        ->orderBy('a.did', 'asc')
        ->groupBy(['a.did'])
        ->paginate(20, ['*'], 'page', $page);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20