1
前言
MySQL支持由 RFC 7159 定义的原生JSON 数据类型，该数据类型可以有效访问 JSON（JavaScript Object Notation）中的元素数据。与将JSON 格式的字符串存储为单个字符串类型相比，JSON 数据类型具有以下优势：
- 自动验证存储在JSON列中的JSON数据格式。无效格式会报错。
- 优化的存储格式。存储在JSON列中的JSON文档被转换为允许快速读取访问文档元素的内部格式。内部是以二进制格式存储JSON数据。
- 对JSON文档元素的快速读取访问。当服务器读取JSON文档时，不需要重新解析文本获取该值。通过键或数组索引直接查找子对象或嵌套值，而不需要读取整个JSON文档。
- 存储JSON文档所需的空间，大致与LONGBLOB或LONGTEXT相同
- 存储在JSON列中的任何JSON文档的大小都仅限于设置的系统变量maxallowedpacket的值
- MySQL 8.0.13之前，JSON列不能有非null的默认值。
- 在 MySQL 8.0 中，优化器可以对 JSON 列执行部分就地更新，而不是删除旧文档并将新文档完整地写入列。
MYSQL 8.0，除了提供JSON 数据类型，还有一组 SQL 函数可用于操作 JSON 的值，例如创建JSON对象、增删改查JSON数据中的某个元素。另外，关注终码一生公众号，回复“资料”，送你面试题宝典和大量视频资源下载！
MySQL是结构化数据存储，JSON是非结构化格式，在MySQL中使用JSON类型可以打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择，以下内容包括
（1）JSON 数据类型
（2）JSON类型创建插入数据
（3）提取JSON内字段
（4）JSON类型和字符串的区别
（5）JSON类型数据修改
（6）JSON类型使用索引
（7）JSON类型其他常用函数
（8）JSON ARRAY的多值索引
（9）基于JSON类型的用户画像设计
（1）JSON 数据类型
JSON（JavaScript Object Notation）主要用于互联网应用服务之间的数据交换。MySQL 支持JSON 对象和JSON 数组两种类型，JSON 类型是从 MySQL 5.7 版本开始支持的功能，MySQL中使用JSON有以下好处
无须预定义字段：字段可以无限拓展，避免了ALTER ADD COLUMN的操作，使用更加灵活
处理稀疏字段：避免了稀疏字段的NULL值，避免冗余存储
支持索引：相比于字符串格式的JSON，JSON类型支持索引做特定的查询优化
总体而言，JSON 类型比较适合存储一些修改较少、相对静态的数据，或者说适合存储修改较少，且容忍稀疏的聚合数据，比如存储用户的登录信息
（2）JSON类型创建插入数据
create database test default charset utf8mb4;
CREATE TABLE student (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
info JSON DEFAULT NULL
);
插入数据
mysql> INSERT student (info) VALUES ('{"sex": "F", "age": 13, "city": "beijing"}');
Query OK, 1 row affected (0.13 sec)
mysql> INSERT student (info) VALUES ('{"sex": "M", "age": 14, "city": "suzhou"}');
Query OK, 1 row affected (0.10 sec)
mysql> INSERT student (info) VALUES ('{"sex": "F", "age": 23, "city": "shenzhen"}');
Query OK, 1 row affected (0.20 sec)
查看数据
mysql> select * from student;
+----+---------------------------------------------+
| id | info |
+----+---------------------------------------------+
| 1 | {"age": 13, "sex": "F", "city": "beijing"} |
| 2 | {"age": 14, "sex": "M", "city": "suzhou"} |
| 3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
+----+---------------------------------------------+
试一下插入JSON数组
mysql> INSERT student (info) VALUES ('[1,2,3,4]');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT student (info) VALUES ('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
Query OK, 1 row affected (0.11 sec)
mysql> select * from student;
+----+-------------------------------------------------+
| id | info |
+----+-------------------------------------------------+
| 1 | {"age": 13, "sex": "F", "city": "beijing"} |
| 2 | {"age": 14, "sex": "M", "city": "suzhou"} |
| 3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
| 4 | [1, 2, 3, 4] |
| 5 | [{"sex": "M"}, {"sex": "F", "city": "nanjing"}] |
+----+-------------------------------------------------+
5 rows in set (0.00 sec)
可以混合插入JSON数组和JSON对象，但是必须要符合JSON格式
其中整个字符串使用单引号，键值对使用双引号，
JSON中可以允许有多余空格，MySQL会自动解析，输出的使用格式为符号后带有一个空格
（3）提取JSON内字段
因为支持了新的JSON类型，MySQL 配套提供了丰富的 JSON 字段处理函数，用于方便地操作 JSON 数据，最常见的就是函数 JSON_EXTRACT，它用来从 JSON 数据中提取所需要的字段内容
（1）提取JSON对象
主要是JSON_UNQUOTE和JSON_EXTRACT，JSON_EXTRACT作用是去除最外侧的双引号，JSON_EXTRACT根据键提取值
mysql> SELECT
-> id,
-> JSON_UNQUOTE(JSON_EXTRACT(info,"$.sex")) sex,
-> JSON_UNQUOTE(JSON_EXTRACT(info,"$.age")) age,
-> JSON_UNQUOTE(JSON_EXTRACT(info,"$.city")) city
-> FROM student;
+----+------+------+----------+
| id | sex | age | city |
+----+------+------+----------+
| 1 | F | 13 | beijing |
| 2 | M | 14 | suzhou |
| 3 | F | 23 | shenzhen |
+----+------+------+----------+
3 rows in set (0.00 sec)
MySQL 还提供了 ->> 表达式，和上述 SQL 效果完全一样，->>也是去除最外面的引号，还有一种符号->，它也能得到提取结果但是不去除外面的符号，相当于->和JSON_EXTRACT对应
mysql> SELECT
-> id,
-> info->>"$.sex" sex,
-> info->>"$.age" age,
-> info->>"$.city" city
-> FROM student;
+----+------+------+----------+
| id | sex | age | city |
+----+------+------+----------+
| 1 | F | 13 | beijing |
| 2 | M | 14 | suzhou |
| 3 | F | 23 | shenzhen |
+----+------+------+----------+
3 rows in set (0.00 sec)
如果JSON对象中查询的键不存在，则返回为NULL
mysql> SELECT
-> id,
-> JSON_UNQUOTE(JSON_EXTRACT(info,"$.sex")) sex,
-> JSON_UNQUOTE(JSON_EXTRACT(info,"$.height")) height
-> FROM student;
+----+------+--------+
| id | sex | height |
+----+------+--------+
| 1 | F | NULL |
| 2 | M | NULL |
| 3 | F | NULL |
+----+------+--------+
3 rows in set (0.00 sec)
（2）提取JSON数组
先创建JSON数组类型，插入数据
mysql> CREATE TABLE student (
-> id BIGINT AUTO_INCREMENT PRIMARY KEY,
-> info JSON DEFAULT NULL
-> );
Query OK, 0 rows affected (0.42 sec)
mysql> INSERT student (info) VALUES ('[1, 2, 3, 4]');
Query OK, 1 row affected (0.09 sec)
mysql> INSERT student (info) VALUES ('[2, 3, 4]');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT student (info) VALUES ('[3, 1, -1]');
Query OK, 1 row affected (0.13 sec)
mysql> select * from student;
+----+--------------+
| id | info |
+----+--------------+
| 1 | [1, 2, 3, 4] |
| 2 | [2, 3, 4] |
| 3 | [3, 1, -1] |
+----+--------------+
3 rows in set (0.00 sec)
JOSN数组通过索引取对应的值，同样是使用JSON_EXTRACT，索引从0开始
mysql> SELECT
-> JSON_EXTRACT(info, '$[0]') first
-> FROM student;
+----------------------------+
| first |
+----------------------------+
| 1 |
| 2 |
| 3 |
+----------------------------+
同样可以采用->>符号，这种方式会去除双引号，如果JSON数组内的元素是双引号字符串，显示出来的时候也会被去除引号
mysql> SELECT
-> id,
-> info->>"$[0]" first,
-> info->>"$[1]" second
-> FROM student;
+----+-------+--------+
| id | first | second |
+----+-------+--------+
| 1 | 1 | 2 |
| 2 | 2 | 3 |
| 3 | 3 | 1 |
+----+-------+--------+
可以提取数组中的多个值，此时JSON_EXTRACT后面传入多个参数，提取的顺序和输出顺序一致
mysql> SELECT id, JSON_EXTRACT(info, "$[1]", "$[0]") a FROM student;
+----+--------+
| id | a |
+----+--------+
| 1 | [2, 1] |
| 2 | [5, 2] |
| 3 | [3, 1] |
+----+--------+
3 rows in set (0.01 sec)
如果提取的索引位置不存在则返回空
mysql> SELECT
-> id,
-> info->>"$[2]" a
-> FROM student;
+----+------+
| id | a |
+----+------+
| 1 | 3 |
| 2 | 6 |
| 3 | NULL |
+----+------+
3 rows in set (0.00 sec)
（3）提取嵌套数组
嵌套数组主要是JSON数组内包含多个JSON对象或者JSON数组包含多个JSON数组，先以包含多个JSON对象为例
mysql> truncate table student;
Query OK, 0 rows affected (0.70 sec)
mysql> INSERT student (info) VALUES ('[{"no":1,"ent_name":"张家港市杨舍百桥士方园艺场","score":98.2}, {"no":2,"ent_name":"昆山市朱北苗圃有限公司","score":98.2}, {"no":3,"ent_name":"苏州市吴中区临湖现代渔业发展有限公司","score":98.2}]');
Query OK, 1 row affected (0.08 sec)
mysql> INSERT student (info) VALUES ('[{"no":1,"ent_name":"张家港市合力土石方挖掘服务部","score":98.1}, {"no":2,"ent_name":"中国石化销售有限公司江苏苏州养武加油站","score":97.6}, {"no":3,"ent_name":"中国石化销售有限公司江苏苏州太仓璜泾二站服务点","score":97.5}]');
Query OK, 1 row affected (0.09 sec)
mysql> select * from student;
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | info |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | [{"no": 1, "score": 98.2, "ent_name": "张家港市杨舍百桥士方园艺场"}, {"no": 2, "score": 98.2, "ent_name": "昆山市朱北苗圃有限公司"}, {"no": 3, "score": 98.2, "ent_name": "苏州市吴中区临湖现代渔业发展有限公司"}] |
| 2 | [{"no": 1, "score": 98.1, "ent_name": "张家港市合力土石方挖掘服务部"}, {"no": 2, "score": 97.6, "ent_name": "中国石化销售有限公司江苏苏州养武加油站"}, {"no": 3, "score": 97.5, "ent_name": "中国石化销售有限公司江苏苏州太仓璜泾二站服务点"}] |
+----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
提取嵌套数组的值，首先要找到数组中对应的对象，然后根据对象的键找值
mysql> SELECT
-> id,
-> JSON_UNQUOTE(JSON_EXTRACT(info -> "$[0]", "$.ent_name")) first_ent_name
-> FROM student;
+----+--------------------------------------------+
| id | first_ent_name |
+----+--------------------------------------------+
| 1 | 张家港市杨舍百桥士方园艺场 |
| 2 | 张家港市合力土石方挖掘服务部 |
+----+--------------------------------------------+
2 rows in set (0.00 sec)
对比一下之前的写法则只能提取第一个数组对象，可见JSON_UNQUOTE对于内侧的引号不删除，只删除外侧的引号
mysql> SELECT
-> id,
-> JSON_UNQUOTE(JSON_EXTRACT(info, "$[0]")) first
-> FROM student;
+----+------------------------------------------------------------------------------------+
| id | first |
+----+------------------------------------------------------------------------------------+
| 1 | {"no": 1, "score": 98.2, "ent_name": "张家港市杨舍百桥士方园艺场"} |
| 2 | {"no": 1, "score": 98.1, "ent_name": "张家港市合力土石方挖掘服务部"} |
+----+------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
在看数组套数组的情况
mysql> INSERT student (info) VALUES ('[1, 2, [3, 4]]');
Query OK, 1 row affected (0.07 sec)
mysql> select * from student;
+----+----------------+
| id | info |
+----+----------------+
| 1 | [1, 2, [3, 4]] |
+----+----------------+
1 row in set (0.00 sec)
提取其中嵌套的数组，第一个表示嵌套数据的位置索引，第二个*表示嵌套数据内取所有元素
mysql> SELECT
-> id,
-> JSON_EXTRACT(info, "$[2][*]") a
-> FROM student;
+----+--------+
| id | a |
+----+--------+
| 1 | [3, 4] |
+----+--------+
1 row in set (0.00 sec)
如果第二个参数不是*，也可以选取嵌套数据内的指定位置的元素
SELECT
id,
JSON_EXTRACT(info, "$[2][1]") a
FROM student;
mysql> SELECT
-> id,
-> JSON_EXTRACT(info, "$[2][1]") a
-> FROM student;
+----+------+
| id | a |
+----+------+
| 1 | 4 |
+----+------+
1 row in set (0.00 sec)
（4）提取JSON后增加过滤 / 排序条件
提取JSON后不能用新命名的字段做筛选过滤，需要调用把JSON函数或者符号再写一遍
mysql> select * from student;
+----+---------------------------------------------+
| id | info |
+----+---------------------------------------------+
| 1 | {"age": 13, "sex": "F", "city": "beijing"} |
| 2 | {"age": 14, "sex": "M", "city": "suzhou"} |
| 3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
+----+---------------------------------------------+
3 rows in set (0.00 sec)
筛选sex是F，age大于14的
mysql> SELECT
-> id,
-> info
-> FROM student WHERE info->>"$.age" > 14 and info->>"$.sex" = 'F';
+----+---------------------------------------------+
| id | info |
+----+---------------------------------------------+
| 3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
+----+---------------------------------------------+
1 row in set (0.01 sec)
根据age倒序排序取第一，只要city列
mysql> SELECT
-> id,
-> info->>"$.city"
-> FROM student WHERE info->>"$.age" > 14 and info->>"$.sex" = 'F';
+----+-----------------+
| id | info->>"$.city" |
+----+-----------------+
| 3 | shenzhen |
+----+-----------------+
1 row in set (0.00 sec)
（4）JSON类型和字符串的区别
除了JSON类型支持索引之外，看一下脚本语言对于JSON类型取值和字符串是否有区别，使用pymysql连接测试
>>> import pymysql
>>> config = {"user": "root", "password": "123456", "database": "test", "host": "127.0.0.1", "port": 3306}
>>> conn = pymysql.connect(**config)
>>> cursor = conn.cursor()
>>> cursor.execute("select info from student where id = 1")
1
>>> res = cursor.fetchall() # (('{"age": 13, "sex": "F", "city": "beijing"}',),)
>>> cursor.close()
>>> conn.close()
>>> json.loads(res[0][0])
{'age': 13, 'sex': 'F', 'city': 'beijing'}
可见结果是一个JSON格式的字符串，可以直接解析成JSON，所以脚本语言取出的JSON类型结果和字符串没有差别，就是JSON格式的字符串，另外指定JSON格式后，MySQL会对插入的字符串做检验，如果不符合JSON格式插入报错，这也是和传统Varchar或者TEXT的区别
JSON格式相比于Varchar，TEXT支持索引
JSON格式会对插入的字符串做JSON格式校验，不符合则报错
JSON格式的输入输出都是字符串，如果使用Varchar或者TEXT格式人工保证字符串为JSON格式，效果是一致的
作者：xiaogp
链接：https://www.jianshu.com/p/181497c586bd
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
2
常用JSON函数
首先，创建表列时候，列要设置为JSON类型:
CREATE TABLE t1 (content JSON);
插入数据，可以像插入varchar类型的数据一样，把json串添加单引号进行插入
mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.00 sec)当然mysql也提供了创建JSON对象的函数：
mysql> INSERT INTO t1 VALUES(JSON_OBJECT("key1","value1","key2","value2"));
Query OK, 1 row affected (0.00 sec)使用JSON_EXTRACT函数查询JSON类型数据中某个元素的值：
lamba表达式风格查询：
使用JSON_SET函数更新JSON中某个元素的值，如果不存在则添加：
mysql> update t1 set content=JSON_SET(content,"$.key1",'value111');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
更多JSON类型数据操作函数，可以参考：https://dev.mysql.com/doc/refman/8.0/en/json.html
3
MyBatis中使用JSON
比如Device表里面有个JSON类型的content字段，其中含有名称为name的元素，我们来修改和查询name元素对应的值。欢迎关注我们，公号终码一生。
ExtMapper中定义修改和查询接口：
@Mapper
public interface DeviceDOExtMapper extends com.zlx.user.dal.mapper.DeviceDOMapper {
//更新JSON串中名称为name的key的值
int updateName(@Param("name") String name, @Param("query") DeviceQuery query);
//查询JSON串中名称为name的key的值
String selectName(DeviceQuery query);
}
ExtMapper.xml中定义查询sql:
<mapper namespace="com.zlx.user.dal.mapper.ext.DeviceDOExtMapper">
<!--更新JSON串中名称为name的key的值-->
<update id="updateName" parameterType="map">
update device
<set>
<if test="name != null">
content = JSON_SET(content, '$.name', #{name,jdbcType=VARCHAR})
</if>
</set>
<if test="_parameter != null">
<include refid="Update_By_Example_Where_Clause"/>
</if>
</update>
<!--查询JSON串中名称为name的key的值-->
<select id="selectName" parameterType="com.zlx.user.dal.model.DeviceQuery" resultType="java.lang.String">
select
`content`->'$.name'
from device
<if test="_parameter != null">
<include refid="Example_Where_Clause"/>
</if>
</select>
</mapper>
4
总结
虽然我们实践上不建议把所有扩展字段都放到一个大字段里面。但是即使有原因一定到放，那么也建议选择JSON类型，而不是varcahr和Text类型。
参考：https://dev.mysql.com/doc/refman/8.0/en/json.html
PS：防止找不到本篇文章，可以收藏点赞，方便翻阅查找哦。
—END—
