MySQL's JSON data type

mysql video tutorial column introduces the JSON data type.

MySQL 5.7 adds support for the JSON data type. Previously, if we wanted to store JSON type data, we could only do it ourselvesJSON.stringify( ) and JSON.parse() operations, and there is no way to perform query operations on the data in JSON. All operations must be read and parseed, which is very troublesome. After the native JSON data type is supported, we can directly perform data query and modification operations on JSON, which is much more convenient than before.

For the convenience of demonstration, I first create a user table, in which the info field is used to store the user's basic information. It is very simple to define a field as JSON type data, just directly follow the field name with JSON.

CREATE TABLE user (
  id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL,
  info JSON
);复制代码

After the table is successfully created, we will talk about how to operate the JSON data type according to the classic CRUD data operation.

Add data

Adding data is relatively simple, but you need to understand that MySQL's storage of JSON is essentially a string storage operation. It's just that when it is defined as JSON type, some indexes will be created internally to facilitate subsequent operations. So you need to use string packaging when adding JSON data.

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)复制代码

In addition to spelling JSON yourself, you can also call MySQL's JSON creation function to create it.

• JSON_OBJECT: Quickly create a JSON object, with odd columns as key and even columns as value. The usage method is JSON_OBJECT(key,value,key1,value1)
• JSON_ARRAY: Quickly create a JSON array, use the method 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)复制代码

But for JavaScript engineers It is said that whether you use strings to write or use built-in functions to create JSON, it is a very troublesome thing, and it is far less easy to use than JS native objects. Therefore, in the think-model module, we have added support for automatic processing of JSON data type data JSON.stringify(), so JS object data can be passed in directly.

Since the automatic serialization and parsing of data is done based on the field type, in order not to affect the running project, you need to configure jsonFormat: true in the module to enable this function.

//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);
  }
}复制代码

Let’s take a look at what the data ultimately stored in the database looks like

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)复制代码

Query data

In order to better support the operation of JSON data, MySQL Provides some JSON data manipulation class methods. The methods related to query operations are mainly as follows:

• JSON_EXTRACT(): To obtain part of JSON data based on Path, use the method JSON_EXTRACT(json_doc, path[, path]. ..)
• ##->: The equivalent of JSON_EXTRACT()
• ->>: The equivalent of JSON_EXTRACT() and JSON_UNQUOTE()
• JSON_CONTAINS(): Query whether the JSON data is included in the specified Path If the specified data is included, 1 is returned, otherwise 0 is returned. Usage method JSON_CONTAINS(json_doc, val[, path])
• JSON_CONTAINS_PATH(): Query whether the specified path exists, return 1 if it exists, otherwise return 0. one_or_all Can only take the value "one" or "all", one means that only one exists, and all means that all of them exist. Usage method JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
• JSON_KEYS(): Get all key values ​​of JSON data under the specified path . The usage method JSON_KEYS(json_doc[, path]) is similar to the Object.keys() method in JavaScript.
• JSON_SEARCH(): Query the Paths containing the specified string and return it as a JSON Array. The query string can be matched using '%' or '_' in LIKE. The usage method JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...]) is similar to the findIndex() operation in JavaScript.

We will not describe each method one by one with examples here, but only give some examples of how to operate it in some scenarios.

Returning the user's age and gender

The purpose of this example is to tell you how to get part of the JSON data and return it according to the normal table fields. This block can use

JSON_EXTRACT or equivalent -> operations. According to the example, you can see that the data returned by sex is in quotation marks. At this time, you can use JSON_UNQUOTE() or directly use ->>. Removed the quotation marks.

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视频教程

The above is the detailed content of MySQL's JSON data type. For more information, please follow other related articles on the PHP Chinese website!