谈谈 MySQL 的 JSON 数据类型操作

曾经终败给现在 2022-12-07 02:19 207阅读 0赞

编者按：本文作者李喆明，奇舞团前端开发工程师

MySQL 5.7 增加了 JSON 数据类型的支持，在之前如果要存储 JSON 类型的数据的话我们只能自己做 JSON.stringify() 和 JSON.parse() 的操作，而且没办法针对 JSON 内的数据进行查询操作，所有的操作必须读取出来 parse 之后进行，非常的麻烦。原生的 JSON 数据类型支持之后，我们就可以直接对 JSON 进行数据查询和修改等操作了，较之前会方便非常多。

为了方便演示我先创建一个 user 表，其中 info 字段用来存储用户的基础信息。要将字段定义成 JSON 类型数据非常简单，直接字段名后接 JSON 即可。

>     CREATE TABLE user (
>       id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
>       name VARCHAR(30) NOT NULL,
>       info JSON
>     );

表创建成功之后我们就按照经典的 CRUD 数据操作来讲讲怎么进行 JSON 数据类型的操作。

## 添加数据 ##

添加数据这块是比较简单，不过需要理解 MySQL 对 JSON 的存储本质上还是字符串的存储操作。只是当定义为 JSON 类型之后内部会对数据再进行一些索引的创建方便后续的操作而已。所以添加 JSON 数据的时候需要使用字符串包装。

>     mysql> INSERT INTO user (`name`, `info`) VALUES('lilei', '{"sex": "male", "age": 18, "hobby": ["basketball", "football"], "score": [85, 90, 100]}');
>     Query OK, 1 row affected (0.00 sec)

除了自己拼 JSON 之外，你还可以调用 MySQL 的 JSON 创建函数进行创建。

*  JSON\_OBJECT：快速创建 JSON 对象，奇数列为 key，偶数列为 value，使用方法 JSON\_OBJECT(key,value,key1,value1)
 *  JSON\_ARRAY：快速创建 JSON 数组，使用方法 JSON\_ARRAY(item0, item1, item2)

>     mysql> INSERT INTO user (`name`, `info`) VALUES('hanmeimei', JSON_OBJECT(
>         -> 'sex', 'female',
>         -> 'age', 18,
>         -> 'hobby', JSON_ARRAY('badminton', 'sing'),
>         -> 'score', JSON_ARRAY(90, 95, 100)
>         -> ));
>     Query OK, 1 row affected (0.00 sec)

不过对于 JavaScript 工程师来说不管是使用字符串来写还是使用自带函数来创建 JSON 都是非常麻烦的一件事，远没有 JS 原生对象来的好用。所以在 think-model 模块中我们增加了 JSON 数据类型的数据自动进行 JSON.stringify() 的支持，所以直接传入 JS 对象数据即可。

由于数据的自动序列化和解析是根据字段类型来做的，为了不影响已运行的项目，需要在模块中配置 jsonFormat: true 才能开启这项功能。

>     //adapter.js
>     const MySQL = require('think-model-mysql');
>     exports.model = {
>       type: 'mysql',
>       mysql: {
>         handle: MySQL,
>         ...
>         jsonFormat: true
>       }
>     };

>     //user.js
>     module.exports = class extends think.Controller {
>       async indexAction() {
>         const userId = await this.model('user').add({
>           name: 'lilei',
>           info: {
>             sex: 'male',
>             age: 16,
>             hobby: ['basketball', 'football'],
>             score: [85, 90, 100]
>           }
>         });
>         return this.success(userId);
>       }
>     }

下面让我们来看看最终存储到数据库中的数据是什么样的

>     mysql> SELECT * FROM `user`;
>     +----+-----------+-----------------------------------------------------------------------------------------+
>     | id | name      | info                                                                                    |
>     +----+-----------+-----------------------------------------------------------------------------------------+
>     | 1  | lilei     | {"age": 18, "sex": "male", "hobby": ["basketball", "football"], "score": [85, 90, 100]} |
>     | 2  | hanmeimei | {"age": 18, "sex": "female", "hobby": ["badminton", "sing"], "score": [90, 95, 100]}    |
>     +----+-----------+-----------------------------------------------------------------------------------------+
>     2 rows in set (0.00 sec)

## 查询数据 ##

为了更好的支持 JSON 数据的操作，MySQL 提供了一些 JSON 数据操作类的方法。和查询操作相关的方法主要如下：

