MySQL REGEXP + whitespace (\s)

Question

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:

 {
    "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:

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?

Answer 1

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 \s notation. So, you just need to replace each occurrence of \s with [[: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 \s with \\s.