Go语言中将SQL关系数据转换为嵌套JSON结构的最佳实践

package main

import (
    "database/sql"
    "encoding/json"
    "fmt"
    "log"

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

// Comment 结构体表示评论
type Comment struct {
    ID      int    `json:"id"`
    Content string `json:"comment"`
}

// Post 结构体表示文章，包含一个评论切片
type Post struct {
    ID       int       `json:"id"`
    Title    string    `json:"title"`
    Comments []Comment `json:"comments"` // 嵌套的评论列表
}

-- posts 表
CREATE TABLE Posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL
);

-- comments 表
CREATE TABLE Comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    comment TEXT NOT NULL,
    FOREIGN KEY (post_id) REFERENCES Posts(id) ON DELETE CASCADE
);

-- 示例数据
INSERT INTO Posts (title) VALUES ('Go语言入门'), ('数据库连接');
INSERT INTO Comments (post_id, comment) VALUES
(1, '这是一篇很棒的Go语言教程！'),
(1, '我从中学到了很多。'),
(2, '如何处理连接池？');

SELECT
    p.id, p.title,
    c.id AS comment_id, c.comment AS comment_content
FROM
    Posts p
LEFT JOIN
    Comments c ON p.id = c.post_id
ORDER BY
    p.id, c.id;

func getPostsWithComments(db *sql.DB) ([]Post, error) {
    query := `
        SELECT
            p.id, p.title,
            c.id AS comment_id, c.comment AS comment_content
        FROM
            Posts p
        LEFT JOIN
            Comments c ON p.id = c.post_id
        ORDER BY
            p.id, c.id;
    `
    rows, err := db.Query(query)
    if err != nil {
        return nil, fmt.Errorf("查询失败: %w", err)
    }
    defer rows.Close()

    posts := []Post{}

    for rows.Next() {
        var post Post
        var comment Comment
        // 使用 sql.NullInt64 和 sql.NullString 处理 LEFT JOIN 可能产生的 NULL 值
        var commentID sql.NullInt64
        var commentContent sql.NullString

        err := rows.Scan(
            &post.ID, &post.Title,
            &commentID, &commentContent,
        )
        if err != nil {
            return nil, fmt.Errorf("扫描行失败: %w", err)
        }

        // 检查当前行是否属于一个新的 Post
        // 如果 posts 列表为空，或者当前 post.ID 与列表中最后一个 post 的 ID 不同，
        // 则表示这是一个新的 Post
        if len(posts) == 0 || posts[len(posts)-1].ID != post.ID {
            // 初始化新 Post 的 Comments 切片
            post.Comments = []Comment{}
            // 如果 commentID 有效（即存在评论），则添加评论
            if commentID.Valid {
                comment.ID = int(commentID.Int64)
                comment.Content = commentContent.String
                post.Comments = append(post.Comments, comment)
            }
            posts = append(posts, post)
        } else {
            // 如果 commentID 有效，则将评论添加到列表中最后一个 Post 的 Comments 切片中
            if commentID.Valid {
                comment.ID = int(commentID.Int64)
                comment.Content = commentContent.String
                posts[len(posts)-1].Comments = append(posts[len(posts)-1].Comments, comment)
            }
            // 如果 commentID 无效（即文章没有评论），则无需操作，因为文章本身已添加
        }
    }

    if err = rows.Err(); err != nil {
        return nil, fmt.Errorf("遍历行错误: %w", err)
    }

    return posts, nil
}

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()

    // 确保数据库连接有效
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("成功连接到数据库！")

    posts, err := getPostsWithComments(db)
    if err != nil {
        log.Fatal(err)
    }

    // 将结果序列化为 JSON
    jsonData, err := json.MarshalIndent(posts, "", "  ")
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println(string(jsonData))
}

[
  {
    "id": 1,
    "title": "Go语言入门",
    "comments": [
      {
        "id": 1,
        "comment": "这是一篇很棒的Go语言教程！"
      },
      {
        "id": 2,
        "comment": "我从中学到了很多。"
      }
    ]
  },
  {
    "id": 2,
    "title": "数据库连接",
    "comments": [
      {
        "id": 3,
        "comment": "如何处理连接池？"
      }
    ]
  },
  {
    "id": 3,
    "title": "另一篇文章",
    "comments": []
  }
]