原生 SQL 操作与 GORM 对比

# MySQL 驱动
go get github.com/go-sql-driver/mysql

# PostgreSQL 驱动（推荐 pgx，也可用 lib/pq）
go get github.com/jackc/pgx/v5
go get github.com/jackc/pgx/v5/stdlib  # database/sql 适配器

# 或
go get github.com/lib/pq

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql" // 匿名导入，注册驱动
)

func main() {
    dsn := "user:pass@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)
    }

    // 连接池配置
    db.SetMaxOpenConns(100)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(30 * time.Minute)
    db.SetConnMaxIdleTime(5 * time.Minute)
}

import (
    "database/sql"
    _ "github.com/jackc/pgx/v5/stdlib"
)

func main() {
    dsn := "postgres://user:pass@localhost:5432/dbname?sslmode=disable"
    db, err := sql.Open("pgx", dsn)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }
}

type User struct {
    ID        int
    Name      string
    Age       int
    CreatedAt time.Time
}

var user User
row := db.QueryRow("SELECT id, name, age, created_at FROM users WHERE id = ?", 1)

err := row.Scan(&user.ID, &user.Name, &user.Age, &user.CreatedAt)
if err == sql.ErrNoRows {
    fmt.Println("用户不存在")
} else if err != nil {
    log.Fatal(err)
}

rows, err := db.Query("SELECT id, name, age FROM users WHERE age > ?", 18)
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.Age); err != nil {
        log.Fatal(err)
    }
    users = append(users, u)
}

// 检查遍历过程中是否有错误
if err := rows.Err(); err != nil {
    log.Fatal(err)
}

// database/sql 不支持直接扫描到结构体，需逐字段映射
rows, _ := db.Query("SELECT u.id, u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id")
defer rows.Close()

type UserStats struct {
    ID         int
    Name       string
    OrderCount int
}
var stats []UserStats
for rows.Next() {
    var s UserStats
    rows.Scan(&s.ID, &s.Name, &s.OrderCount)
    stats = append(stats, s)
}

// INSERT
result, err := db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", "张三", 18)
if err != nil {
    log.Fatal(err)
}
id, _ := result.LastInsertId()  // 自增 ID（MySQL 支持，PostgreSQL 不支持）
n, _ := result.RowsAffected()   // 影响行数

// UPDATE
result, err = db.Exec("UPDATE users SET age = ? WHERE id = ?", 20, 1)
n, _ = result.RowsAffected()

// DELETE
result, err = db.Exec("DELETE FROM users WHERE id = ?", 1)

// PostgreSQL — 用 RETURNING 获取插入 ID
var newID int
err = db.QueryRow(
    "INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id",
    "张三", 18,
).Scan(&newID)

// 手动拼接参数（数据库限制了参数数量）
func BatchInsert(db *sql.DB, users []User) error {
    if len(users) == 0 {
        return nil
    }

    // 构建批量 INSERT
    query := "INSERT INTO users (name, age) VALUES "
    args := make([]interface{}, 0, len(users)*2)

    for i, u := range users {
        if i > 0 {
            query += ", "
        }
        query += "(?, ?)"
        args = append(args, u.Name, u.Age)
    }

    _, err := db.Exec(query, args...)
    return err
}

// 开启事务
tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}

// 确保事务结束（Commit 或 Rollback）
defer func() {
    if r := recover(); r != nil {
        tx.Rollback()
        panic(r)
    }
}()

// 操作 1
result, err := tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", 100, 1)
if err != nil {
    tx.Rollback()
    return
}
if n, _ := result.RowsAffected(); n == 0 {
    tx.Rollback()
    return
}

// 操作 2
_, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", 100, 2)
if err != nil {
    tx.Rollback()
    return
}

// 提交
err = tx.Commit()
if err != nil {
    log.Fatal(err)
}

func TransferWithTimeout(ctx context.Context, db *sql.DB, fromID, toID int, amount float64) error {
    ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
    defer cancel()

    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelReadCommitted,
        ReadOnly:  false,
    })
    if err != nil {
        return err
    }
    defer tx.Rollback() // Commit 后 Rollback 是空操作

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

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

    return tx.Commit()
}

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

// 多次执行
rows1, _ := stmt.Query(18)
rows2, _ := stmt.Query(30)

// 预编译写入
insertStmt, err := db.Prepare("INSERT INTO users (name, age) VALUES (?, ?)")
defer insertStmt.Close()

for i := 0; i < 1000; i++ {
    insertStmt.Exec(fmt.Sprintf("user_%d", i), i%50)
}

import "database/sql"

type User struct {
    ID      int
    Name    string
    Email   sql.NullString  // 可能为 NULL
    Age     sql.NullInt64   // 可能为 NULL
    Bio     sql.NullString  // 可能为 NULL
}

var u User
row := db.QueryRow("SELECT id, name, email, age, bio FROM users WHERE id = ?", 1)
row.Scan(&u.ID, &u.Name, &u.Email, &u.Age, &u.Bio)

// 检查 NULL
if u.Email.Valid {
    fmt.Println(u.Email.String)
} else {
    fmt.Println("邮箱未设置")
}

