乐趣区

关于thinkjs:谈谈-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 数据类型的操作。

<!–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.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_SETJSON_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_INSERTJSON_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() 不反对数字查找这个也是一个不小的坑了。

退出移动版