使用MySQL LIKE运算子处理以JSON编码的字段

本文介绍了使用MySQL LIKE运算子处理以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"表示换行符)，所以必须将在\""加倍.mysql/doc/zh-CN/string-comparison-functions.html#operator_like"rel =" nofollow> LIKE 字符串.例如，要搜索"\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 上查看.