4.3 数据库操作

database/sql 基础

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

// 连接数据库
db, _ := sql.Open("mysql", "user:pass@tcp(localhost:3306)/dbname")
defer db.Close()

// 验证连接
db.Ping()

// 连接池配置
db.SetMaxIdleConns(10)
db.SetMaxOpenConns(100)
db.SetConnMaxLifetime(time.Hour)

---

增删改查

// 插入
result, _ := db.Exec(
    "INSERT INTO users (name, email) VALUES (?, ?)",
    "Alice", "alice@example.com",
)
id, _ := result.LastInsertId()

// 查询单行
var user User
db.QueryRow(
    "SELECT id, name, email FROM users WHERE id = ?",
    id,
).Scan(&user.ID, &user.Name, &user.Email)

// 查询多行
rows, _ := db.Query("SELECT * FROM users")
defer rows.Close()
for rows.Next() {
    var user User
    rows.Scan(&user.ID, &user.Name, &user.Email)
}

// 更新
db.Exec("UPDATE users SET email = ? WHERE id = ?", email, id)

// 删除
db.Exec("DELETE FROM users WHERE id = ?", id)

---

事务处理

func transferMoney(db *sql.DB, fromID, toID int, amount float64) error {
    tx, _ := db.Begin()
    defer tx.Rollback()

    _, err := tx.Exec(
        "UPDATE accounts SET balance = balance - ? WHERE id = ?",
        amount, fromID,
    )
    if err != nil {
        return err
    }

    _, err = tx.Exec(
        "UPDATE accounts SET balance = balance + ? WHERE id = ?",
        amount, toID,
    )
    if err != nil {
        return err
    }

    return tx.Commit()
}

// 事务选项
tx, _ := db.BeginTx(ctx, &sql.TxOptions{
    Isolation: sql.LevelReadCommitted,
    ReadOnly:  false,
})

---

ORM 简介

// GORM 示例
import "gorm.io/gorm"

type User struct {
    gorm.Model
    Name  string
    Email string `gorm:"uniqueIndex"`
}

// 连接
db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{})

// 自动迁移
db.AutoMigrate(&User{})

// CRUD
db.Create(&User{Name: "Alice"})
db.First(&user, 1)
db.Where("name = ?", "Alice").First(&user)
db.Find(&users)
db.Model(&user).Update("Name", "Bob")
db.Delete(&user)

// 预加载
db.Preload("Posts").First(&user)