Go语言结构体性能优化与数据库操作最佳实践

type A struct {
    I int
    S string
}

var MyA A // MyA.I 初始化为 0，MyA.S 初始化为 ""

// 改进后的 Person 结构体和数据库操作函数签名
type Card struct {
    Number string
    Type   string
}

type Person struct {
    Name  string
    Cards []Card
}

// PersistToDatabase 接收一个 Person 实例作为参数
func PersistToDatabase(person Person) error {
    // ... 数据库操作逻辑 ...
    return nil
}

func handleRequest(w http.ResponseWriter, r *http.Request) {
    var p Person // 每个请求都创建自己的 Person 实例
    if err := json.NewDecoder(r.Body).Decode(&p); err != nil {
        http.Error(w, err.Error(), http.StatusBadRequest)
        return
    }

    if err := PersistToDatabase(p); err != nil {
        http.Error(w, "Failed to persist data", http.StatusInternalServerError)
        return
    }

    w.WriteHeader(http.StatusOK)
}

func PersistToDatabase(person Person) error {
    tx, err := db.Begin()
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }
    // 确保在函数退出时回滚事务，除非已成功提交
    defer func() {
        if r := recover(); r != nil { // 捕获panic，确保回滚
            _ = tx.Rollback()
            panic(r)
        } else if err != nil { // 如果有错误，回滚
            _ = tx.Rollback()
        }
    }()

    // 使用预处理语句
    stmt1, err := tx.Prepare(`insert into tb1(name) values(?)`)
    if err != nil {
        return fmt.Errorf("failed to prepare stmt1: %w", err)
    }
    defer stmt1.Close() // 确保语句关闭

    stmt2, err := tx.Prepare(`insert into tb2(name, num, type) values(?, ?, ?)`)
    if err != nil {
        return fmt.Errorf("failed to prepare stmt2: %w", err)
    }
    defer stmt2.Close() // 确保语句关闭

    // 执行插入操作
    if _, err = stmt1.Exec(person.Name); err != nil {
        return fmt.Errorf("failed to exec stmt1: %w", err)
    }

    for _, x := range person.Cards {
        if _, err = stmt2.Exec(person.Name, x.Number, x.Type); err != nil {
            return fmt.Errorf("failed to exec stmt2 for card %s: %w", x.Number, err)
        }
    }

    // 所有操作成功，提交事务
    return tx.Commit()
}

// 假设 dbManager 是一个管理数据库连接和预处理语句的结构体
type DBManager struct {
    db    *sql.DB
    stmt1 *sql.Stmt
    stmt2 *sql.Stmt
}

func NewDBManager(db *sql.DB) (*DBManager, error) {
    stmt1, err := db.Prepare(`insert into tb1(name) values(?)`)
    if err != nil {
        return nil, fmt.Errorf("failed to prepare stmt1: %w", err)
    }
    stmt2, err := db.Prepare(`insert into tb2(name, num, type) values(?, ?, ?)`)
    if err != nil {
        stmt1.Close() // 确保已准备的语句被关闭
        return nil, fmt.Errorf("failed to prepare stmt2: %w", err)
    }
    return &DBManager{db: db, stmt1: stmt1, stmt2: stmt2}, nil
}

func (dm *DBManager) Close() {
    dm.stmt1.Close()
    dm.stmt2.Close()
}

// 使用预处理语句的方法
func (dm *DBManager) PersistPerson(person Person) error {
    tx, err := dm.db.Begin()
    if err != nil {
        return fmt.Errorf("failed to begin transaction: %w", err)
    }
    defer func() {
        if r := recover(); r != nil {
            _ = tx.Rollback()
            panic(r)
        } else if err != nil {
            _ = tx.Rollback()
        }
    }()

    // 使用事务内的预处理语句（stmt.ExecContext with transaction）
    // 注意：这里的stmt1和stmt2是全局（或DBManager级别）的，
    // 如果要与事务绑定，需要使用 tx.Stmt(dm.stmt1) 或 tx.PrepareContext
    // 更推荐在事务内重新PrepareContext或直接使用ExecContext

    // 更好的做法是在事务内重新Prepare，或者直接使用tx.ExecContext
    // 如果是简单的SQL，直接使用 tx.Exec 也是可以的
    if _, err = tx.Exec(`insert into tb1(name) values(?)`, person.Name); err != nil {
        return fmt.Errorf("failed to exec tb1: %w", err)
    }

    for _, x := range person.Cards {
        if _, err = tx.Exec(`insert into tb2(name, num, type) values(?, ?, ?)`, person.Name, x.Number, x.Type); err != nil {
            return fmt.Errorf("failed to exec tb2 for card %s: %w", x.Number, err)
        }
    }

    return tx.Commit()
}