时间戳

首页 > 解决方案 > MySQL LIKE 与 json_extract

mysql - MySQL LIKE 与 json_extract

问题描述

我有一个 MySQL 查询，我在其中按 json 字段过滤：

SELECT id, username 
FROM   (SELECT id, 
               Json_extract(payload, '$.username') AS username 
        FROM   table1) AS tmp 
WHERE  username = 'userName1';

它返回 1 行，如下所示：

1, "userName1"看到子句中没有的引号了吗？

我需要的是使WHERE子句不区分大小写。但是当我这样做时

WHERE username LIKE 'userName1';

它返回 0 行。我不明白为什么它会这样=工作，尽管它没有那些双引号，但该子句仍然有效。

如果我做

WHERE username LIKE '%userName1%'; now 也返回该行，因为%%考虑了引号：

1, "userName1"

但是当我这样做时

WHERE username LIKE '%username1%'; 它返回 0 行，因此与通常的 MySQL 不同，LIKE它在某种程度上区分大小写。

我在做什么错以及如何以不区分大小写的方式过滤 json 有效负载？EDIT=========================================== 猜测COLLATE应该在这里使用，但到目前为止我不明白如何使它工作。

标签： mysqlsqlsql-likemysql-jsoncollate

解决方案

MySQL 的默认排序规则latin1_swedish_ci在 8.0 之前和utf8mb4_0900_ai_ci8.0 之后。因此，在普通列中，非二进制字符串比较默认不区分大小写。

但是，正如MySQL 手册中提到的 JSON 类型

utf8mb4MySQL 使用字符集和排序规则处理 JSON 上下文中使用的字符串utf8mb4_bin。”。

因此，您的 JSON 值在utf8mb4_bin排序规则中，您需要对任一操作数应用不区分大小写的排序规则，以使比较不区分大小写。

例如

WHERE username COLLATE XXX LIKE '...'

whereXXX应该是 utf8mb4 排序规则（例如utf8mb4_general_ci您提到的。）。

或者

WHERE username LIKE '...' COLLATE YYY

whereYYY应该是与您连接的字符集匹配的排序规则。

对于相等比较，您应该使用或取消引用提取运算符取消引用JSON 值JSON_UNQUOTE()->>

例如

JSON_UNQUOTE(JSON_EXTRACT(payload, '$.username'))

或者干脆

payload->>'$.username'

JSON 类型和函数的工作方式与普通数据类型不同。看来你是新手。因此，我建议您在将其投入生产环境之前仔细阅读手册。

推荐阅读