MySQL 反斜杠

昨天，我写了一篇博文，向您展示了如何编写返回 JSON 结构的查询，以实现 1对多 关系。关系是在成员和联系表之间。它从成员表中返回一个account_number ，并以 JSON 结构从联系人表中返回first_name和last_name列的列表。

我的一个学生问我为什么选择用 Python 去除反斜杠，我的回答是 SQL 对于大多数博客读者来说已经很复杂了。学生问，但是你将如何在 SQL 中做到这一点。好的，这是一个公平的问题，原因有两个。首先，您不需要在本地程序中进行操作，因为它会在服务器上运行得更快。其次，如果您去掉反斜杠，您可以将其插入到标准 JSON 列中。这篇博文将向您展示如何做到这两点。

您将使用三个REGEXP_REPLACE函数调用，例如：

SELECT   REGEXP_REPLACE(
           REGEXP_REPLACE(
             REGEXP_REPLACE(
               JSON_OBJECT(
                  'account_number', account_number
                 ,'contact', CONCAT('['
                               , GROUP_CONCAT(
                                    JSON_OBJECT('first_name',first_name
                                   ,'last_name',last_name ) SEPARATOR ',')
                               ,']')
               )
               ,'\\\\','')
             ,'"\\\[','\\\[')
           ,'\\\]"','\\\]') AS json_result 
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id
GROUP BY m.account_number;
复制

它返回以下内容：

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"contact": [{"last_name": "Winn", "first_name": "Randi"},{"last_name": "Winn", "first_name": "Brian"}], "account_number": "US00001"}                                                         |
| {"contact": [{"last_name": "Vizquel", "first_name": "Oscar"},{"last_name": "Vizquel", "first_name": "Doreen"}], "account_number": "US00002"}                                                  |
| {"contact": [{"last_name": "Sweeney", "first_name": "Meaghan"},{"last_name": "Sweeney", "first_name": "Matthew"},{"last_name": "Sweeney", "first_name": "Ian"}], "account_number": "US00003"} |
| {"contact": [{"last_name": "Clinton", "first_name": "Goeffrey"}], "account_number": "US00004"}                                                                                                |
| {"contact": [{"last_name": "Moss", "first_name": "Wendy"}], "account_number": "US00005"}                                                                                                      |
| {"contact": [{"last_name": "Gretelz", "first_name": "Simon"}], "account_number": "US00006"}                                                                                                   |
| {"contact": [{"last_name": "Royal", "first_name": "Elizabeth"}], "account_number": "US00007"}                                                                                                 |
| {"contact": [{"last_name": "Smith", "first_name": "Brian"}], "account_number": "US00008"}                                                                                                     |
| {"contact": [{"last_name": "Potter", "first_name": "Harry"},{"last_name": "Potter", "first_name": "Ginny"},{"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"}       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
复制

让我们使用以下脚本创建一个具有 JSON 结构的表：

/* Drop table if it exists. */
DROP TABLE IF EXISTS example;
 
/* Create a example table. */
CREATE TABLE example
( id      int unsigned  auto_increment
, struct  json 
, PRIMARY KEY (id));
复制

现在，我们可以将查询嵌入到INSERT语句中：

INSERT
INTO   example
( struct )
(SELECT   REGEXP_REPLACE(
            REGEXP_REPLACE(
              REGEXP_REPLACE(
                JSON_OBJECT(
                   'account_number', account_number
                  ,'contact', CONCAT('['
                                , GROUP_CONCAT(
                                     JSON_OBJECT('first_name',first_name
                                    ,'last_name',last_name ) SEPARATOR ',')
                                ,']')
                )
                ,'\\\\','')
              ,'"\\\[','\\\[')
            ,'\\\]"','\\\]') AS json_result 
FROM     member m INNER JOIN contact c
ON       m.member_id = c.member_id
GROUP BY m.account_number);
复制

示例表的查询，例如：

SELECT * FROM example;
复制

返回：

+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | struct                                                                                                                                                                                          |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | {"contact": [{"last_name": "Winn", "first_name": "Randi"}, {"last_name": "Winn", "first_name": "Brian"}], "account_number": "US00001"}                                                          |
|  2 | {"contact": [{"last_name": "Vizquel", "first_name": "Oscar"}, {"last_name": "Vizquel", "first_name": "Doreen"}], "account_number": "US00002"}                                                   |
|  3 | {"contact": [{"last_name": "Sweeney", "first_name": "Meaghan"}, {"last_name": "Sweeney", "first_name": "Matthew"}, {"last_name": "Sweeney", "first_name": "Ian"}], "account_number": "US00003"} |
|  4 | {"contact": [{"last_name": "Clinton", "first_name": "Goeffrey"}], "account_number": "US00004"}                                                                                                  |
|  5 | {"contact": [{"last_name": "Moss", "first_name": "Wendy"}], "account_number": "US00005"}                                                                                                        |
|  6 | {"contact": [{"last_name": "Gretelz", "first_name": "Simon"}], "account_number": "US00006"}                                                                                                     |
|  7 | {"contact": [{"last_name": "Royal", "first_name": "Elizabeth"}], "account_number": "US00007"}                                                                                                   |
|  8 | {"contact": [{"last_name": "Smith", "first_name": "Brian"}], "account_number": "US00008"}                                                                                                       |
|  9 | {"contact": [{"last_name": "Potter", "first_name": "Harry"}, {"last_name": "Potter", "first_name": "Ginny"}, {"last_name": "Potter", "first_name": "Lily"}], "account_number": "US00011"}       |
+----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
复制

我希望这能回答有关是否可以使用 SQL 从原始结果集中删除反斜杠以及如何将 JSON 结果集插入 JSON 数据类型的问题。

原文标题：MySQL Backslashes
原文作者：MacLochlainns
原文地址：https://blog.mclaughlinsoftware.com/2022/06/23/mysql-backslashes/