Golang中如何实现主从查询

在Go中实现主从查询通常有两种方式：嵌套查询和JOIN查询。以下是具体实现：

1. 嵌套查询方式（N+1查询）

package main

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

type Order struct {
    ID      int
    UserID  int
    Amount  float64
    Details []OrderDetail
}

type OrderDetail struct {
    ID      int
    OrderID int
    Product string
    Price   float64
}

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 主查询：获取订单列表
    rows, err := db.Query("SELECT id, user_id, amount FROM orders WHERE user_id = ?", 1)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    var orders []Order
    
    for rows.Next() {
        var order Order
        if err := rows.Scan(&order.ID, &order.UserID, &order.Amount); err != nil {
            log.Fatal(err)
        }

        // 从查询：获取订单详情
        detailRows, err := db.Query("SELECT id, order_id, product, price FROM order_details WHERE order_id = ?", order.ID)
        if err != nil {
            log.Fatal(err)
        }
        defer detailRows.Close()

        for detailRows.Next() {
            var detail OrderDetail
            if err := detailRows.Scan(&detail.ID, &detail.OrderID, &detail.Product, &detail.Price); err != nil {
                log.Fatal(err)
            }
            order.Details = append(order.Details, detail)
        }
        
        orders = append(orders, order)
    }

    // 输出结果
    for _, order := range orders {
        fmt.Printf("订单ID: %d, 金额: %.2f\n", order.ID, order.Amount)
        for _, detail := range order.Details {
            fmt.Printf("  商品: %s, 价格: %.2f\n", detail.Product, detail.Price)
        }
    }
}

2. JOIN查询方式（单次查询）

package main

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

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(127.0.0.1:3306)/dbname")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // 使用JOIN一次性查询所有数据
    query := `
        SELECT 
            o.id, o.user_id, o.amount,
            od.id, od.order_id, od.product, od.price
        FROM orders o
        LEFT JOIN order_details od ON o.id = od.order_id
        WHERE o.user_id = ?
        ORDER BY o.id, od.id
    `
    
    rows, err := db.Query(query, 1)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    orders := make(map[int]*Order)
    
    for rows.Next() {
        var (
            orderID, userID int
            amount float64
            detailID sql.NullInt64
            detailOrderID sql.NullInt64
            product sql.NullString
            price sql.NullFloat64
        )
        
        if err := rows.Scan(&orderID, &userID, &amount, 
            &detailID, &detailOrderID, &product, &price); err != nil {
            log.Fatal(err)
        }

        // 如果订单不存在，创建新订单
        if _, exists := orders[orderID]; !exists {
            orders[orderID] = &Order{
                ID:     orderID,
                UserID: userID,
                Amount: amount,
            }
        }

        // 如果有详情记录，添加到订单中
        if detailID.Valid {
            orders[orderID].Details = append(orders[orderID].Details, OrderDetail{
                ID:      int(detailID.Int64),
                OrderID: int(detailOrderID.Int64),
                Product: product.String,
                Price:   price.Float64,
            })
        }
    }

    // 输出结果
    for _, order := range orders {
        fmt.Printf("订单ID: %d, 金额: %.2f\n", order.ID, order.Amount)
        for _, detail := range order.Details {
            fmt.Printf("  商品: %s, 价格: %.2f\n", detail.Product, detail.Price)
        }
    }
}

3. 使用预加载模式

package main

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

func loadOrdersWithDetails(db *sql.DB, userID int) ([]Order, error) {
    // 查询主表
    orders, err := getOrders(db, userID)
    if err != nil {
        return nil, err
    }

    // 批量查询从表
    if len(orders) > 0 {
        orderIDs := make([]interface{}, len(orders))
        for i, order := range orders {
            orderIDs[i] = order.ID
        }
        
        detailsMap, err := getOrderDetailsBatch(db, orderIDs)
        if err != nil {
            return nil, err
        }

        // 关联数据
        for i := range orders {
            if details, exists := detailsMap[orders[i].ID]; exists {
                orders[i].Details = details
            }
        }
    }
    
    return orders, nil
}

func getOrders(db *sql.DB, userID int) ([]Order, error) {
    rows, err := db.Query("SELECT id, user_id, amount FROM orders WHERE user_id = ?", userID)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    var orders []Order
    for rows.Next() {
        var order Order
        if err := rows.Scan(&order.ID, &order.UserID, &order.Amount); err != nil {
            return nil, err
        }
        orders = append(orders, order)
    }
    return orders, nil
}

func getOrderDetailsBatch(db *sql.DB, orderIDs []interface{}) (map[int][]OrderDetail, error) {
    if len(orderIDs) == 0 {
        return make(map[int][]OrderDetail), nil
    }

    // 构建IN查询
    query := "SELECT id, order_id, product, price FROM order_details WHERE order_id IN ("
    placeholders := ""
    for i := 0; i < len(orderIDs); i++ {
        if i > 0 {
            placeholders += ","
        }
        placeholders += "?"
    }
    query += placeholders + ")"

    rows, err := db.Query(query, orderIDs...)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    detailsMap := make(map[int][]OrderDetail)
    for rows.Next() {
        var detail OrderDetail
        if err := rows.Scan(&detail.ID, &detail.OrderID, &detail.Product, &detail.Price); err != nil {
            return nil, err
        }
        detailsMap[detail.OrderID] = append(detailsMap[detail.OrderID], detail)
    }
    
    return detailsMap, nil
}

嵌套查询简单但效率较低，JOIN查询效率高但需要处理重复数据，预加载模式在数据量较大时性能更好。根据实际场景选择合适的方式。