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: 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() 不支持数字查找这个也是一个不小的坑了。
相关免费学习推荐：mysql视频教程
以上就是mysql 的 json 数据类型的详细内容。