*  JSON\_EXTRACT()：根据 Path 获取部分 JSON 数据，使用方法 JSON\_EXTRACT(json\_doc, path\[, path\] ...)
 *  \->：JSON\_EXTRACT() 的等价写法
 *  \->>：JSON\_EXTRACT() 和 JSON\_UNQUOTE() 的等价写法
 *  JSON\_CONTAINS()：查询 JSON 数据是否在指定 Path 包含指定的数据，包含则返回1，否则返回0。使用方法 JSON\_CONTAINS(json\_doc, val\[, path\])
 *  JSON\_CONTAINS\_PATH()：查询是否存在指定路径，存在则返回1，否则返回0。one\_or\_all 只能取值 "one" 或 "all"，one 表示只要有一个存在即可，all 表示所有的都存在才行。使用方法 JSON\_CONTAINS\_PATH(json\_doc, one\_or\_all, path\[, path\] ...)
 *  JSON\_KEYS()：获取 JSON 数据在指定路径下的所有键值。使用方法 JSON\_KEYS(json\_doc\[, path\])，类似 JavaScript 中的 Object.keys() 方法。
 *  JSON\_SEARCH()：查询包含指定字符串的 Paths，并作为一个 JSON Array 返回。查询的字符串可以用 LIKE 里的 '%' 或 '\_' 匹配。使用方法 JSON\_SEARCH(json\_doc, one\_or\_all, search\_str\[, escape\_char\[, path\] ...\])，类似 JavaScript 中的 findIndex() 操作。

我们在这里不对每个方法进行逐个的举例描述，仅提出一些场景举例应该怎么操作。

### 返回用户的年龄和性别 ###

举这个例子就是想告诉下大家怎么获取 JSON 数据中的部分内容，并按照正常的表字段进行返回。这块可以使用 JSON\_EXTRACT 或者等价的 -> 操作都可以。其中根据例子可以看到 sex 返回的数据都带有引号，这个时候可以使用 JSON\_UNQUOTE() 或者直接使用 ->> 就可以把引号去掉了。

>     mysql> SELECT `name`, JSON_EXTRACT(`info`, '$.age') as `age`, `info`->'$.sex' as sex FROM `user`;
>     +-----------+------+----------+
>     | name      | age  | sex      |
>     +-----------+------+----------+
>     | lilei     | 18   | "male"   |
>     | hanmeimei | 16   | "female" |
>     +-----------+------+----------+
>     2 rows in set (0.00 sec)

这里我们第一次接触到了 Path 的写法，MySQL 通过这种字符串的 Path 描述帮助我们映射到对应的数据。和 JavaScript 中对象的操作比较类似，通过 . 获取下一级的属性，通过 \[\] 获取数组元素。

不一样的地方在于需要通过 $ 表示本身，这个也比较好理解。另外就是可以使用 \* 和 \*\* 两个通配符，比如 .\* 表示当前层级的所有成员的值，\[\*\] 则表示当前数组中所有成员值。\*\* 类似 LIKE 一样可以接前缀和后缀，比如 a\*\*b 表示的是以 a 开头，b结尾的路径。

路径的写法非常简单，后面的内容里也会出现。上面的这个查询对应在 think-model 的写法为

>     //user.js
>     module.exports = class extends think.Controller {
>       async indexAction() {
>         const userModel = this.model('user');
>         const field = "name, JSON_EXTRACT(info, '$.age') AS age, info->'$.sex' as sex";
>         const users = await userModel.field(field).where('1=1').select();
>         return this.success(users);
>       }
>     }

### 返回喜欢篮球的男性用户 ###

>     mysql> SELECT `name` FROM `user` WHERE JSON_CONTAINS(`info`, '"male"', '$.sex') AND JSON_SEARCH(`info`, 'one', 'basketball', null, '$.hobby');
>     +-------+
>     | name  |
>     +-------+
>     | lilei |
>     +-------+
>     1 row in set, 1 warning (0.00 sec)

这个例子就是简单的告诉大家怎么对属性和数组进行查询搜索。其中需要注意的是 JSON\_CONTAINS() 查询字符串由于不带类型转换的问题字符串需要使用加上 "" 包裹查询，或者使用 JSON\_QUOTE('male') 也可以。

如果你使用的是 MySQL 8 的话，也可以使用新增的 JSON\_VALUE() 来代替 JSON\_CONTAINS()，新方法的好处是会带类型转换，避免刚才双引号的尴尬问题。不需要返回的路径的话，JSON\_SEARCH() 在这里也可以使用新增的 MEMBER OF 或者 JSON\_OVERLAPS() 方法替换。

>     mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND 'basketball' MEMBER OF(JSON_VALUE(`info`, '$.hobby'));
>     +-------+
>     | name  |
>     +-------+
>     | lilei |
>     +-------+
>     1 row in set (0.00 sec)
>     mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'));
>     +-------+
>     | name  |
>     +-------+
>     | lilei |
>     +-------+
>     1 row in set (0.00 sec)

