Go SQL操作中自定义[]byte类型扫描陷阱与解决方案

type Votes []byte

func Vote(_type, did int, username string) (isSucceed bool) {
    db := lib.OpenDb()
    defer db.Close()

    // 1. 查询 votes
    stmt, err := db.Prepare(`SELECT votes FROM users WHERE username = ?`)
    lib.CheckErr(err)
    res := stmt.QueryRow(username)
    stmt.Close()

    var votes Votes
    res.Scan(&votes) // 问题发生在这里
    fmt.Println("Original votes:", votes, string(votes)) // 例如: [48 48 48 48] 0000

    // 2. 修改 votes
    // votes.add(_type, 1) // 假设 add 方法会修改 votes 的内容
    // fmt.Println("Modified votes:", votes, string(votes)) // 例如: [49 48 48 48] 1000

    // 3. 准备更新语句时，votes 的值意外变化
    stmt, err = db.Prepare(`UPDATE users SET votes = ? WHERE username = ?`)
    lib.CheckErr(err)
    fmt.Println("Votes before Exec:", votes, string(votes)) // 此时 votes 可能会变成 [4 254 0 0] [EOT]□[NUL][NUL]
    _, _ = stmt.Exec(votes, username)
    stmt.Close()

    // ... 后续操作
    return
}

package main

import "fmt"

type BYTES []byte

func test(v interface{}) {
    // 尝试将 v 断言为 *[]byte
    b, ok := v.(*[]byte)
    fmt.Println("Is *[]byte?", b, ok)
}

func main() {
    p := BYTES("hello")
    fmt.Println("Calling test with &p (type *BYTES):")
    test(&p) // 输出: Is *[]byte? <nil> false

    fmt.Println("\nCalling test with (*[]byte)(&p) (type *[]byte):")
    test((*[]byte)(&p)) // 输出: Is *[]byte? &[104 101 108 108 111] true
}

res.Scan(&votes)

res.Scan((*[]byte)(&votes))

package main

import (
    "fmt"
    "time"
    // "github.com/Go-SQL-Driver/MySQL" // 假设已导入
    // "your_project/lib" // 假设 lib 包含 OpenDb 和 CheckErr
)

// 假设 Votes 和 VoteType 定义如下
type Votes []byte
type VoteType int

// 假设 VOTE_MAX 定义
const VOTE_MAX byte = 57 // ASCII for '9'

func (this *Votes) add(_type VoteType, num int) (isSucceed bool) {
    if int(_type) >= len(*this) {
        // 处理索引越界情况
        return false
    }
    if (*this)[_type] > VOTE_MAX-1 { // beyond
        isSucceed = false
    } else {
        (*this)[_type]++
        isSucceed = true
    }
    return
}

// 模拟 lib 包的函数
type MockDB struct{}
func (m *MockDB) Prepare(query string) (*MockStmt, error) { return &MockStmt{query: query}, nil }
func (m *MockDB) Close() error { return nil }

type MockStmt struct { query string }
func (s *MockStmt) QueryRow(args ...interface{}) *MockRow {
    // 模拟查询结果
    if s.query == `SELECT votes FROM users WHERE username = ?` {
        return &MockRow{data: []byte("0000")}
    }
    return &MockRow{data: nil}
}
func (s *MockStmt) Exec(args ...interface{}) (interface{}, error) {
    // 模拟执行
    fmt.Printf("Executing query: %s with args: %v\n", s.query, args)
    return nil, nil
}
func (s *MockStmt) Close() error { return nil }

type MockRow struct { data []byte }
func (r *MockRow) Scan(dest ...interface{}) error {
    if len(dest) == 1 {
        if b, ok := dest[0].(*[]byte); ok {
            *b = r.data // 正确填充
            return nil
        }
    }
    return fmt.Errorf("scan failed: unsupported type or multiple destinations")
}

// 模拟 lib.OpenDb 和 lib.CheckErr
func OpenDb() *MockDB { return &MockDB{} }
func CheckErr(err error) { if err != nil { panic(err) } }


func VoteCorrected(_type, did int, username string) (isSucceed bool) {
    db := OpenDb() // 使用模拟 DB
    defer db.Close()

    // 1. 查询 votes
    stmt, err := db.Prepare(`SELECT votes FROM users WHERE username = ?`)
    CheckErr(err)
    res := stmt.QueryRow(username)
    stmt.Close()

    var votes Votes
    // 核心修改：显式类型转换
    err = res.Scan((*[]byte)(&votes))
    CheckErr(err)
    fmt.Println("Original votes (after scan):", votes, string(votes)) // 预期: [48 48 48 48] 0000

    // 2. 修改 votes
    isSucceed = votes.add(VoteType(_type), 1)
    fmt.Println("Modified votes:", votes, string(votes)) // 预期: [49 48 48 48] 1000

    if isSucceed {
        // 3. 更新用户 votes
        stmt, err := db.Prepare(`UPDATE users SET votes = ? WHERE username = ?`)
        CheckErr(err)

        fmt.Println("Votes before Exec (should be correct):", votes, string(votes)) // 预期: [49 48 48 48] 1000
        _, _ = stmt.Exec(votes, username) // 此时 votes 的值是正确的
        stmt.Close()

        // 4. 插入投票数据
        stmt, err = db.Prepare(`INSERT votes SET did = ?, username = ?, date = ?`)
        CheckErr(err)

        today := time.Now()
        _, _ = stmt.Exec(did, username, today)
        stmt.Close()
    }

    return
}

func main() {
    VoteCorrected(0, 123, "testuser")
}