CodeIgniter中使用JSON_CONTAINS查询JSON数组元素的问题

问题背景 #

你的CodeIgniter查询代码如下：

$this->db->select('*')->from('users')->where(['doctor_id' => NULL])->where("JSON_CONTAINS(chamber_id, '2', '$')")->get()->result()

数据库中chamber_id字段存储的是JSON数组格式数据：["2","47","93"]，需要用原生SQL实现查询该字段数组中包含"2"的记录。

原生SQL解决方案 #

写法1：直接传入JSON格式字符串 #

SELECT * FROM users 
WHERE doctor_id IS NULL 
AND JSON_CONTAINS(chamber_id, '"2"', '$');

写法2：使用JSON_QUOTE函数自动转义 #

SELECT * FROM users 
WHERE doctor_id IS NULL 
AND JSON_CONTAINS(chamber_id, JSON_QUOTE('2'), '$');

关键说明 #

• chamber_id存储的是字符串类型的JSON数组，数组元素是带双引号的字符串（如"2"），因此JSON_CONTAINS的匹配值必须是JSON格式的字符串，而非普通字符串。
• 直接传入'2'会被MySQL解析为数字类型，和数组里的字符串元素不匹配，导致无法查询到目标数据。

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