从MySQL 5.7迁移至MariaDB 10.6.5后SQL查询语法错误求助
错误信息
Uncaught Exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON) FROM attributes AS _sub_attr WHERE JSON_EXTRACT(sub_prop_attr.attrib_te...' at line 1 query: SELECT *,p.id AS id, DATEDIFF(CURDATE(),DATE(p.created)) AS days, COALESCE(bed_room,bath_room,parking,construction_size,lot_size,floors,building_age) AS chs , (SELECT JSON_OBJECTAGG(attribute_name, (SELECT CAST(CONCAT('[',GROUP_CONCAT(JSON_QUOTE(_sub_attr.attribute_name)),']') AS JSON) FROM attributes AS _sub_attr WHERE JSON_EXTRACT(sub_prop_attr.attrib_terms, JSON_UNQUOTE(JSON_SEARCH(sub_prop_attr.attrib_terms, 'one', _sub_attr.id))) IS NOT NULL)) FROM prop_attributes AS sub_prop_attr LEFT JOIN attributes AS sub_attr ON sub_prop_attr.attribute_id = sub_attr.id WHERE prop_id = p.id) AS JSON_attributes FROM properties p LEFT JOIN (SELECT id, state_name, CONCAT("en ",state_name) AS state_alias FROM states) s on p.state=s.id LEFT JOIN (SELECT id, city_na in C:\wamp64...\classes\MysqliDb.php on line 1819
问题根源
报错核心是MySQL与MariaDB在JSON函数语法/兼容性上的差异：
- 手动用
CONCAT+GROUP_CONCAT拼接JSON数组后CAST AS JSON的写法，在MariaDB中存在语法解析逻辑差异，触发语法错误。 JSON_EXTRACT+JSON_UNQUOTE+JSON_SEARCH的组合写法冗余，且在MariaDB中容易出现解析异常。
修复方案
改用原生JSON聚合函数替代手动拼接，同时优化JSON存在性检查逻辑，确保跨数据库兼容：
修改后的PHP查询片段
$Srelated_attributes = ", (SELECT JSON_OBJECTAGG(attribute_name, (SELECT JSON_ARRAYAGG(JSON_QUOTE(_sub_attr.attribute_name)) FROM attributes AS _sub_attr WHERE JSON_CONTAINS(sub_prop_attr.attrib_terms, CAST(_sub_attr.id AS JSON), '$') IS NOT NULL)) FROM prop_attributes AS sub_prop_attr LEFT JOIN attributes AS sub_attr ON sub_prop_attr.attribute_id = sub_attr.id WHERE prop_id = p.id) AS JSON_attributes";
关键修改点
替换手动JSON数组拼接：
原写法用CONCAT('[',GROUP_CONCAT(...),']')手动拼数组再转JSON，改为JSON_ARRAYAGG(JSON_QUOTE(_sub_attr.attribute_name))，直接生成标准JSON数组，避免转义与CAST语法问题。优化JSON存在性检查：
用JSON_CONTAINS(sub_prop_attr.attrib_terms, CAST(_sub_attr.id AS JSON), '$')替代JSON_EXTRACT+JSON_UNQUOTE+JSON_SEARCH的组合，逻辑更简洁，且在MySQL和MariaDB中行为完全一致。版本兼容性确认：
确保使用的MariaDB版本≥10.2（该版本开始全面支持JSON函数），若版本过低需升级数据库。
验证
修改后查询可在MariaDB和MySQL环境下同时正常运行，移除了原有的语法兼容问题，且JSON生成逻辑更可靠。
内容的提问来源于stack exchange，提问作者Adrian Vignolo
