如何匹配MySQL JSON列的完整内容？解决键顺序匹配失败问题

这个问题我之前也碰到过——MySQL的JSON类型在直接用=比较时，是严格按照字符串的字节顺序来判断相等性的，哪怕两个JSON的键值对完全一样，只要键的排列顺序不同，就会被判定为不相等，这就是你查询返回matches === 0的核心原因。

下面给你几种可行的解决方案，适配不同的MySQL版本：

方案1：使用JSON_EQUAL()函数（MySQL 8.0.17+） #

这是最直接的解决方式，JSON_EQUAL()会忽略JSON对象的键顺序，只深度比较内容和结构是否一致。修改后的SQL如下：

SELECT COUNT(id) as matches FROM my_table 
WHERE JSON_EQUAL(my_table.settings, '{ "color": "red", "location": "Australia", "flavour": "cheese" }');

方案2：使用JSON_NORMALIZE()标准化后比较（MySQL 8.0.13+） #

如果你的MySQL版本稍旧一点（但不低于8.0.13），可以用JSON_NORMALIZE()把JSON对象的按键名排序，这样不管原始存储的键顺序如何，标准化后的字符串都会一致，再用=比较就没问题了：

SELECT COUNT(id) as matches FROM my_table 
WHERE JSON_NORMALIZE(my_table.settings) = JSON_NORMALIZE('{ "color": "red", "location": "Australia", "flavour": "cheese" }');

方案3：逐个比较JSON键值对（兼容所有支持JSON的MySQL版本） #

如果你的MySQL版本低于8.0.13，这个兼容性最强的方法适合你——直接提取每个键对应的值进行比较，用->>运算符（或者JSON_EXTRACT()）即可：

SELECT COUNT(id) as matches FROM my_table 
WHERE 
  settings->>'$.color' = 'red' 
  AND settings->>'$.location' = 'Australia' 
  AND settings->>'$.flavour' = 'cheese';

这种方法还有额外好处：如果你给JSON字段的键创建了索引，查询效率会比全JSON匹配更高。

小提示 #

如果你的业务经常需要做这种全JSON内容匹配，建议可以在存储时就用JSON_NORMALIZE()标准化后再存入，这样后续查询用=直接比较也不会有问题。

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