MySQL正则表达式 + 空格 (\s)

Question

我使用的数据库会将JSON条目存储在LONGTEXT数据类型中。我想能够根据JSON数据选择条目。以下是一些示例数据：

我所用的数据库将JSON条目存储在LONGTEXT数据类型中。我希望能够根据JSON数据选择条目。以下是一些示例数据：

 {
    "12f9cb0a-2218-4590-a05d-c1ffab00f693":  {
        "0":  {
            "value": "test"
        }
    },
    "4d1dfd2e-7bc1-4303-9c8c-90856e918bb9":  {
        "item":  {
            "0": "11"
        }
    }
 }

所以我想通过使用REGEXP函数过滤空格（制表符、空格、换行符），选择包含“4d1dfd2e-7bc1-4303-9c8c-90856e918bb9”：{“item”：{“0”：“11”}} 的数据，但我尝试了以下代码却没有成功：

SELECT * FROM my_table WHERE (elements REGEXP BINARY '"4d1dfd2e-7bc1-4303-9c8c-90856e918bb9":\s*{\s*"item":\s*{\s*"0":\s*"11"\s*}\s*}');

使用 Rubular 和 Regexpal.com 进行正则表达式测试有效，但 MYSQL 似乎不喜欢 \s* 表达式。是否有更好的解决方案？

- Tommy Plummer

Answer 1

在 MySQL 8.0 之前，MySQL 的正则表达式仅支持 §12.7.2 "MySQL 5.7 参考手册" 中所列出的符号（或适用版本的对应部分），其中包括 [[:space:]] 符号但不包括 \s 符号。因此，您只需将每个 \s 替换为 [[:space:]]。

在 MySQL 8.0 及更高版本中，MySQL 使用 Unicode 国际组件的正则表达式支持 [link]，其中包含 \s 符号[link]; 但由于 MySQL 本身在字符串中使用 \ 作为转义字符，因此您需要将每个 \s 替换为 \\s。

- ruakh

---

谢谢。是否有一种C转义序列可以处理制表符、空格和换行符？（与\s的替代方案） - Tommy Plummer

重新阅读一下，查询仍然没有返回任何结果:(。它从'"4d1dfd2e-7bc1-4303-9c8c-90856e918bb9":\s*'中断... '"4d1dfd2e-7bc1-4303-9c8c-90856e918bb9":\s*{'不起作用。 - Tommy Plummer

MySQL 支持 \s。只需要使用双反斜杠 \\s 即可。 - Matthias Gwiozda

@MatthiasGwiozda：感谢您的投票。这个答案已经超过七年了；MySQL现在拥有的功能当时可能并不存在，这应该不会让您感到惊讶。 - ruakh

是的，如果您能编辑您的答案并写一些信息说明现在已经可以实现（如果确实是因为版本差异），那就太好了。这可能会让阅读您答案的人感到困惑。 - Matthias Gwiozda

Answer 2

使用字符串替换函数。

select replace(json, ' ','') from table;

如果您的数据中有空格，这个方法将无法正常工作。

- Lighthart

---

在你添加了查询之后，ruakh发布了一个非常好的答案。请阅读他的回答。 - Lighthart

Answer 3

最好使用JSON类型： SELECT CAST(`column` AS JSON) WHERE JSON_EXTRACT(CAST(`column` as JSON),'$."4d1dfd2e-7bc1-4303-9c8c-90856e918bb9".item."0"')="11"

- Crusader