使用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;