上面的这个查询对应在 think-model 的写法为

>     //user.js
>     module.exports = class extends think.Controller {
>       async indexAction() {
>         const userModel = this.model('user');
>         const where = {
>           _string: [
>             "JSON_CONTAINS(info, '\"male\"', '$.sex')",
>             "JSON_SEARCH(info, 'one', 'basketball', null, '$.hobby')"
>           ]
>         };
>         const where1 = {
>           _string: [
>             "JSON_VALUE(`info`, '$.sex') = 'male'",
>             "'basketball' MEMBER OF (JSON_VALUE(`info`, '$.hobby'))"
>           ]
>         };
>         const where2 = {
>           _string: [
>             "JSON_VALUE(`info`, '$.sex') = 'male'",
>             "JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'))"
>           ]
>         }
>         const users = await userModel.field('name').where(where).select();
>         return this.success(users);
>       }
>     }

## 修改数据 ##

MySQL 提供的 JSON 操作函数中，和修改操作相关的方法主要如下：

*  JSON\_APPEND/JSON\_ARRAY\_APPEND：这两个名字是同一个功能的两种叫法，MySQL 5.7 的时候为 JSON\_APPEND，MySQL 8 更新为 JSON\_ARRAY\_APPEND，并且之前的名字被废弃。该方法如同字面意思，给数组添加值。使用方法 JSON\_ARRAY\_APPEND(json\_doc, path, val\[, path, val\] ...)
 *  JSON\_ARRAY\_INSERT：给数组添加值，区别于 JSON\_ARRAY\_APPEND() 它可以在指定位置插值。使用方法 JSON\_ARRAY\_INSERT(json\_doc, path, val\[, path, val\] ...)
 *  JSON\_INSERT/JSON\_REPLACE/JSON\_SET：以上三个方法都是对 JSON 插入数据的，他们的使用方法都为 JSON\_\[INSERT|REPLACE|SET\](json\_doc, path, val\[, path, val\] ...)，不过在插入原则上存在一些差别。
 *   *  JSON\_INSERT：当路径不存在才插入
     *  JSON\_REPLACE：当路径存在才替换
     *  JSON\_SET：不管路径是否存在
 *  JSON\_REMOVE：移除指定路径的数据。使用方法 JSON\_REMOVE(json\_doc, path\[, path\] ...)

由于 JSON\_INSERT, JSON\_REPLACE, JSON\_SET 和 JSON\_REMOVE 几个方法支持属性和数组的操作，所以前两个 JSON\_ARRAY 方法用的会稍微少一点。下面我们根据之前的数据继续举几个实例看看。

### 修改用户的年龄 ###

>     mysql> UPDATE `user` SET `info` = JSON_REPLACE(`info`, '$.age', 20) WHERE `name` = 'lilei';
>     Query OK, 1 row affected (0.00 sec)
>     Rows matched: 1 Changed: 1 Warnings: 0
>     mysql> SELECT JSON_VALUE(`info`, '$.age') as age FROM `user` WHERE `name` = 'lilei';
>     +------+
>     | age  |
>     +------+
>     | 20   |
>     +------+
>     1 row in set (0.00 sec)

JSON\_INSERT 和 JSON\_SET 的例子也是类似，这里就不多做演示了。对应到 think-model 中的话，需要使用 EXP 条件表达式处理，对应的写法为

>     //user.js
>     module.exports = class extends think.Controller {
>       async indexAction() {
>         const userModel = this.model('user');
>         await userModel.where({name: 'lilei'}).update({
>           info: ['exp', "JSON_REPLACE(info, '$.age', 20)"]
>         });
>         return this.success();
>       }
>     }

### 修改用户的爱好 ###

>     mysql> UPDATE `user` SET `info` = JSON_ARRAY_APPEND(`info`, '$.hobby', 'badminton') WHERE `name` = 'lilei';
>     Query OK, 1 row affected (0.00 sec)
>     Rows matched: 1 Changed: 1 Warnings: 0
>     mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei';
>     +-----------------------------------------+
>     | hobby                                   |
>     +-----------------------------------------+
>     | ["basketball", "football", "badminton"] |
>     +-----------------------------------------+
>     1 row in set (0.00 sec)

JSON\_ARRAY\_APPEND 在对数组进行操作的时候还是要比 JSON\_INSERT 之类的方便的，起码你不需要知道数组的长度。对应到 think-model 的写法为

>     //user.js
>     module.exports = class extends think.Controller {
>       async indexAction() {
>         const userModel = this.model('user');
>         await userModel.where({name: 'lilei'}).update({
>           info: ['exp', "JSON_ARRAY_APPEND(info, '$.hobby', 'badminton')"]
>         });
>         return this.success();
>       }
>     }

