谈谈 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.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,