使用MySQL LIKE运算子处理以JSON编码的字段 [英] Using MySQL LIKE operator for fields encoded in JSON

问题描述

我一直在尝试通过此查询获取表格行:

I've been trying to get a table row with this query:

SELECT * FROM `table` WHERE `field` LIKE "%\u0435\u0442\u043e\u0442%"

字段本身:

Field                                     
--------------------------------------------------------------------
\u0435\u0442\u043e\u0442 \u0442\u0435\u043a\u0441\u0442 \u043d\u0430

尽管我似乎无法使其正常运行. 我已经尝试过尝试使用反斜杠字符:

Although I can't seem to get it working properly. I've already tried experimenting with the backslash character:

LIKE "%\\u0435\\u0442\\u043e\\u0442%"
LIKE "%\\\\u0435\\\\u0442\\\\u043e\\\\u0442%"

但是它们似乎也不起作用.

But none of them seems to work, as well.

如果有人可以提示我做错了事，我将不胜感激. 提前致谢！

I'd appreciate if someone could give a hint as to what I'm doing wrong. Thanks in advance!

问题已解决. 解决方案:即使在更正查询语法之后，它也不会返回任何结果.将字段设为BINARY后，查询开始工作.

Problem solved. Solution: even after correcting the syntax of the query, it didn't return any results. After making the field BINARY the query started working.

推荐答案

如字符串比较函数:

注意

因为MySQL在字符串中使用C转义语法(例如，"\n"表示换行符)，所以必须将在\n"，请将其指定为"\\n".要搜索"\"，请将其指定为"\\\\"；这是因为解析器会一次删除反斜杠，而在进行模式匹配时会再次反斜杠，因此要匹配一个反斜杠.

Note

Because MySQL uses C escape syntax in strings (for example, "\n" to represent a newline character), you must double any "\" that you use in LIKE strings. For example, to search for "\n", specify it as "\\n". To search for "\", specify it as "\\\\"; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.

因此:

SELECT * FROM `table` WHERE `field` LIKE '%\\\\u0435\\\\u0442\\\\u043e\\\\u0442%'

在 sqlfiddle 上查看.

这篇关于使用MySQL LIKE运算子处理以JSON编码的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！