mysql - 如何在 MySQL 中搜索具有 JSON 值的字段？

Question

我有一个 MySQL 列，其中包含的字段的数据如下所示:

["I don't read books anymore","books \/ novels"]

我想检索包含“书籍/小说”的所有字段。我该怎么做？我试过:

SELECT * FROM `table` where data_field like '%books \/ novels%'

但这会返回零结果。为什么？应该如何搜索 JSON 字段？

Answer 1

试试这个:

SELECT * FROM `table` WHERE date_field like '%books \\\\/ novels%' 

请参阅此处注释: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like

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 data_field like '%books __ novels%'

或者可能是这样的:

SELECT * FROM `table` where data_field like '%books _ novels%'

下划线是类似搜索中的单个字符通配符。