關於where子句的M ysql正則表達式

我需要一個正則表達式，它只會給我那些匹配模式的記錄。下面是表結構

auto_id (Auto-increment)  | json_string (Text)

樣本記錄如下：

auto_id | json_string
--------+-----------------------------------------------------------------------------------------------
1 | {"12":"1774","13":"V.","14":"2","16":"9","17":"39","18":"59", "19":"88","20":"144","21":"236"}
2 | {"12":"1774","13":"Y.","14":"2","16":"7","17":"39","18":"59", "19":"88","20":"144","21":"236"}
3 | {"12":"1774","13":"V.","14":"2","16":"9","17":"39","18":"59", "19":"88","20":"144","21":"236"}

我的查詢是：

SELECT * 
FROM tbl_json 
WHERE json_data REGEXP '"14":"[1|2]"' 
AND json_data REGEXP '"16":["39|7"]' 
LIMIT 3;

這應該只返回 id 為 2 的記錄。而不是 1 和 3 作為"16":"39'或"16":"7"不是json_string.

有人知道我做錯了什麼嗎？

您已將 REGEXP 語法用於替代(first|second)和符號類[abcd]。您的查詢應如下所示：

SELECT * 
 FROM tbl_json 
WHERE json_data REGEXP '"14":"(1|2)"' 
  AND json_data REGEXP '"16":"(39|7)"' 
LIMIT 3;

引用自：https://dba.stackexchange.com/questions/222945