Go语言与MySQL存储过程：解决Error 1312结果集返回限制

DELIMITER $$

USE `MobiFit_Dev`$$

DROP PROCEDURE IF EXISTS `User_ByEmail`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `User_ByEmail`(pEmail VARCHAR(250))
BEGIN
    SELECT 
        * 
    FROM
        `User` 
    WHERE `Email` = pEmail ;
END$$

DELIMITER ;

package entities

import (
    "database/sql"
    "fmt"
    "github.com/go-sql-driver/mysql"
    // "mobifit/db" // 假设 db 包提供了数据库连接
)

// User 结构体定义，用于映射数据库表字段
type User struct {
    Id             sql.NullInt64
    Email          sql.NullString
    HashedPassword sql.NullString
    RoleId         sql.NullInt64
    FirstName      sql.NullString
    LastName       sql.NullString
    Gender         sql.NullString
    DateOfBirth    mysql.NullTime
    Height         sql.NullFloat64
    CurrentWeight  sql.NullFloat64
    CreatedAt      mysql.NullTime
    ConfirmedAt    mysql.NullTime
    LastActivityAt mysql.NullTime
    DeletedAt      mysql.NullTime
}

// UserByEmail 函数尝试通过存储过程获取用户信息
func UserByEmail(db *sql.DB, email string) *User { // 假设 db 连接作为参数传入
    u := new(User)
    // 这一行代码会引发 panic: Error 1312
    rows, err := db.Query("CALL User_ByEmail(?)", email) 

    if err != nil {
        panic(err)
    }
    // fmt.Println(rows.Columns()) // 这一行可能在 panic 之后无法执行

    for rows.Next() {
        if err := rows.Scan(
            &u.Id,
            &u.Email,
            &u.HashedPassword,
            &u.RoleId,
            &u.FirstName,
            &u.LastName,
            &u.Gender,
            &u.DateOfBirth,
            &u.Height,
            &u.CurrentWeight,
            &u.CreatedAt,
            &u.ConfirmedAt,
            &u.LastActivityAt,
            &u.DeletedAt); err != nil {
            panic(err)
        }
    }

    if err := rows.Err(); err != nil {
        panic(err)
    }
    fmt.Println(u)
    return u
}

// UserByEmail 函数直接执行 SELECT 查询
func UserByEmailDirectQuery(db *sql.DB, email string) *User {
    u := new(User)
    rows, err := db.Query("SELECT Id, Email, HashedPassword, RoleId, FirstName, LastName, Gender, DateOfBirth, Height, CurrentWeight, CreatedAt, ConfirmedAt, LastActivityAt, DeletedAt FROM User WHERE Email = ?", email)

    if err != nil {
        panic(err)
    }
    defer rows.Close() // 确保关闭 rows

    if rows.Next() { // 只期望一条结果
        if err := rows.Scan(
            &u.Id,
            &u.Email,
            &u.HashedPassword,
            &u.RoleId,
            &u.FirstName,
            &u.LastName,
            &u.Gender,
            &u.DateOfBirth,
            &u.Height,
            &u.CurrentWeight,
            &u.CreatedAt,
            &u.ConfirmedAt,
            &u.LastActivityAt,
            &u.DeletedAt); err != nil {
            panic(err)
        }
    } else if err := rows.Err(); err != nil { // 检查是否有迭代错误
        panic(err)
    } else {
        // 没有找到记录，u 仍然是其零值，或者可以返回 nil
        return nil 
    }

    fmt.Println(u)
    return u
}

// 假设有一个存储过程用于更新用户信息，不返回结果
// CREATE PROCEDURE UpdateUser(pId INT, pEmail VARCHAR(250))
// BEGIN UPDATE User SET Email = pEmail WHERE Id = pId; END$$

func UpdateUserEmail(db *sql.DB, userId int, newEmail string) (sql.Result, error) {
    result, err := db.Exec("CALL UpdateUser(?, ?)", userId, newEmail)
    if err != nil {
        return nil, fmt.Errorf("failed to call UpdateUser stored procedure: %w", err)
    }
    return result, nil
}