MySQL LIKE 与 json_extract [英] MySQL LIKE with json_extract

问题描述

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

I have a MySQL query, where I filter by a json field:

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

它返回 1 行，如下所示:

It returns 1 row, which looks like:

1, "userName1" 看到不在子句中的引号了吗?

1, "userName1" See the quotes that are not in the clause?

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

What I need is to make the WHERE clause case insensitive. But when I do

WHERE username LIKE 'userName1';

它返回 0 行.我不明白为什么它会这样工作，= 子句虽然没有那些双引号却能工作.

it returns 0 rows. I don't understand why it works this way, the = clause works though it doesn't have those double quotes.

如果我这样做

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

WHERE username LIKE '%userName1%'; now also returns the row, because %% takes quotes into consideration:

1, "userName1"

但是当我这样做时

WHERE username LIKE '%username1%';它返回 0 行，因此与通常的 MySQL LIKE 不同，它以某种方式区分大小写.

WHERE username LIKE '%username1%'; it returns 0 rows, so unlike the usual MySQL LIKE it's somehow case sensitive.

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

What am I doing wrong and how to filter the json payload the case insensitive way? EDIT========================================= The guess is that COLLATE should be used here, but so far I don't understand how to make it work.

推荐答案

好的，我能够通过在 LIKE 子句之后添加 COLLATE utf8mb4_general_ci 来解决不区分大小写的问题.

Okay, I was able to solve the case insensitivity by adding COLLATE utf8mb4_general_ci after the LIKE clause.

所以这里的重点是找到一个有效的排序规则，然后可以通过研究您使用的数据库来找到它.

So the point here is to find a working collation, which in its turn can be found by researching the db you work with.

这篇关于MySQL LIKE 与 json_extract的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！