MySQL的JSON数据类型有哪些
小编给大家分享一下MySQL的JSON 数据类型有哪些,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!
专注于为中小企业提供成都网站制作、网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业灵宝免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了成百上千家企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
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.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: 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.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: 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.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: 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.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()
不支持数字查找这个也是一个不小的坑了。
以上是MySQL的JSON 数据类型有哪些的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!
文章题目:MySQL的JSON数据类型有哪些
标题链接:http://myzitong.com/article/pecjsj.html