Golang实现MySQL数据集记录移动功能

在Go语言中，标准库database/sql没有直接提供类似Delphi/Visual Basic中数据集（DataSet）的BOF、EOF、First、Next等游标操作功能。但可以通过以下方式实现类似功能：

方案一：使用切片缓存数据实现游标操作

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

type DataSet struct {
    records []map[string]interface{}
    current int
}

func (ds *DataSet) BOF() bool {
    return ds.current < 0
}

func (ds *DataSet) EOF() bool {
    return ds.current >= len(ds.records)
}

func (ds *DataSet) First() {
    if len(ds.records) > 0 {
        ds.current = 0
    } else {
        ds.current = -1
    }
}

func (ds *DataSet) Next() bool {
    ds.current++
    return !ds.EOF()
}

func (ds *DataSet) Last() {
    ds.current = len(ds.records) - 1
}

func (ds *DataSet) Skip(n int) {
    ds.current += n
    if ds.current < -1 {
        ds.current = -1
    }
}

func (ds *DataSet) FieldByName(name string) interface{} {
    if ds.current >= 0 && ds.current < len(ds.records) {
        return ds.records[ds.current][name]
    }
    return nil
}

func (ds *DataSet) Locate(fieldName string, value interface{}) bool {
    for i, record := range ds.records {
        if record[fieldName] == value {
            ds.current = i
            return true
        }
    }
    return false
}

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // 查询数据
    rows, err := db.Query("SELECT id, name, age FROM users")
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    // 创建数据集
    ds := &DataSet{current: -1}
    
    // 读取所有记录到内存
    columns, _ := rows.Columns()
    for rows.Next() {
        values := make([]interface{}, len(columns))
        valuePtrs := make([]interface{}, len(columns))
        for i := range columns {
            valuePtrs[i] = &values[i]
        }
        
        rows.Scan(valuePtrs...)
        
        record := make(map[string]interface{})
        for i, col := range columns {
            val := values[i]
            b, ok := val.([]byte)
            if ok {
                record[col] = string(b)
            } else {
                record[col] = val
            }
        }
        ds.records = append(ds.records, record)
    }

    // 使用数据集操作
    ds.First()
    for !ds.EOF() {
        fmt.Printf("ID: %v, Name: %v\n", 
            ds.FieldByName("id"), 
            ds.FieldByName("name"))
        ds.Next()
    }

    // 查找记录
    if ds.Locate("name", "John") {
        fmt.Printf("Found: Age = %v\n", ds.FieldByName("age"))
    }
}

方案二：使用第三方库sqlx增强功能

package main

import (
    "fmt"
    "github.com/jmoiron/sqlx"
    _ "github.com/go-sql-driver/mysql"
)

type User struct {
    ID   int    `db:"id"`
    Name string `db:"name"`
    Age  int    `db:"age"`
}

type DataSet struct {
    users   []User
    current int
}

// 实现与方案一相同的方法...

func main() {
    db := sqlx.MustConnect("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    
    var users []User
    err := db.Select(&users, "SELECT * FROM users")
    if err != nil {
        panic(err)
    }

    ds := &DataSet{
        users:   users,
        current: -1,
    }

    // 遍历记录
    ds.First()
    for !ds.EOF() {
        currentUser := ds.users[ds.current]
        fmt.Printf("User: %+v\n", currentUser)
        ds.Next()
    }
}

方案三：使用database/sql.Rows直接操作

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

type RowScanner struct {
    rows *sql.Rows
    current map[string]interface{}
    hasNext bool
}

func NewRowScanner(rows *sql.Rows) *RowScanner {
    return &RowScanner{
        rows: rows,
        hasNext: rows.Next(),
    }
}

func (rs *RowScanner) EOF() bool {
    return !rs.hasNext
}

func (rs *RowScanner) Next() bool {
    if !rs.hasNext {
        return false
    }
    
    columns, _ := rs.rows.Columns()
    values := make([]interface{}, len(columns))
    valuePtrs := make([]interface{}, len(columns))
    
    for i := range columns {
        valuePtrs[i] = &values[i]
    }
    
    rs.rows.Scan(valuePtrs...)
    
    rs.current = make(map[string]interface{})
    for i, col := range columns {
        val := values[i]
        b, ok := val.([]byte)
        if ok {
            rs.current[col] = string(b)
        } else {
            rs.current[col] = val
        }
    }
    
    rs.hasNext = rs.rows.Next()
    return true
}

func (rs *RowScanner) Field(name string) interface{} {
    return rs.current[name]
}

func main() {
    db, _ := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    
    rows, _ := db.Query("SELECT * FROM users")
    defer rows.Close()
    
    scanner := NewRowScanner(rows)
    for !scanner.EOF() {
        scanner.Next()
        fmt.Printf("Name: %v, Age: %v\n", 
            scanner.Field("name"), 
            scanner.Field("age"))
    }
}

这些实现方式提供了类似传统数据集的功能，但需要注意Go语言通常更倾向于使用迭代器模式（如rows.Next()）而不是游标模式来处理数据库记录。