### 删除用户的分数 ###

>     mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, '$.score[0]') WHERE `name` = 'lilei';
>     Query OK, 1 row affected (0.00 sec)
>     Rows matched: 1 Changed: 1 Warnings: 0
>     mysql> SELECT `name`, JSON_VALUE(`info`, '$.score') as score FROM `user` WHERE `name` = 'lilei';
>     +-------+-----------+
>     | name  | score     |
>     +-------+-----------+
>     | lilei | [90, 100] |
>     +-------+-----------+
>     1 row in set (0.00 sec)

删除这块和之前修改操作类似，没有什么太多需要说的。但是对数组进行操作很多时候我们可能就是想删值，但是却不知道这个值的 Path 是什么。这个时候就需要利用之前讲到的 JSON\_SEARCH() 方法，它是根据值去查找路径的。比如说我们要删除 lilei 兴趣中的 badminton 选项可以这么写。

>     mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton'))) WHERE `name` = 'lilei';
>     Query OK, 1 row affected (0.00 sec)
>     Rows matched: 1 Changed: 1 Warnings: 0
>     mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei';
>     +----------------------------+
>     | hobby                      |
>     +----------------------------+
>     | ["basketball", "football"] |
>     +----------------------------+
>     1 row in set (0.00 sec)

这里需要注意由于 JSON\_SEARCH 不会做类型转换，所以匹配出来的路径字符串需要进行 JSON\_UNQUOTE() 操作。另外还有非常重要的一点是 JSON\_SEARCH 无法对数值类型数据进行查找，也不知道这个是 Bug 还是 Feature。这也是为什么我没有使用 score 来进行举例而是换成了 hobby 的原因。如果数值类型的话目前只能取出来在代码中处理了。

>     mysql> SELECT JSON_VALUE(`info`, '$.score') FROM `user` WHERE `name` = 'lilei';
>     +-------------------------------+
>     | JSON_VALUE(`info`, '$.score') |
>     +-------------------------------+
>     | [90, 100]                     |
>     +-------------------------------+
>     1 row in set (0.00 sec)
>     mysql> SELECT JSON_SEARCH(`info`, 'one', 90, null, '$.score') FROM `user` WHERE `name` = 'lilei';
>     +-------------------------------------------------+
>     | JSON_SEARCH(`info`, 'one', 90, null, '$.score') |
>     +-------------------------------------------------+
>     | NULL                                            |
>     +-------------------------------------------------+
>     1 row in set (0.00 sec)

以上对应到 think-model 的写法为

>     //user.js
>     module.exports = class extends think.Controller {
>       async indexAction() {
>         const userModel = this.model('user');
>         // 删除分数
>         await userModel.where({name: 'lilei'}).update({
>           info: ['exp', "JSON_REMOVE(info, '$.score[0]')"]
>         });
>         // 删除兴趣
>         await userModel.where({name: 'lilei'}).update({
>           info: ['exp', "JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton')))"]
>         });
>         return this.success();
>       }
>     }

## 后记 ##

由于最近有一个需求，有一堆数据，要记录这堆数据的排序情况，方便根据排序进行输出。一般情况下肯定是给每条数据增加一个 order 字段来记录该条数据的排序情况。但是由于有着批量操作，在这种时候使用单字段去存储会显得特别麻烦。在服务端同事的建议下，我采取了使用 JSON 字段存储数组的情况来解决这个问题。

也因为这样了解了一下 MySQL 对 JSON 的支持情况，同时将 think-model 做了一些优化，对 JSON 数据类型增加了支持。由于大部分 JSON 操作需要通过内置的函数来操作，这个本身是可以通过 EXP 条件表达式来完成的。所以只需要对 JSON 数据的添加和查询做好优化就可以了。

整体来看，配合提供的 JSON 操作函数，MySQL 对 JSON 的支持完成一些日常的需求还是没有问题的。除了作为 WHERE 条件以及查询字段之外，其它的 ORDER, GROUP, JOIN 等操作也都是支持 JSON 数据的。

不过对比 MongoDB 这种天生支持 JSON 的话，在操作性上还是要麻烦许多。特别是在类型转换这块，使用一段时间后发现非常容易掉坑。什么时候会带引号，什么时候会不带引号，什么时候需要引号，什么时候不需要引号，这些都容易让新手发憷。另外 JSON\_SEARCH() 不支持数字查找这个也是一个不小的坑了。

## 关于奇舞周刊 ##

《奇舞周刊》是360公司专业前端团队「奇舞团」运营的前端技术社区。关注公众号后，直接发送链接到后台即可给我们投稿。

![format_png][]

[format_png]: /images/20221123/492ea1cf916645c4b0e74434bc6e256a.png