// 指针方案：Go 中 nil 表示 NULL
type User struct {
    ID    int
    Name  string
    Email *string // nil → NULL
    Age   *int    // nil → NULL
}

// 在 SQL 中用默认值替代 NULL
row := db.QueryRow("SELECT id, name, COALESCE(email, '') as email, COALESCE(age, 0) as age FROM users WHERE id = ?", 1)
var u User // 用基础类型即可
row.Scan(&u.ID, &u.Name, &u.Email, &u.Age)

// ❌ 错误：每次打开新连接池
func handler(w http.ResponseWriter, r *http.Request) {
    db, _ := sql.Open("mysql", dsn)
    db.Query("SELECT ...") // 每次请求创建新连接池！泄漏！
}

// ✅ 正确：全局单例
var DB *sql.DB

func init() {
    DB, _ = sql.Open("mysql", dsn)
    DB.SetMaxOpenConns(100)
    DB.SetMaxIdleConns(10)
    DB.SetConnMaxLifetime(time.Hour)
}

func handler(w http.ResponseWriter, r *http.Request) {
    DB.QueryContext(r.Context(), "SELECT ...")
}

stats := db.Stats()
fmt.Printf(`Database Pool Stats:
  当前打开连接:  %d
  空闲连接:      %d
  使用中连接:    %d
  累计等待次数:  %d (高值表示池太小)
  等待时长合计:  %v
  因空闲关闭:    %d
  因超时关闭:    %d
`,
    stats.OpenConnections,
    stats.Idle,
    stats.InUse,
    stats.WaitCount,
    stats.WaitDuration,
    stats.MaxIdleClosed,
    stats.MaxLifetimeClosed,
)

import (
    "database/sql"
    "errors"
    "github.com/go-sql-driver/mysql" // MySQL 特定错误
    "github.com/jackc/pgx/v5/pgconn" // PostgreSQL 特定错误
)

// 通用
if errors.Is(err, sql.ErrNoRows) {
    // 无结果
}

// MySQL 特定错误
var mysqlErr *mysql.MySQLError
if errors.As(err, &mysqlErr) {
    switch mysqlErr.Number {
    case 1062:
        fmt.Println("主键/唯一键重复")
    case 1451:
        fmt.Println("外键约束冲突")
    case 1213:
        fmt.Println("死锁，需要重试")
    }
}

// PostgreSQL 特定错误
var pgErr *pgconn.PgError
if errors.As(err, &pgErr) {
    switch pgErr.Code {
    case "23505":
        fmt.Println("唯一约束冲突")
    case "23503":
        fmt.Println("外键约束冲突")
    case "40P01":
        fmt.Println("死锁")
    }
}

func ExecWithRetry(db *sql.DB, fn func(tx *sql.Tx) error, maxRetries int) error {
    for i := 0; i < maxRetries; i++ {
        tx, _ := db.Begin()
        err := fn(tx)
        if err == nil {
            return tx.Commit()
        }
        tx.Rollback()

        if !isDeadlockError(err) {
            return err // 非死锁错误直接返回
        }
        time.Sleep(time.Millisecond * time.Duration(10*(i+1))) // 退避策略
    }
    return errors.New("达到最大重试次数")
}

func isDeadlockError(err error) bool {
    var mysqlErr *mysql.MySQLError
    if errors.As(err, &mysqlErr) && mysqlErr.Number == 1213 {
        return true
    }
    var pgErr *pgconn.PgError
    if errors.As(err, &pgErr) && pgErr.Code == "40P01" {
        return true
    }
    return false
}

go get github.com/jmoiron/sqlx

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

// sqlx.DB 内嵌了 sql.DB，所有 database/sql 方法都能用
db, _ := sqlx.Connect("mysql", dsn)

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Age       int       `db:"age"`
    CreatedAt time.Time `db:"created_at"`
}

// StructScan — 自动映射到结构体
var user User
db.Get(&user, "SELECT id, name, age, created_at FROM users WHERE id = ?", 1)

// 多行映射
var users []User
db.Select(&users, "SELECT id, name, age FROM users WHERE age > ?", 18)

// 命名参数
sql := "SELECT * FROM users WHERE name = :name AND age > :age"
arg := map[string]interface{}{"name": "张三", "age": 18}
rows, _ := db.NamedQuery(sql, arg)

// IN 查询（自动展开切片）
ids := []int{1, 2, 3}
query, args, _ := sqlx.In("SELECT * FROM users WHERE id IN (?)", ids)
db.Select(&users, query, args...) // sqlx.In 会生成 3 个 ? 占位符

// 事务
tx := db.MustBegin()
tx.Exec("INSERT INTO users (name) VALUES (?)", "张三")
tx.Commit()

import sq "github.com/Masterminds/squirrel"

// MySQL 风格
users := sq.Select("*").From("users").Where(sq.Eq{"age": 18}).OrderBy("name DESC").Limit(10)
sql, args, _ := users.ToSql()
// SELECT * FROM users WHERE age = ? ORDER BY name DESC LIMIT 10

