使用Gorm映射原生查询结果到DTO时的查询执行异常问题

定义的Gorm模型 #

type User struct {
    gorm.Model
    Username string
    Password string
}

type Assistant struct {
    gorm.Model
    UserID        int
    User          User
    InstitutionID int
}

type Professor struct {
    gorm.Model
    UserID        int
    User          User
    InstitutionID int
}

两种原生查询实现 #

第一种实现 #

var users *[]model.User
db.Raw("SELECT u.id, u.username "+
    "FROM users u "+
    "LEFT JOIN assistants ass ON ass.user_id = u.id "+
    "LEFT JOIN professors prof ON prof.user_id = u.id "+
    "WHERE (u.username = @username1 AND ass.institution_id = @ins1)"+
    "      or                                           "+
    "      (u.username = @username2 AND prof.institution_id = @ins2)",
    map[string]interface{}{"username1": dto.Username, "ins1": dto.InstitutionID, "username2": dto.Username, "ins2": dto.InstitutionID},
).Find(&users)

第二种实现 #

var users *[]model.User
db.Raw("SELECT u.id, u.username "+
        "FROM users u "+
        "LEFT JOIN assistants ass ON ass.user_id = u.id "+
        "LEFT JOIN professors prof ON prof.user_id = u.id "+
        "WHERE ( u.username = ? AND ass.institution_id = ? )"+
        "      or                                           "+
        "      (u.username = ? AND prof.institution_id = ?) RETURNING id, username", dto.Username, dto.InstitutionID, dto.Username, dto.InstitutionID).Scan(&users)

控制台错误日志 #

goroutine 20 [running]:
        C:/Program Files/Go/src/net/http/server.go:2122 +0x2f
github.com/gorilla/mux.(*Router).ServeHTTP(0xc000000480, {0xf09690, 0xc0001501c0}, 0xc000212000)
        C:/Users/Miljan.Puletic/go/pkg/mod/github.com/gorilla/mux@v1.8.0/mux.go:210 +0x1cf
        C:/Program Files/Go/src/net/http/server.go:2122 +0x2f
github.com/gorilla/mux.(*Router).ServeHTTP(0xc000000480, {0xf09690, 0xc0001501c0}, 0xc000212000)
        C:/Users/User/go/pkg/mod/github.com/gorilla/mux@v1.8.0/mux.go:210 +0x1cf
net/http.serverHandler.ServeHTTP({0xf08698?}, {0xf09690, 0xc0001501c0}, 0xc000212000)
        C:/Program Files/Go/src/net/http/server.go:2936 +0x316
net/http.(*conn).serve(0xc000408ab0, {0xf09bf8, 0xc000422030})
        C:/Program Files/Go/src/net/http/server.go:1995 +0x612
created by net/http.(*Server).Serve
        C:/Program Files/Go/src/net/http/server.go:3089 +0x5ed

排查修复要点 #

1. MySQL不支持RETURNING子句 #

第二个查询中添加的RETURNING id, username是PostgreSQL专属语法，MySQL无此特性，这是查询失败的直接原因，删除该子句即可。

2. 切片变量声明错误 #

代码中var users *[]model.User声明的是指针切片，Gorm的Scan/Find方法需要接收值切片的指针，正确声明应为var users []model.User，然后传递&users给方法。未初始化的指针切片会引发空指针panic，对应日志中的goroutine报错。

3. 命名参数格式错误 #

第一种实现中使用@username1格式的命名参数，Gorm对MySQL的命名参数支持是:前缀（如:username1）而非@，改用该格式或直接使用?占位符即可正常绑定参数。

4. 查询逻辑优化（可选） #

原查询用LEFT JOIN可能返回重复数据，改用EXISTS子查询可提升性能并避免重复结果，优化后的SQL如下：

SELECT u.id, u.username 
FROM users u 
WHERE (u.username = ? AND EXISTS (SELECT 1 FROM assistants ass WHERE ass.user_id = u.id AND ass.institution_id = ?))
OR (u.username = ? AND EXISTS (SELECT 1 FROM professors prof WHERE prof.user_id = u.id AND prof.institution_id = ?))

修复后的代码示例 #

var users []model.User
err := db.Raw(`
    SELECT u.id, u.username 
    FROM users u 
    WHERE (u.username = ? AND EXISTS (SELECT 1 FROM assistants ass WHERE ass.user_id = u.id AND ass.institution_id = ?))
    OR (u.username = ? AND EXISTS (SELECT 1 FROM professors prof WHERE prof.user_id = u.id AND prof.institution_id = ?))
`, dto.Username, dto.InstitutionID, dto.Username, dto.InstitutionID).Scan(&users).Error

if err != nil {
    log.Printf("查询用户失败: %v", err)
    // 此处添加HTTP错误处理逻辑
    return
}

额外调试建议 #

开启Gorm详细日志，可查看实际执行的SQL语句和错误详情，初始化DB时配置：

db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    Logger: logger.Default.LogMode(logger.Info),
})

内容的提问来源于stack exchange，提问作者Wrapper