使用JSON_TABLE值对Mysql的WHERE条件进行比较

Question

我得到了一个以逗号分隔的 JSON 值列表形式的 ID 列表，一些示例数据集如下所示 [340596,340597,340595] 这个列表可能很大，有时有 50k ID，用逗号分隔

以下查询将这些 ID 连接到表主键并获取表中当前存在的记录

SELECT s.id,s.contactid, s.Quantity FROM 
JSON_TABLE('[340596,340597,340595]', '$[*]' columns (Id int path '$')) AS sm   
LEFT JOIN mastertable s ON s.Id = sm.id

主表可能包含这些ID，也可能这些记录已从主表中删除，因此此查询的目的是确保返回结果集仅包含活动记录

我必须对此查询再应用一次过滤，并且该过滤基于另一个 JSON int 数组，并且需要将其与 ContactID 列进行匹配

SELECT s.id,s.contactid, s.Quantity FROM 
JSON_TABLE('[340596,340597,340595]', '$[*]' columns (Id int path '$')) AS sm   
LEFT JOIN mastertable s ON s.Id = sm.id  
WHERE s.ContactId IN (
SELECT cm.id  FROM 
JSON_TABLE('[12345,450597,640595]', '$[*]' columns (Id int path '$')) AS cm  
)

然而，对于大型结果集，Mysql IN 性能并不更好。我们可以用其他更好的方式来代替这个IN吗？

Answer 1

您可以将 IN 子句中的 id 转储到临时表中，然后将它们与 JSON_TABLE 连接以获得结果。 或者，您可以使用 CTE 并加入其中。

with temp as (
SELECT cm.id  FROM 
JSON_TABLE('[12345,450597,640595]', '$[*]' columns (Id int path '$')) AS cm  
)
SELECT s.id,s.contactid, s.Quantity FROM 
JSON_TABLE('[340596,340597,340595]', '$[*]' columns (Id int path '$')) AS sm   
LEFT JOIN mastertable s ON s.Id = sm.id
INNER JOIN temp t ON s.ID = t.id;