MySQL三表连接生成JSON数组时重复数据问题求助

你遇到的重复问题本质是笛卡尔积：一个商品关联2个属性、4张图片，三表连接时数据库会生成 2×4=8 条组合记录，这就是属性重复4次、图片重复2次的原因。DISTINCT无效是因为每条记录的atr_id或img_id是唯一的，整行数据并不重复；GROUP BY如果只按item_id分组，会直接丢失部分属性/图片，因为数据库只会返回每组的第一条记录。

下面给出两种可行的解决方案：

方案一：拆分查询，避免笛卡尔积 #

先单独查询商品+属性，再查询所有图片数据，最后在PHP中将图片对应到商品上，完全避免多表连接产生的重复。

// 第一步：查询商品及关联属性
$rs_items = $conn->prepare("
    SELECT a.item_id, a.it_title, b.atr_id, b.color 
    FROM items a 
    LEFT JOIN atributes b ON a.item_id = b.item_atr_id 
    ORDER BY a.item_id ASC, b.atr_id ASC
");
$rs_items->execute();
$items_data = [];

// 整理商品与属性的关联关系
while ($row = $rs_items->fetch(PDO::FETCH_ASSOC)) {
    $item_id = $row['item_id'];
    // 初始化商品数据结构
    if (!isset($items_data[$item_id])) {
        $items_data[$item_id] = [
            'item_id' => $item_id,
            'it_title' => $row['it_title'],
            'attributes' => [],
            'gallery' => []
        ];
    }
    // 把属性添加到对应商品的数组中
    if (!empty($row['atr_id'])) {
        $items_data[$item_id]['attributes'][] = [
            'atr_id' => $row['atr_id'],
            'color' => $row['color']
        ];
    }
}

// 第二步：查询所有商品的图片数据
$rs_gallery = $conn->prepare("
    SELECT item_gal_id, img_id, file_name 
    FROM gallery 
    ORDER BY item_gal_id ASC, img_id ASC
");
$rs_gallery->execute();

// 将图片对应到所属商品
while ($row = $rs_gallery->fetch(PDO::FETCH_ASSOC)) {
    $item_id = $row['item_gal_id'];
    if (isset($items_data[$item_id])) {
        $items_data[$item_id]['gallery'][] = [
            'img_id' => $row['img_id'],
            'file_name' => $row['file_name']
        ];
    }
}

// 转成最终JSON（重新索引数组）
$final_json = json_encode(array_values($items_data));

方案二：用GROUP_CONCAT合并字段，PHP拆分重组 #

如果不想拆分多次查询，可以用GROUP_CONCAT把每个商品的属性、图片合并成字符串，再在PHP中拆分重组。

// 一次性查询并合并多值字段
$rs_items = $conn->prepare("
    SELECT 
        a.item_id,
        a.it_title,
        GROUP_CONCAT(DISTINCT CONCAT(b.atr_id, '|', b.color) SEPARATOR ';;') AS attributes_str,
        GROUP_CONCAT(DISTINCT CONCAT(c.img_id, '|', c.file_name) SEPARATOR ';;') AS gallery_str
    FROM items a 
    LEFT JOIN atributes b ON a.item_id = b.item_atr_id 
    LEFT JOIN gallery c ON a.item_id = c.item_gal_id 
    GROUP BY a.item_id, a.it_title
    ORDER BY a.item_id ASC
");
$rs_items->execute();

$items_data = [];
while ($row = $rs_items->fetch(PDO::FETCH_ASSOC)) {
    $item = [
        'item_id' => $row['item_id'],
        'it_title' => $row['it_title'],
        'attributes' => [],
        'gallery' => []
    ];
    
    // 拆分属性字符串，还原成数组
    if (!empty($row['attributes_str'])) {
        $attr_parts = explode(';;', $row['attributes_str']);
        foreach ($attr_parts as $part) {
            list($atr_id, $color) = explode('|', $part);
            $item['attributes'][] = [
                'atr_id' => $atr_id,
                'color' => $color
            ];
        }
    }
    
    // 拆分图片字符串，还原成数组
    if (!empty($row['gallery_str'])) {
        $gallery_parts = explode(';;', $row['gallery_str']);
        foreach ($gallery_parts as $part) {
            list($img_id, $file_name) = explode('|', $part);
            $item['gallery'][] = [
                'img_id' => $img_id,
                'file_name' => $file_name
            ];
        }
    }
    
    $items_data[] = $item;
}

$final_json = json_encode($items_data);

内容的提问来源于stack exchange，提问作者Walter