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 数据类型的操作。
<!--more-->
增加数据
增加数据这块是比较简单，不过须要了解 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.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_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.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_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: 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() 不反对数字查找这个也是一个不小的坑了。