MySQL REGEXP + 空格 (\s)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15558172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL REGEXP + whitespace (\s)
提问by Tommy Plummer
The database I work with stores JSON entries in LONGTEXT datatypes. I want to be able to SELECT entries depending on the JSON data. Here's some example data:
我使用的数据库以 LONGTEXT 数据类型存储 JSON 条目。我希望能够根据 JSON 数据选择条目。以下是一些示例数据：
{
"12f9cb0a-2218-4590-a05d-c1ffab00f693": {
"0": {
"value": "test"
}
},
"4d1dfd2e-7bc1-4303-9c8c-90856e918bb9": {
"item": {
"0": "11"
}
}
}
So I want to select data that contains "4d1dfd2e-7bc1-4303-9c8c-90856e918bb9": { "item": { "0":"11" } }by filtering out the whitespace (tabs, spaces, new lines) using the REGEXP function, I have tried this to no avail:
所以我想选择包含"4d1dfd2e-7bc1-4303-9c8c-90856e918bb9": { "item": { "0":"11" } }通过使用REGEXP 函数，我试过无济于事：
SELECT * FROM my_table WHERE (elements REGEXP BINARY '"4d1dfd2e-7bc1-4303-9c8c-90856e918bb9":\s*{\s*"item":\s*{\s*"0":\s*"11"\s*}\s*}');
The regex test works using Rubular and Regexpal.com but MYSQL doesn't seem to like the \s*expression. Does anyone have a better solution for this?
正则表达式测试使用 Rubular 和 Regexpal.com 工作，但 MYSQL 似乎不喜欢\s*表达式。有没有人对此有更好的解决方案？
回答by ruakh
Before MySQL 8.0, MySQL regexes only support the notations listed in §12.7.2 "Regular Expressions" of the MySQL 5.7 Reference Manual(or counterpart for the appropriate version), which include the [[:space:]]notation but not the \snotation. So, you just need to replace each occurrence of \swith [[:space:]].
在 MySQL 8.0 之前，MySQL 正则表达式仅支持MySQL 5.7 参考手册（或相应版本的对应版本）的第 12.7.2 节“正则表达式”中列出的表示法，其中包括[[:space:]]表示法但不支持\s表示法。所以，你只需要更换的每次出现\s用[[:space:]]。
In MySQL 8.0 and later, MySQL uses the regex support of International Components for Unicode [link], which does include \s[link]; but because MySQL itself uses \as the escape character within strings, you need to double the backslash, replacing each occurrence of \swith \\s.
在 MySQL 8.0 及更高版本中，MySQL 使用 International Components for Unicode [ link]的正则表达式支持，其中包括\s[ link]；但是因为 MySQL 本身\在字符串中用作转义字符，所以您需要将反斜杠加倍，\s用\\s.
回答by Lighthart
Use string replace;
使用字符串替换；
select replace(json, ' ','') from table;
If your data has spaces in it, this won't work.
如果您的数据中有空格，这将不起作用。
回答by Crusader
It is better to use json type:
SELECT CAST(`column` AS JSON)
WHERE JSON_EXTRACT(CAST(`column` as JSON),'$."4d1dfd2e-7bc1-4303-9c8c-90856e918bb9".item."0"')="11"
最好使用 json 类型：
SELECT CAST(`column` AS JSON)
WHERE JSON_EXTRACT(CAST(`column` as JSON),'$."4d1dfd2e-7bc1-4303-9c8c-90856e918bb9".item."0"')="11"
