Go语言中关系型数据库访问的最佳实践与性能优化

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql" // 导入MySQL驱动
    "log"
)

type User struct {
    ID   int
    Name string
    Email string
}

func main() {
    // 连接数据库
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/database_name")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 验证连接
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Successfully connected to MySQL!")

    // 插入数据（使用预处理语句）
    stmt, err := db.Prepare("INSERT INTO users(name, email) VALUES(?, ?)")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()

    _, err = stmt.Exec("Alice", "alice@example.com")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("User Alice inserted.")

    // 查询数据（使用预处理语句）
    rows, err := db.Query("SELECT id, name, email FROM users WHERE id > ?", 0)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var u User
        if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
            log.Fatal(err)
        }
        users = append(users, u)
    }

    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }

    fmt.Println("Users found:", users)
}

package main

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

// User 定义用户结构体
type User struct {
    ID    int    `json:"id"`
    Name  string `json:"name"`
    Email string `json:"email"`
}

// UserDS (User DataStore) 定义用户数据存储接口
type UserDS interface {
    CreateUser(user User) (int, error)
    GetUserByID(id int) (*User, error)
    GetAllUsers() ([]User, error)
    UpdateUser(user User) error
    DeleteUser(id int) error
}

// MySQLUserDB 实现UserDS接口，使用MySQL作为底层存储
type MySQLUserDB struct {
    db *sql.DB
}

// NewMySQLUserDB 创建一个新的MySQLUserDB实例
func NewMySQLUserDB(db *sql.DB) *MySQLUserDB {
    return &MySQLUserDB{db: db}
}

// CreateUser 实现UserDS接口的CreateUser方法
func (m *MySQLUserDB) CreateUser(user User) (int, error) {
    stmt, err := m.db.Prepare("INSERT INTO users(name, email) VALUES(?, ?)")
    if err != nil {
        return 0, fmt.Errorf("prepare statement failed: %w", err)
    }
    defer stmt.Close()

    res, err := stmt.Exec(user.Name, user.Email)
    if err != nil {
        return 0, fmt.Errorf("execute statement failed: %w", err)
    }

    id, err := res.LastInsertId()
    if err != nil {
        return 0, fmt.Errorf("get last insert ID failed: %w", err)
    }
    return int(id), nil
}

// GetUserByID 实现UserDS接口的GetUserByID方法
func (m *MySQLUserDB) GetUserByID(id int) (*User, error) {
    row := m.db.QueryRow("SELECT id, name, email FROM users WHERE id = ?", id)
    var user User
    err := row.Scan(&user.ID, &user.Name, &user.Email)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return nil, nil // 用户不存在
        }
        return nil, fmt.Errorf("scan row failed: %w", err)
    }
    return &user, nil
}

// GetAllUsers 实现UserDS接口的GetAllUsers方法
func (m *MySQLUserDB) GetAllUsers() ([]User, error) {
    rows, err := m.db.Query("SELECT id, name, email FROM users")
    if err != nil {
        return nil, fmt.Errorf("query all users failed: %w", err)
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email); err != nil {
            return nil, fmt.Errorf("scan user row failed: %w", err)
        }
        users = append(users, user)
    }
    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("rows iteration error: %w", err)
    }
    return users, nil
}

// UpdateUser 实现UserDS接口的UpdateUser方法
func (m *MySQLUserDB) UpdateUser(user User) error {
    stmt, err := m.db.Prepare("UPDATE users SET name = ?, email = ? WHERE id = ?")
    if err != nil {
        return fmt.Errorf("prepare update statement failed: %w", err)
    }
    defer stmt.Close()

    _, err = stmt.Exec(user.Name, user.Email, user.ID)
    if err != nil {
        return fmt.Errorf("execute update statement failed: %w", err)
    }
    return nil
}

// DeleteUser 实现UserDS接口的DeleteUser方法
func (m *MySQLUserDB) DeleteUser(id int) error {
    stmt, err := m.db.Prepare("DELETE FROM users WHERE id = ?")
    if err != nil {
        return fmt.Errorf("prepare delete statement failed: %w", err)
    }
    defer stmt.Close()

    _, err = stmt.Exec(id)
    if err != nil {
        return fmt.Errorf("execute delete statement failed: %w", err)
    }
    return nil
}

// 应用程序服务层，依赖UserDS接口
type UserService struct {
    userStore UserDS
}

func NewUserService(store UserDS) *UserService {
    return &UserService{userStore: store}
}

func (s *UserService) RegisterUser(name, email string) (int, error) {
    user := User{Name: name, Email: email}
    return s.userStore.CreateUser(user)
}

// ... 其他业务方法

func main() {
    // 假设db已经初始化并连接成功
    db, err := sql.Open("mysql", "root:password@tcp(127.0.0.1:3306)/testdb")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Connected to database.")

    // 初始化数据存储实现
    userDB := NewMySQLUserDB(db)
    // 初始化服务层，注入数据存储接口
    userService := NewUserService(userDB)

    // 使用服务层进行操作
    userID, err := userService.RegisterUser("Bob", "bob@example.com")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Registered user with ID: %d\n", userID)

    retrievedUser, err := userService.userStore.GetUserByID(userID)
    if err != nil {
        log.Fatal(err)
    }
    if retrievedUser != nil {
        fmt.Printf("Retrieved user: %+v\n", *retrievedUser)
    }

    // 进一步的测试和操作...
}