phpjson取值为技巧_谈谈 MySQL 的 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 创建函数进行创建。

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.jsconst MySQL = require('think-model-mysql');exports.model = { type: 'mysql', mysql: { handle: MySQL, ... jsonFormat: true }};复制代码

//user.jsmodule.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 数据中的部分内容，并按照正常的表字段进行返回。
这块可以利用 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 一样可以接前缀和后缀，比如 ab 表示的因此 a 开头，b结尾的路径。

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

//user.jsmodule.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.jsmodule.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_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: 0mysql> 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.jsmodule.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: 0mysql> 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.jsmodule.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: 0mysql> 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: 0mysql> 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.jsmodule.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() 不支持数字查找这个也是一个不小的坑了。

链接：https://juejin.im/post/6872156239841722376