MySQL 5.7中如何从JSON字段查询去重ID？求性能最优方案

针对你的场景（MySQL 5.7，数千行JSON格式数据），从性能角度看，直接在数据库层面完成去重ID的提取是最优选择——完全没必要把所有数据拉到PHP里再处理，数据库专门做这类数据聚合和筛选的底层优化，能帮你省掉大量的网络IO和PHP内存开销，数据量越大，这个优势越明显。

具体MySQL查询实现 #

因为MySQL 5.7没有8.0的JSON_TABLE函数，我们需要用一个数字辅助表来拆分JSON的键数组，步骤如下：

1. 先创建一个临时数字表（如果你的JSON对象最多有10个键，这个就够了；如果键更多，就添加更多数字）：

CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

2. 执行查询提取所有去重ID：

SELECT DISTINCT 
  JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(attributes), CONCAT('$[', n-1, ']'))) AS unique_id
FROM your_table
JOIN nums ON n <= JSON_LENGTH(JSON_KEYS(attributes))
ORDER BY unique_id;

代码解释： #

• JSON_KEYS(attributes)：把JSON对象的所有键转换成一个JSON数组（比如["7","57","65","66"]）
• JSON_LENGTH(...)：获取这个键数组的长度，确保我们只遍历到实际存在的键
• JSON_EXTRACT(..., CONCAT('$[', n-1, ']'))：逐个取出数组里的元素（JSON数组是0下标，所以用n-1）
• JSON_UNQUOTE(...)：去掉键值的引号，得到纯数字ID
• DISTINCT：自动去重所有ID

对比PHP处理的性能 #

如果用PHP处理，你需要先把所有attributes列的数据查询出来，然后循环每一行：

$uniqueIds = [];
// 假设$pdo是你的数据库连接
$stmt = $pdo->query("SELECT attributes FROM your_table");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $json = json_decode($row['attributes'], true);
    $uniqueIds = array_merge($uniqueIds, array_keys($json));
}
$uniqueIds = array_unique($uniqueIds);
sort($uniqueIds);

这种方法的问题很明显：

• 需要把几千行JSON数据全部从数据库传输到PHP，增加了网络开销
• PHP需要在内存中存储所有JSON解码后的数组，数据量大时内存占用会很高
• PHP的数组操作效率远不如数据库的底层优化，速度会慢很多

所以除非你的业务有特殊需求，否则优先用MySQL的方案。

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