// PostgreSQL 风格
psql := sq.StatementBuilder.PlaceholderFormat(sq.Dollar)
sql, args, _ := psql.Select("*").From("users").Where(sq.Eq{"age": 18}).ToSql()
// SELECT * FROM users WHERE age = $1

import "github.com/huandu/go-sqlbuilder"

sql, args := sqlbuilder.NewSelectBuilder().
    Select("id", "name").
    From("users").
    Where(
        sqlbuilder.GreaterThan("age", 18),
        sqlbuilder.Equal("status", "active"),
    ).
    OrderBy("created_at DESC").
    Limit(10).
    Build()

// 查询单条 ≈ 12 行
row := db.QueryRow("SELECT id, name, age, email, phone, created_at FROM users WHERE id = ?", id)
var u User
err := row.Scan(&u.ID, &u.Name, &u.Age, &u.Email, &u.Phone, &u.CreatedAt)
if err != nil { ... }

// 查询全部 ≈ 15 行
rows, _ := db.Query("SELECT id, name, age FROM users WHERE age > ?", 18)
defer rows.Close()
var users []User
for rows.Next() {
    var u User
    rows.Scan(&u.ID, &u.Name, &u.Age)
    users = append(users, u)
}
rows.Err()

// 更新 ≈ 5 行
db.Exec("UPDATE users SET name = ?, age = ? WHERE id = ?", name, age, id)

// 插入 ≈ 5 行
result, _ := db.Exec("INSERT INTO users (name, age) VALUES (?, ?)", name, age)
id, _ := result.LastInsertId()

// 事务 ≈ 18 行
tx, _ := db.Begin()
defer tx.Rollback()
tx.Exec(...)
tx.Exec(...)
tx.Commit()

// 查询单条 ≈ 2 行
db.First(&user, id)

// 查询全部 ≈ 2 行
db.Where("age > ?", 18).Find(&users)

// 更新 ≈ 2 行
db.Model(&user).Updates(User{Name: name, Age: age})

// 插入 ≈ 1 行
db.Create(&user) // 自动回填 ID

// 事务 ≈ 5 行
db.Transaction(func(tx *gorm.DB) error {
    tx.Create(...)
    tx.Create(...)
    return nil
})

// 1. 全局使用 GORM
var DB *gorm.DB

// 2. 简单 CRUD：用 GORM
func GetUser(id uint) (*User, error) {
    var user User
    err := DB.First(&user, id).Error
    return &user, err
}

func ListActiveUsers(page, size int) ([]User, int64, error) {
    var users []User
    var total int64
    DB.Model(&User{}).Where("status = ?", "active").Count(&total)
    DB.Where("status = ?", "active").Scopes(Paginate(page, size)).Find(&users)
    return users, total, nil
}

// 3. 复杂统计查询：用 Raw SQL
type SalesReport struct {
    Date   string  `json:"date"`
    Total  float64 `json:"total"`
    Orders int     `json:"orders"`
}

func GetSalesReport(start, end time.Time) ([]SalesReport, error) {
    var reports []SalesReport
    err := DB.Raw(`
        SELECT
            DATE(created_at) as date,
            SUM(amount) as total,
            COUNT(*) as orders
        FROM orders
        WHERE created_at BETWEEN ? AND ?
        GROUP BY DATE(created_at)
        ORDER BY date
    `, start, end).Scan(&reports).Error
    return reports, err
}

// 4. 高性能写入：使用 Exec（跳过 GORM 的反射和回调）
func BatchUpdateStatus(userIDs []uint, status string) error {
    return DB.Exec(
        "UPDATE users SET status = ? WHERE id IN (?)",
        status, userIDs,
    ).Error
}

// 5. 复杂事务 + 悲观锁：混合使用
func TransferMoney(fromID, toID uint, amount float64) error {
    return DB.Transaction(func(tx *gorm.DB) error {
        // 用 Raw SQL 做行锁（GORM 的 Clauses(Locking) 也可）
        var balance float64
        row := tx.Raw(
            "SELECT balance FROM accounts WHERE id = ? FOR UPDATE",
            fromID,
        ).Row()
        row.Scan(&balance)

        if balance < amount {
            return errors.New("余额不足")
        }

        tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID)
        tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID)
        return nil
    })
}

// GORM 提供 *sql.DB 访问
sqlDB, _ := DB.DB()

// 直接用 sqlx 封装（共享连接池）
sqlxDB := sqlx.NewDb(sqlDB, "mysql")

// 复杂查询用 sqlx 的结构体映射
type ComplexResult struct {
    UserName  string `db:"user_name"`
    OrderCnt  int    `db:"order_cnt"`
    TotalAmt  float64 `db:"total_amt"`
}
var results []ComplexResult
sqlxDB.Select(&results, `
    SELECT u.name as user_name, COUNT(o.id) as order_cnt, SUM(o.amount) as total_amt
    FROM users u
    LEFT JOIN orders o ON o.user_id = u.id
    WHERE u.created_at > ?
    GROUP BY u.id
    HAVING order_cnt > 5
`, startDate)