使用Golang实现MySQL中表A和表B的插入操作及一体处理

在Go语言中实现MySQL表A和表B的原子性插入操作，需要使用事务（Transaction）来确保数据一致性。以下是完整的实现示例：

package main

import (
    "context"
    "database/sql"
    "fmt"
    "log"
    "time"

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

const (
    username = "root"
    password = "root"
    hostname = "localhost:3306"
    dbname   = "ecommerce"
)

// 创建表A和表B
func createTables(db *sql.DB) error {
    // 创建表A
    queryA := `CREATE TABLE IF NOT EXISTS tableA(
        id int primary key auto_increment,
        name varchar(100) not null,
        value int,
        created_at datetime default CURRENT_TIMESTAMP
    )`
    
    // 创建表B
    queryB := `CREATE TABLE IF NOT EXISTS tableB(
        id int primary key auto_increment,
        a_id int not null,
        description text,
        amount decimal(10,2),
        created_at datetime default CURRENT_TIMESTAMP,
        FOREIGN KEY (a_id) REFERENCES tableA(id)
    )`

    ctx, cancelfunc := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancelfunc()

    // 创建表A
    _, err := db.ExecContext(ctx, queryA)
    if err != nil {
        log.Printf("Error creating tableA: %v", err)
        return err
    }

    // 创建表B
    _, err = db.ExecContext(ctx, queryB)
    if err != nil {
        log.Printf("Error creating tableB: %v", err)
        return err
    }

    log.Println("Tables created successfully")
    return nil
}

// 原子性插入数据到表A和表B
func insertDataAtomically(db *sql.DB, aName string, aValue int, bDescription string, bAmount float64) error {
    // 开始事务
    tx, err := db.Begin()
    if err != nil {
        return fmt.Errorf("begin transaction failed: %v", err)
    }
    
    // 确保事务在函数结束时要么提交要么回滚
    defer func() {
        if p := recover(); p != nil {
            tx.Rollback()
            panic(p)
        } else if err != nil {
            tx.Rollback()
        } else {
            err = tx.Commit()
        }
    }()

    // 插入表A数据
    var aID int64
    result, err := tx.Exec(
        "INSERT INTO tableA(name, value) VALUES(?, ?)",
        aName, aValue,
    )
    if err != nil {
        return fmt.Errorf("insert into tableA failed: %v", err)
    }

    // 获取表A的自增ID
    aID, err = result.LastInsertId()
    if err != nil {
        return fmt.Errorf("get last insert id failed: %v", err)
    }

    // 插入表B数据，使用表A的ID作为外键
    _, err = tx.Exec(
        "INSERT INTO tableB(a_id, description, amount) VALUES(?, ?, ?)",
        aID, bDescription, bAmount,
    )
    if err != nil {
        return fmt.Errorf("insert into tableB failed: %v", err)
    }

    log.Printf("Successfully inserted data with transaction. TableA ID: %d", aID)
    return nil
}

// 使用上下文进行事务处理
func insertDataWithContext(db *sql.DB, aName string, aValue int, bDescription string, bAmount float64) error {
    ctx := context.Background()
    
    // 开始带上下文的事务
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin transaction failed: %v", err)
    }

    // 插入表A数据
    result, err := tx.ExecContext(ctx,
        "INSERT INTO tableA(name, value) VALUES(?, ?)",
        aName, aValue,
    )
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("insert into tableA failed: %v", err)
    }

    // 获取表A的自增ID
    aID, err := result.LastInsertId()
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("get last insert id failed: %v", err)
    }

    // 插入表B数据
    _, err = tx.ExecContext(ctx,
        "INSERT INTO tableB(a_id, description, amount) VALUES(?, ?, ?)",
        aID, bDescription, bAmount,
    )
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("insert into tableB failed: %v", err)
    }

    // 提交事务
    if err := tx.Commit(); err != nil {
        return fmt.Errorf("commit transaction failed: %v", err)
    }

    log.Printf("Successfully inserted data with context. TableA ID: %d", aID)
    return nil
}

// 批量插入示例
func batchInsertData(db *sql.DB, data []struct {
    aName        string
    aValue       int
    bDescription string
    bAmount      float64
}) error {
    tx, err := db.Begin()
    if err != nil {
        return fmt.Errorf("begin transaction failed: %v", err)
    }

    defer func() {
        if err != nil {
            tx.Rollback()
        } else {
            err = tx.Commit()
        }
    }()

    for _, item := range data {
        // 插入表A
        result, err := tx.Exec(
            "INSERT INTO tableA(name, value) VALUES(?, ?)",
            item.aName, item.aValue,
        )
        if err != nil {
            return fmt.Errorf("insert into tableA failed: %v", err)
        }

        // 获取自增ID
        aID, err := result.LastInsertId()
        if err != nil {
            return fmt.Errorf("get last insert id failed: %v", err)
        }

        // 插入表B
        _, err = tx.Exec(
            "INSERT INTO tableB(a_id, description, amount) VALUES(?, ?, ?)",
            aID, item.bDescription, item.bAmount,
        )
        if err != nil {
            return fmt.Errorf("insert into tableB failed: %v", err)
        }
    }

    log.Printf("Successfully batch inserted %d records", len(data))
    return nil
}

func main() {
    db, err := sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s)/%s", username, password, hostname, dbname))
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 设置连接池参数
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    db.SetConnMaxLifetime(time.Minute * 5)

    // 测试连接
    ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
    defer cancel()
    if err := db.PingContext(ctx); err != nil {
        log.Fatal(err)
    }

    // 创建表
    if err := createTables(db); err != nil {
        log.Fatal(err)
    }

    // 示例1: 原子性插入单条数据
    err = insertDataAtomically(db, "Product1", 100, "Description for product1", 99.99)
    if err != nil {
        log.Printf("Atomic insert failed: %v", err)
    }

    // 示例2: 使用上下文插入数据
    err = insertDataWithContext(db, "Product2", 200, "Description for product2", 199.99)
    if err != nil {
        log.Printf("Context insert failed: %v", err)
    }

    // 示例3: 批量插入数据
    batchData := []struct {
        aName        string
        aValue       int
        bDescription string
        bAmount      float64
    }{
        {"Product3", 300, "Description 3", 299.99},
        {"Product4", 400, "Description 4", 399.99},
        {"Product5", 500, "Description 5", 499.99},
    }
    
    err = batchInsertData(db, batchData)
    if err != nil {
        log.Printf("Batch insert failed: %v", err)
    }
}

这个实现提供了三种方式处理表A和表B的原子性插入：

1. 基础事务处理 (insertDataAtomically)：使用标准的Begin()、Commit()和Rollback()方法
2. 带上下文的事务处理 (insertDataWithContext)：使用BeginTx()支持上下文取消和超时
3. 批量事务处理 (batchInsertData)：在单个事务中处理多条记录插入

所有方法都确保要么两个表都插入成功，要么都失败回滚，保持数据一致性。