← Redis | Gateway →

MySQL - Go MySQL 客户端

MySQL 是最流行的关系型数据库之一。Go 通过 database/sql 标准库和 go-sql-driver/mysql 驱动支持 MySQL 操作。

连接数据库

📝 建立连接

package main

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

func main() {
    // DSN 格式
    dsn := "user:password@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"
    
    // 打开连接
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()
    
    // 测试连接
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }
    
    fmt.Println("Connected to MySQL")
    
    // 连接池配置
    db.SetMaxIdleConns(10)
    db.SetMaxOpenConns(100)
    db.SetConnMaxLifetime(time.Hour)
}

CRUD 操作

📝 增删改查

type User struct {
    ID    int64
    Name  string
    Email string
    Age   int
}

// 创建
func CreateUser(db *sql.DB, user *User) (int64, error) {
    result, err := db.Exec(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
        user.Name, user.Email, user.Age,
    )
    if err != nil {
        return 0, err
    }
    return result.LastInsertId()
}

// 查询单条
func GetUser(db *sql.DB, id int64) (*User, error) {
    var user User
    err := db.QueryRow(
        "SELECT id, name, email, age FROM users WHERE id = ?",
        id,
    ).Scan(&user.ID, &user.Name, &user.Email, &user.Age)
    
    if err == sql.ErrNoRows {
        return nil, nil
    }
    if err != nil {
        return nil, err
    }
    return &user, nil
}

// 查询多条
func GetAllUsers(db *sql.DB) ([]*User, error) {
    rows, err := db.Query("SELECT id, name, email, age FROM users")
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    
    var users []*User
    for rows.Next() {
        var user User
        if err := rows.Scan(&user.ID, &user.Name, &user.Email, &user.Age); err != nil {
            return nil, err
        }
        users = append(users, &user)
    }
    
    return users, rows.Err()
}

// 更新
func UpdateUser(db *sql.DB, user *User) error {
    _, err := db.Exec(
        "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?",
        user.Name, user.Email, user.Age, user.ID,
    )
    return err
}

// 删除
func DeleteUser(db *sql.DB, id int64) error {
    _, err := db.Exec("DELETE FROM users WHERE id = ?", id)
    return err
}

事务处理

📝 事务操作

func TransferMoney(db *sql.DB, from, to int64, amount float64) error {
    // 开启事务
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    
    // 确保提交或回滚
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            tx.Rollback()
        }
    }()
    
    // 扣款
    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?",
        amount, from,
    )
    if err != nil {
        return err
    }
    
    // 收款
    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?",
        amount, to,
    )
    if err != nil {
        return err
    }
    
    // 提交事务
    return tx.Commit()
}

预编译语句

📝 Prepared Statement

// 创建预编译语句
stmt, err := db.Prepare("SELECT id, name, email FROM users WHERE id = ?")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()

// 使用预编译语句
var user User
err = stmt.QueryRow(1).Scan(&user.ID, &user.Name, &user.Email)

// 批量插入
func BatchInsert(db *sql.DB, users []User) error {
    tx, _ := db.Begin()
    stmt, _ := tx.Prepare("INSERT INTO users (name, email) VALUES (?, ?)")
    defer stmt.Close()
    
    for _, user := range users {
        _, err := stmt.Exec(user.Name, user.Email)
        if err != nil {
            tx.Rollback()
            return err
        }
    }
    
    return tx.Commit()
}

📖 延伸阅读