Gorm 解析sql报错 - Nineya|java/go/python

正常运行

  db.Table("roles").
        Select("id", "name", "description", "GROUP_CONCAT(permission_id) as permission_ids").
        Joins("LEFT JOIN roles_permission ON roles.id = roles_permission.role_id").
        Where("name='xxx'").  // 或者 Where(fmt.Sprintf("name='%s'", "xxx")).
        First(&results)

使用 Where("name=?", "xxx") 报错

  db.Table("roles").
        Select("id", "name", "description", "GROUP_CONCAT(permission_id) as permission_ids").
        Joins("LEFT JOIN roles_permission ON roles.id = roles_permission.role_id").
        Where("name=?", "xxx").
        First(&results)

报错结果 2024/12/23 22:37:20 /Users/rongts/study-go/meida-admin-server/internal/interfaces/test/main.go:31 Error 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'meida_dev.roles.id'; this is incompatible with sql_mode=only_full_group_by [2.615ms] [rows:0] SELECT id,name,description,GROUP_CONCAT(permission_id) as permission_ids FROM roles LEFT JOIN roles_permission ON roles.id = roles_permission.role_id WHERE name='xxx' ORDER BY roles.id LIMIT 1

打印的 sql 是可以正常运行的 SELECT id,name,description,GROUP_CONCAT(permission_id) as permission_ids FROM roles LEFT JOIN roles_permission ON roles.id = roles_permission.role_id WHERE name='xxx' ORDER BY roles.id LIMIT 1

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: ivila

@r1005410078

打印的 sql 是可以正常运行的 ❌

打印的 sql 是可以在低版本的MySQL正常运行的 ✔

你的SQL里面有GROUP_CONCAT，但是却又没有GROUP BY子句，在高版本的MySQL中，MySQL严格按照标准实现了，所以你的非法SQL就报错了。

你可以看MySQL官方的文档解释：https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html

另外的是，GORM只是一个SQL Client，有关SQL Server的错误其实不应该报在这里。

Comment From: github-actions[bot]