MySQL：MySQL8.0 JSON类型利用整理，基于用户画像的案例

摘要：MySQL，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类型创建插入数据

  

1. create database test default charset utf8mb4;
3. CREATE TABLE student (
4.   id BIGINT AUTO_INCREMENT PRIMARY KEY,
5.   info JSON DEFAULT NULL
6. );

复制代码

  插入数据
  

1. mysql> INSERT student (info) VALUES ('{"sex": "F", "age": 13, "city": "beijing"}');
2. Query OK, 1 row affected (0.13 sec)
4. mysql> INSERT student (info) VALUES ('{"sex": "M", "age": 14, "city": "suzhou"}');
5. Query OK, 1 row affected (0.10 sec)
7. mysql> INSERT student (info) VALUES ('{"sex": "F", "age": 23, "city": "shenzhen"}');
8. Query OK, 1 row affected (0.20 sec)

复制代码

  查看数据
  

1. mysql> select * from student;
2. +----+---------------------------------------------+
3. | id | info                                        |
4. +----+---------------------------------------------+
5. |  1 | {"age": 13, "sex": "F", "city": "beijing"}  |
6. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |
7. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
8. +----+---------------------------------------------+

复制代码

  试一下插入JSON数组
  

1. mysql> INSERT student (info) VALUES ('[1,2,3,4]');
2. Query OK, 1 row affected (0.12 sec)
4. mysql> INSERT student (info) VALUES ('[{"sex": "M"},{"sex":"F", "city":"nanjing"}]');
5. Query OK, 1 row affected (0.11 sec)
7. mysql> select * from student;
8. +----+-------------------------------------------------+
9. | id | info                                            |
10. +----+-------------------------------------------------+
11. |  1 | {"age": 13, "sex": "F", "city": "beijing"}      |
12. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}       |
13. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"}     |
14. |  4 | [1, 2, 3, 4]                                    |
15. |  5 | [{"sex": "M"}, {"sex": "F", "city": "nanjing"}] |
16. +----+-------------------------------------------------+
17. 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根据键提取值
  

1. mysql> SELECT
2.     ->     id,
3.     ->     JSON_UNQUOTE(JSON_EXTRACT(info,"$.sex")) sex,
4.     ->     JSON_UNQUOTE(JSON_EXTRACT(info,"$.age")) age,
5.     ->     JSON_UNQUOTE(JSON_EXTRACT(info,"$.city")) city
6.     -> FROM student;
7. +----+------+------+----------+
8. | id | sex  | age  | city     |
9. +----+------+------+----------+
10. |  1 | F    | 13   | beijing  |
11. |  2 | M    | 14   | suzhou   |
12. |  3 | F    | 23   | shenzhen |
13. +----+------+------+----------+
14. 3 rows in set (0.00 sec)

复制代码

  MySQL 还提供了 ->> 表达式，和上述 SQL 效果完全一样，->>也是去除最外面的引号，另有一种符号->，它也能得到提取结果但是不去除外面的符号，相当于->和JSON_EXTRACT对应
  

1. mysql> SELECT
2.     ->     id,
3.     ->     info->>"$.sex" sex,
4.     ->     info->>"$.age" age,
5.     ->     info->>"$.city" city
6.     -> FROM student;
7. +----+------+------+----------+
8. | id | sex  | age  | city     |
9. +----+------+------+----------+
10. |  1 | F    | 13   | beijing  |
11. |  2 | M    | 14   | suzhou   |
12. |  3 | F    | 23   | shenzhen |
13. +----+------+------+----------+
14. 3 rows in set (0.00 sec)

复制代码

  如果JSON对象中查询的键不存在，则返回为NULL
  

1. mysql> SELECT
2.     ->     id,
3.     ->     JSON_UNQUOTE(JSON_EXTRACT(info,"$.sex")) sex,
4.     ->     JSON_UNQUOTE(JSON_EXTRACT(info,"$.height")) height
5.     -> FROM student;
6. +----+------+--------+
7. | id | sex  | height |
8. +----+------+--------+
9. |  1 | F    | NULL   |
10. |  2 | M    | NULL   |
11. |  3 | F    | NULL   |
12. +----+------+--------+
13. 3 rows in set (0.00 sec)

复制代码

  （2）提取JSON数组

   先创建JSON数组类型，插入数据
  

1. mysql> CREATE TABLE student (
2.     ->   id BIGINT AUTO_INCREMENT PRIMARY KEY,
3.     ->   info JSON DEFAULT NULL
4.     -> );
5. Query OK, 0 rows affected (0.42 sec)
7. mysql> INSERT student (info) VALUES ('[1, 2, 3, 4]');
8. Query OK, 1 row affected (0.09 sec)
10. mysql> INSERT student (info) VALUES ('[2, 3, 4]');
11. Query OK, 1 row affected (0.08 sec)
13. mysql> INSERT student (info) VALUES ('[3, 1, -1]');
14. Query OK, 1 row affected (0.13 sec)
16. mysql> select * from student;
17. +----+--------------+
18. | id | info         |
19. +----+--------------+
20. |  1 | [1, 2, 3, 4] |
21. |  2 | [2, 3, 4]    |
22. |  3 | [3, 1, -1]   |
23. +----+--------------+
24. 3 rows in set (0.00 sec)

复制代码

  JOSN数组通过索引取对应的值，同样是利用JSON_EXTRACT，索引从0开始
  

1. mysql> SELECT
2.     ->     JSON_EXTRACT(info, '$[0]') first
3.     -> FROM student;
4. +----------------------------+
5. | first                      |
6. +----------------------------+
7. | 1                          |
8. | 2                          |
9. | 3                          |
10. +----------------------------+

复制代码

  同样可以接纳->>符号，这种方式会去除双引号，如果JSON数组内的元素是双引号字符串，显示出来的时候也会被去除引号
  

1. mysql> SELECT
2.     ->     id,
3.     ->     info->>"$[0]" first,
4.     ->     info->>"$[1]" second
5.     -> FROM student;
6. +----+-------+--------+
7. | id | first | second |
8. +----+-------+--------+
9. |  1 | 1     | 2      |
10. |  2 | 2     | 3      |
11. |  3 | 3     | 1      |
12. +----+-------+--------+

复制代码

  可以提取数组中的多个值，此时JSON_EXTRACT后面传入多个参数，提取的次序和输出次序一致
  

1. mysql> SELECT id, JSON_EXTRACT(info, "$[1]", "$[0]") a FROM student;
2. +----+--------+
3. | id | a      |
4. +----+--------+
5. |  1 | [2, 1] |
6. |  2 | [5, 2] |
7. |  3 | [3, 1] |
8. +----+--------+
9. 3 rows in set (0.01 sec)

复制代码

  如果提取的索引位置不存在则返回空
  

1. mysql> SELECT
2.     ->     id,
3.     ->     info->>"$[2]" a
4.     -> FROM student;
5. +----+------+
6. | id | a    |
7. +----+------+
8. |  1 | 3    |
9. |  2 | 6    |
10. |  3 | NULL |
11. +----+------+
12. 3 rows in set (0.00 sec)

复制代码

  （3）提取嵌套数组

   嵌套数组主要是JSON数组内包含多个JSON对象或者JSON数组包含多个JSON数组，先以包含多个JSON对象为例
  

1. mysql> truncate table student;
2. Query OK, 0 rows affected (0.70 sec)
4. 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}]');
5. Query OK, 1 row affected (0.08 sec)
7. 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}]');
8. Query OK, 1 row affected (0.09 sec)

复制代码

1. mysql> select * from student;
2. +----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3. | id | info                                                                                                                                                                                                                                                                                                   |
4. +----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5. |  1 | [{"no": 1, "score": 98.2, "ent_name": "张家港市杨舍百桥士方园艺场"}, {"no": 2, "score": 98.2, "ent_name": "昆山市朱北苗圃有限公司"}, {"no": 3, "score": 98.2, "ent_name": "苏州市吴中区临湖现代渔业发展有限公司"}]                                                                                     |
6. |  2 | [{"no": 1, "score": 98.1, "ent_name": "张家港市合力土石方挖掘服务部"}, {"no": 2, "score": 97.6, "ent_name": "中国石化销售有限公司江苏苏州养武加油站"}, {"no": 3, "score": 97.5, "ent_name": "中国石化销售有限公司江苏苏州太仓璜泾二站服务点"}]                                                         |
7. +----+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
8. 2 rows in set (0.00 sec)

复制代码

  提取嵌套数组的值，首先要找到数组中对应的对象，然后根据对象的键找值
  

1. mysql> SELECT
2.     ->     id,
3.     ->     JSON_UNQUOTE(JSON_EXTRACT(info -> "$[0]", "$.ent_name")) first_ent_name
4.     -> FROM student;
5. +----+--------------------------------------------+
6. | id | first_ent_name                             |
7. +----+--------------------------------------------+
8. |  1 | 张家港市杨舍百桥士方园艺场                 |
9. |  2 | 张家港市合力土石方挖掘服务部               |
10. +----+--------------------------------------------+
11. 2 rows in set (0.00 sec)

复制代码

  对比一下之前的写法则只能提取第一个数组对象，可见JSON_UNQUOTE对于内侧的引号不删除，只删除外侧的引号
  

1. mysql> SELECT
2.     ->     id,
3.     ->     JSON_UNQUOTE(JSON_EXTRACT(info, "$[0]")) first
4.     -> FROM student;
5. +----+------------------------------------------------------------------------------------+
6. | id | first                                                                              |
7. +----+------------------------------------------------------------------------------------+
8. |  1 | {"no": 1, "score": 98.2, "ent_name": "张家港市杨舍百桥士方园艺场"}                 |
9. |  2 | {"no": 1, "score": 98.1, "ent_name": "张家港市合力土石方挖掘服务部"}               |
10. +----+------------------------------------------------------------------------------------+
11. 2 rows in set (0.00 sec)

复制代码

  在看数组套数组的情况
  

1. mysql> INSERT student (info) VALUES ('[1, 2, [3, 4]]');
2. Query OK, 1 row affected (0.07 sec)
4. mysql> select * from student;
5. +----+----------------+
6. | id | info           |
7. +----+----------------+
8. |  1 | [1, 2, [3, 4]] |
9. +----+----------------+
10. 1 row in set (0.00 sec)

复制代码

  提取此中嵌套的数组，第一个表现嵌套数据的位置索引，第二个*表现嵌套数据内取全部元素
  

1. mysql> SELECT
2.     ->     id,
3.     ->     JSON_EXTRACT(info, "$[2][*]") a
4.     -> FROM student;
5. +----+--------+
6. | id | a      |
7. +----+--------+
8. |  1 | [3, 4] |
9. +----+--------+
10. 1 row in set (0.00 sec)

复制代码

  如果第二个参数不是*，也可以选取嵌套数据内的指定位置的元素
  

1. SELECT
2.     id,
3.     JSON_EXTRACT(info, "$[2][1]") a
4. FROM student;

复制代码

1. mysql> SELECT
2.     ->     id,
3.     ->     JSON_EXTRACT(info, "$[2][1]") a
4.     -> FROM student;
5. +----+------+
6. | id | a    |
7. +----+------+
8. |  1 | 4    |
9. +----+------+
10. 1 row in set (0.00 sec)

复制代码

  （4）提取JSON后增加过滤 / 排序条件

   提取JSON后不能用新定名的字段做筛选过滤，需要调用把JSON函数或者符号再写一遍
  

1. mysql> select * from student;
2. +----+---------------------------------------------+
3. | id | info                                        |
4. +----+---------------------------------------------+
5. |  1 | {"age": 13, "sex": "F", "city": "beijing"}  |
6. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |
7. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
8. +----+---------------------------------------------+
9. 3 rows in set (0.00 sec)

复制代码

  筛选sex是F，age大于14的
  

1. mysql> SELECT
2.     ->     id,
3.     ->     info
4.     -> FROM student WHERE info->>"$.age" > 14 and info->>"$.sex" = 'F';
5. +----+---------------------------------------------+
6. | id | info                                        |
7. +----+---------------------------------------------+
8. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
9. +----+---------------------------------------------+
10. 1 row in set (0.01 sec)

复制代码

  根据age倒序排序取第一，只要city列
  

1. mysql> SELECT
2.     ->     id,
3.     ->     info->>"$.city"
4.     -> FROM student WHERE info->>"$.age" > 14 and info->>"$.sex" = 'F';
5. +----+-----------------+
6. | id | info->>"$.city" |
7. +----+-----------------+
8. |  3 | shenzhen        |
9. +----+-----------------+
10. 1 row in set (0.00 sec)

复制代码

  

---

   （4）JSON类型和字符串的区别

   除了JSON类型支持索引之外，看一下脚本语言对于JSON类型取值和字符串是否有区别，利用pymysql毗连测试
  

1. >>> import pymysql
2. >>> config = {"user": "root", "password": "123456", "database": "test", "host": "127.0.0.1", "port": 3306}
3. >>> conn = pymysql.connect(**config)
4. >>> cursor = conn.cursor()
5. >>> cursor.execute("select info from student where id = 1")
6. 1
7. >>> res = cursor.fetchall()  # (('{"age": 13, "sex": "F", "city": "beijing"}',),)
8. >>> cursor.close()
9. >>> conn.close()
10. >>> json.loads(res[0][0])
11. {'age': 13, 'sex': 'F', 'city': 'beijing'}

复制代码

  可见结果是一个JSON格式的字符串，可以直接解析成JSON，所以脚本语言取出的JSON类型结果和字符串没有差别，就是JSON格式的字符串，另外指定JSON格式后，MySQL会对插入的字符串做检验，如果不符合JSON格式插入报错，这也是和传统Varchar或者TEXT的区别
   

• JSON格式相比于Varchar，TEXT支持索引
  
• JSON格式会对插入的字符串做JSON格式校验，不符合则报错
  
• JSON格式的输入输出都是字符串，如果利用Varchar或者TEXT格式人工保证字符串为JSON格式，效果是一致的
  

   

---

   （5）JSON类型数据修改

   修改数据主要是JSON_SET，JSON_INSERT，JSON_REPLACE三个方法
   

• JSON_SET：替换现有key的值，插入不存在的key的值
  
• JSON_INSERT：插入不存在的key的值，已经存在的不修改
  
• JSON_REPLACE：只替换已存在的key的值，不存在的不做插入
  

   利用的时候联合update语句
  

1. mysql> select * from student;
2. +----+---------------------------------------------+
3. | id | info                                        |
4. +----+---------------------------------------------+
5. |  1 | {"age": 13, "sex": "F", "city": "beijing"}  |
6. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |
7. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
8. +----+---------------------------------------------+
9. 3 rows in set (0.01 sec)

复制代码

  JSON_SET，不存在则插入，有则替换
  

1. mysql> UPDATE student SET info = JSON_SET(info, "$.city", 'wuxi', "$.height", 123) WHERE id = 1;
2. Query OK, 1 row affected (0.87 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. mysql> select * from student;
6. +----+--------------------------------------------------------+
7. | id | info                                                   |
8. +----+--------------------------------------------------------+
9. |  1 | {"age": 13, "sex": "F", "city": "wuxi", "height": 123} |
10. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}              |
11. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"}            |
12. +----+--------------------------------------------------------+

复制代码

  JSON_INSERT，只会插入不存在的值
  

1. mysql> UPDATE student SET info = JSON_INSERT(info, "$.city", 'wuxi', "$.height", 123) WHERE id = 1;
2. Query OK, 1 row affected (0.11 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. mysql> select * from student;
6. +----+-----------------------------------------------------------+
7. | id | info                                                      |
8. +----+-----------------------------------------------------------+
9. |  1 | {"age": 13, "sex": "F", "city": "beijing", "height": 123} |
10. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}                 |
11. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"}               |
12. +----+-----------------------------------------------------------+
13. 3 rows in set (0.00 sec)

复制代码

  JSON_REPLACE，只会替换已有值
  

1. mysql> UPDATE student SET info = JSON_REPLACE(info, "$.city", 'wuxi', "$.height", 123) WHERE id = 1;
2. Query OK, 1 row affected (0.06 sec)
3. Rows matched: 1  Changed: 1  Warnings: 0
5. mysql> select * from student;
6. +----+---------------------------------------------+
7. | id | info                                        |
8. +----+---------------------------------------------+
9. |  1 | {"age": 13, "sex": "F", "city": "wuxi"}     |
10. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |
11. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
12. +----+---------------------------------------------+
13. 3 rows in set (0.00 sec)

复制代码

  删除利用JSON_REMOVE，在JSON对象中指定key删除
  

1. mysql> select * from student;
2. +----+---------------------------------------------+
3. | id | info                                        |
4. +----+---------------------------------------------+
5. |  1 | {"age": 13, "sex": "F", "city": "wuxi"}     |
6. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |
7. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
8. +----+---------------------------------------------+
9. 3 rows in set (0.00 sec)
11. mysql> UPDATE student SET info = JSON_REMOVE(info, "$.age") WHERE id = 1;
12. Query OK, 1 row affected (0.45 sec)
13. Rows matched: 1  Changed: 1  Warnings: 0
15. mysql> select * from student;
16. +----+---------------------------------------------+
17. | id | info                                        |
18. +----+---------------------------------------------+
19. |  1 | {"sex": "F", "city": "wuxi"}                |
20. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |
21. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
22. +----+---------------------------------------------+
23. 3 rows in set (0.00 sec)

复制代码

  在JSON数组中指定下标删除
  

1. mysql> select * from student;
2. +----+----------------+
3. | id | info           |
4. +----+----------------+
5. |  1 | [1, 2, [3, 4]] |
6. |  2 | [2, 5, 6]      |
7. |  3 | [1, 3]         |
8. +----+----------------+
9. 3 rows in set (0.00 sec)
11. mysql> UPDATE student SET info = JSON_REMOVE(info, "$[1]") WHERE id=2;
12. Query OK, 1 row affected (0.72 sec)
13. Rows matched: 1  Changed: 1  Warnings: 0
15. mysql> select * from student;
16. +----+----------------+
17. | id | info           |
18. +----+----------------+
19. |  1 | [1, 2, [3, 4]] |
20. |  2 | [2, 6]         |
21. |  3 | [1, 3]         |
22. +----+----------------+
23. 3 rows in set (0.00 sec)

复制代码

  

---

   （6）JSON类型利用索引

   当 JSON 数据量非常大，用户希望对 JSON 数据进行有效检索时，可以利用 MySQL 的函数索引功能对 JSON 中的某个字段进行索引，具体方式是先创建一个假造列，再对假造列创建索引
先看一下没有索引下，对JSON对象中某个key做条件检索的EXPLAIN计划，可见访问方式type是ALL全表扫面，利用的索引Key是NULL
  

1. mysql> select * from student;
2. +----+---------------------------------------------+
3. | id | info                                        |
4. +----+---------------------------------------------+
5. |  1 | {"age": 13, "sex": "F", "city": "beijing"}  |
6. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |
7. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
8. +----+---------------------------------------------+
9. 3 rows in set (0.00 sec)mysql> explain select * from student where info->>"$.age" = 13;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 1 warning (0.01 sec)

复制代码

  创建假造列age，而且添加普通索引，可见表新增了一列（虽然是假造列，但是在SELECT和脚本语言取数都能取到新增的假造列），重新EXPLAIN之后Type改为ref代表普通索引，利用的Key是idx_age
  

1. mysql> ALTER TABLE student ADD COLUMN age INT as (info->>"$.age");
2. Query OK, 0 rows affected (0.54 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
5. mysql> select * from student;
6. +----+---------------------------------------------+------+
7. | id | info                                        | age  |
8. +----+---------------------------------------------+------+
9. |  1 | {"age": 13, "sex": "F", "city": "beijing"}  |   13 |
10. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |   14 |
11. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |   23 |
12. +----+---------------------------------------------+------+
13. 3 rows in set (0.00 sec)
15. mysql> ALTER TABLE student ADD INDEX idx_age(age);
16. Query OK, 0 rows affected (0.51 sec)
17. Records: 0  Duplicates: 0  Warnings: 0
19. mysql> explain select * from student where info->>"$.age" = 13;
20. +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
21. | id | select_type | table   | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
22. +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
23. |  1 | SIMPLE      | student | NULL       | ref  | idx_age       | idx_age | 5       | const |    1 |   100.00 | NULL  |
24. +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+
25. 1 row in set, 1 warning (0.00 sec)

复制代码

  除了普通索引，同理还可以为JSON对象的其他Key值创建唯一索引等
除了再建表之后创建假造列增加索引，也可以在建表的时候就为JSON类型创建索引
  

1. mysql> CREATE TABLE student (
2.     ->   id BIGINT AUTO_INCREMENT PRIMARY KEY,
3.     ->   info JSON DEFAULT NULL,
4.     ->   age Int as (info->>"$.age"),
5.     ->   index idx_age(age)
6.     -> );

复制代码

  

---

   （7）JSON类型其他常用函数

   除了上面的JSON_EXTRACT和JSON_UNQUOTE，常用函数如下
   

• MEMBER OF：只能对JSON数组利用，返回1元素存在数组中，0元素不存在数组中
  
• JSON_CONTAINS：可以对JSON数组和JSON对象利用，针对JSON数组检查一个元素或者多个元素是否存在，对于JSON对象检查指定路径下是否有某个值或者是否有某个路径（Key）
  
• JSON_OVERLAP：比较两个JSON数组是否至少有一个元素一致，如果是返回1，否则返回0，如果是JSON对象，判断是否是找有一对key value一致
  
• JSON_KEYS：返回JSON对象的Key，也可以是嵌套JSON对象
  

   以上函数可以在前面加上NOT关键字就可以取反
   MEMBER OF是MySQL 8.0.17新增的函数，查看元素或者子数组是否存在在JSON数组中，如果把MEMBER OF放在where条件后面则直接筛选结果是1的，可以省略写=1
  

1. mysql> select * from student;
2. +----+----------------+
3. | id | info           |
4. +----+----------------+
5. |  1 | [1, 2, [3, 4]] |
6. |  2 | [2, 5, 6]      |
7. |  3 | [1, 3]         |
8. +----+----------------+
9. 3 rows in set (0.01 sec)
11. mysql> SELECT * FROM student WHERE 3 MEMBER OF(info);
12. +----+--------+
13. | id | info   |
14. +----+--------+
15. |  3 | [1, 3] |
16. +----+--------+
17. 1 row in set (0.00 sec)
19. mysql> SELECT * FROM student WHERE JSON_ARRAY(3, 4) MEMBER OF(info);
20. +----+----------------+
21. | id | info           |
22. +----+----------------+
23. |  1 | [1, 2, [3, 4]] |
24. +----+----------------+
25. 1 row in set (0.00 sec)

复制代码

  JSON_CONTAINS和MEMBER OF类似，但是JSON_CONTAINS可以作用与JSON对象，对于JSON数组两者也有区别，JSON_CONTAINS可以指定多个数组内的元素，相当于是且的关系，JSON_CONTAINS放在where后面默认筛选结果值是1的
  

1. mysql> select * from student;
2. +----+----------------+
3. | id | info           |
4. +----+----------------+
5. |  1 | [1, 2, [3, 4]] |
6. |  2 | [2, 5, 6]      |
7. |  3 | [1, 3]         |
8. +----+----------------+
9. 3 rows in set (0.00 sec)
11. mysql> SELECT * FROM student WHERE JSON_CONTAINS(info, '[2, 6]');
12. +----+-----------+
13. | id | info      |
14. +----+-----------+
15. |  2 | [2, 5, 6] |
16. +----+-----------+
17. 1 row in set (0.00 sec)
19. mysql> SELECT * FROM student WHERE JSON_CONTAINS(info, '[2]');
20. +----+----------------+
21. | id | info           |
22. +----+----------------+
23. |  1 | [1, 2, [3, 4]] |
24. |  2 | [2, 5, 6]      |
25. +----+----------------+
27. mysql> SELECT * FROM student WHERE JSON_CONTAINS(info, '[3]');
28. +----+----------------+
29. | id | info           |
30. +----+----------------+
31. |  1 | [1, 2, [3, 4]] |
32. |  3 | [1, 3]         |
33. +----+----------------+

复制代码

  当JSON_CONTAINS作用与JSON对象时，需要判断某个key-value对是否存，格式是(target，value，key)，在指定value的时候，数值用单引号，字符串还要在内加一层双引号
  

1. mysql> select * from student;
2. +----+---------------------------------------------+
3. | id | info                                        |
4. +----+---------------------------------------------+
5. |  1 | {"age": 13, "sex": "F", "city": "beijing"}  |
6. |  2 | {"age": 14, "sex": "M", "city": "suzhou"}   |
7. |  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |
8. +----+---------------------------------------------+
9. mysql> SELECT * FROM student WHERE JSON_CONTAINS(info, '"F"', '$.sex');+----+---------------------------------------------+| id | info                                        |+----+---------------------------------------------+|  1 | {"age": 13, "sex": "F", "city": "beijing"}  ||  3 | {"age": 23, "sex": "F", "city": "shenzhen"} |+----+---------------------------------------------+mysql> SELECT * FROM student WHERE JSON_CONTAINS(info, '13', '$.age');+----+--------------------------------------------+| id | info                                       |+----+--------------------------------------------+|  1 | {"age": 13, "sex": "F", "city": "beijing"} |+----+--------------------------------------------+

复制代码

  JSON_OVERLAP返回两个JSON数组或者JSON对象至少有一个/对元素一致则是1，房子啊where后面筛选出存在至少一个交集的数据
  

1. mysql> SELECT * FROM student WHERE JSON_OVERLAPS(info, '[1, 5]');
2. +----+--------------+
3. | id | info         |
4. +----+--------------+
5. |  1 | [1, 2, 5]    |
6. |  2 | [2, 5, 6]    |
7. |  3 | [1, 3, 8]    |
8. |  4 | [1, 2, 7, 8] |
9. +----+--------------+
10. 4 rows in set (0.00 sec)
12. mysql> SELECT * FROM student WHERE JSON_OVERLAPS(info, '[1, 5, 6]');
13. +----+--------------+
14. | id | info         |
15. +----+--------------+
16. |  1 | [1, 2, 5]    |
17. |  2 | [2, 5, 6]    |
18. |  3 | [1, 3, 8]    |
19. |  4 | [1, 2, 7, 8] |
20. +----+--------------+
21. 4 rows in set (0.00 sec)
23. mysql> SELECT * FROM student WHERE JSON_OVERLAPS(info, '[5]');
24. +----+-----------+
25. | id | info      |
26. +----+-----------+
27. |  1 | [1, 2, 5] |
28. |  2 | [2, 5, 6] |
29. +----+-----------+
30. 2 rows in set (0.00 sec)
32. mysql> SELECT * FROM student WHERE JSON_OVERLAPS(info, '[9]');
33. Empty set (0.00 sec)

复制代码

  

---

   （8）JSON ARRAY的多值索引

   从MySQL8.0.17开始，InnoDB支持多值索引。多值索引是在存储JSON数组的列上定义的辅助索引，对于JSON ARRAY的MEMBER OF，JSON_CONTAINS，JSON_OVERLAPS可以利用多值索引进行性能优化
  

1. mysql> select * from student;
2. +----+--------------+
3. | id | info         |
4. +----+--------------+
5. |  1 | [1, 2, 5, 9] |
6. |  2 | [2, 5, 6, 8] |
7. |  3 | [5, 3, 8, 9] |
8. |  4 | [1, 2, 7, 8] |
9. +----+--------------+
10. 4 rows in set (0.00 sec)

复制代码

  先不创建多值索引，利用JSON_CONTAINS语句EXPLAIN查看实行计划
  

1. mysql> EXPLAIN SELECT * FROM student WHERE JSON_CONTAINS(info, '[5, 3]');
2. +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
3. | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
4. +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
5. |  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using where |
6. +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
7. 1 row in set, 1 warning (0.00 sec)

复制代码

  Type为全表扫描，key为NULL，下一步给表增加多值索引，注意如果这个地方改为idx_info((cast((info->"$") as unsigned array)))，则后续全部的函数都要是info->"$"，否则走不了索引
  

1. mysql> ALTER TABLE student ADD INDEX idx_info((cast(info as unsigned array)));
2. Query OK, 0 rows affected (0.57 sec)
3. Records: 0  Duplicates: 0  Warnings: 0

复制代码

  查看创建多值索引之后的EXPLAIN计划，可见现在JSON操作函数都走了索引
  

1. mysql> EXPLAIN SELECT * FROM student WHERE 5 MEMBER OF(info);
2. +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
3. | id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
4. +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
5. |  1 | SIMPLE      | student | NULL       | ref  | idx_info      | idx_info | 9       | const |    1 |   100.00 | Using where |
6. +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
7. 1 row in set, 1 warning (0.00 sec)
9. mysql> EXPLAIN SELECT * FROM student WHERE JSON_CONTAINS(info, '[5, 3]');
10. +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
11. | id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
12. +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
13. |  1 | SIMPLE      | student | NULL       | range | idx_info      | idx_info | 9       | NULL |    4 |   100.00 | Using where |
14. +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
15. 1 row in set, 1 warning (0.00 sec)
17. mysql> EXPLAIN SELECT * FROM student WHERE JSON_OVERLAPS(info, '[5, 3]');
18. +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
19. | id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
20. +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
21. |  1 | SIMPLE      | student | NULL       | range | idx_info      | idx_info | 9       | NULL |    4 |   100.00 | Using where |
22. +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
23. 1 row in set, 1 warning (0.00 sec)

复制代码

  

---

   （9）基于JSON类型的用户画像设计

   下面基于30万级别的数据，以MySQL的JSON做用户画像的存储和查询测试，画像值已经全部处理成罗列值，不加入数值型的字段，第一步梳理用户标签，定义一张标签表，记录标签值和标签号
  

1. mysql> CREATE TABLE tags_info (
2.     ->     tag_id bigint auto_increment primary key,
3.     ->     tag_name varchar(255) not null,
4.     ->     tag_value varchar(255) not null
5.     -> );
6. Query OK, 0 rows affected (0.80 sec)

复制代码

  插入全部的标签，预览表如下
  

1. mysql> select * from tags_info order by rand() limit 10;
2. +--------+--------------+--------------------+
3. | tag_id | tag_name     | tag_value          |
4. +--------+--------------+--------------------+
5. |     24 | 渠道名称     | 自助收银           |
6. |     38 | 类目范围     | 类目多样           |
7. |      3 | 生日月份     | 1                  |
8. |     35 | RFM          | 重要保持会员       |
9. |     11 | 生日月份     | 9                  |
10. |     44 | 时间偏好     | 常客               |
11. |     16 | 会员等级     | 员工卡             |
12. |     27 | 会员状态     | 沉默               |
13. |     25 | 渠道名称     | 闪电购             |
14. |     43 | 时间偏好     | 周末客             |
15. +--------+--------------+--------------------+

复制代码

  下一步构建用户画像表，标签值替换为标签ID，将一个用户的全部标签值存储为JSON ARRAY，先构建一张用户画像结果表
  

1. mysql> CREATE TABLE user_tag (
2.     ->     user_id bigint not null primary key,
3.     ->     user_tags JSON
4.     -> );
5. Query OK, 0 rows affected (0.41 sec)

复制代码

  插入数据查看预览结果
  

1. mysql> select * from user_tag order by rand() limit 10;
2. +---------+--------------------------------------+
3. | user_id | user_tags                            |
4. +---------+--------------------------------------+
5. |   36978 | [14, 19, 23, 25, 32, 42]             |
6. |   28515 | [14, 19, 23, 27, 36, 42]             |
7. |   28683 | [14, 19, 28]                         |
8. |   39368 | [14, 19, 24, 26, 36, 43]             |
9. |   22269 | [14, 19, 28]                         |
10. |   42160 | [14, 19, 23, 26, 32, 43]             |
11. |   22321 | [14, 17, 27, 33, 40, 43, 44, 47, 49] |
12. |   20407 | [14, 19, 20, 25, 34, 38, 43]         |
13. |   26167 | [14, 17, 23, 27, 35, 38, 43]         |
14. |   38082 | [14, 19, 25, 32, 42]                 |
15. +---------+--------------------------------------+
16. 10 rows in set (0.00 sec)

复制代码

  下一步对用户画像进行标签筛选指定的人群做营销，实际上转化为SQL就是多个条件的与或非组合，看几个案例
（1）取用户品级是白银卡（17）或者银卡（20），且用户状态是活泼的（28），逻辑是（17∪20）∩28，33万里面筛选出265个人
  

1. mysql> SELECT * FROM user_tag WHERE JSON_OVERLAPS(user_tags, '[18, 17, 20]') AND JSON_CONTAINS(user_tags, '[28]') limit 5;
2. +---------+------------------+
3. | user_id | user_tags        |
4. +---------+------------------+
5. |    3201 | [14, 19, 20, 28] |
6. |    4183 | [14, 19, 20, 28] |
7. |    4554 | [14, 19, 20, 28] |
8. |    4890 | [14, 19, 20, 28] |
9. |    6334 | [14, 17, 28]     |
10. +---------+------------------+
12. mysql> SELECT count(*) FROM user_tag WHERE JSON_OVERLAPS(user_tags, '[18, 17, 20]') AND JSON_CONTAINS(user_tags, '[28]');
13. +----------+
14. | count(*) |
15. +----------+
16. |      265 |
17. +----------+
18. 1 row in set (0.66 sec)

复制代码

  （2）筛选母婴客群（47）且是周末客（43），但是过滤掉流失会员（29），逻辑是47∩43∩（not 29），终极结果筛选出11678人
  

1. mysql> SELECT * FROM user_tag WHERE JSON_CONTAINS(user_tags, '[43, 47]') AND NOT JSON_CONTAINS(user_tags, '[29]') limit 10;
2. +---------+----------------------------------------------+
3. | user_id | user_tags                                    |
4. +---------+----------------------------------------------+
5. |    3036 | [14, 17, 24, 27, 33, 37, 40, 43, 44, 47, 48] |
6. |    3049 | [14, 17, 27, 33, 38, 40, 43, 44, 47, 48]     |
7. |    3072 | [14, 19, 20, 27, 33, 37, 40, 43, 44, 47, 48] |
8. |    3099 | [14, 19, 21, 27, 33, 37, 40, 43, 44, 47]     |
9. |    3110 | [14, 15, 27, 33, 37, 40, 43, 44, 46, 47, 48] |
10. |    3127 | [14, 19, 20, 27, 33, 37, 40, 43, 44, 46, 47] |
11. |    3132 | [14, 19, 27, 33, 40, 41, 43, 44, 47]         |
12. |    3147 | [14, 17, 27, 34, 40, 43, 46, 47, 48]         |
13. |    3157 | [14, 19, 23, 26, 34, 43, 47, 48]             |
14. |    3160 | [14, 19, 23, 27, 34, 40, 43, 44, 47, 48]     |
15. +---------+----------------------------------------------+
16. 10 rows in set (0.00 sec)
18. mysql> SELECT count(*) FROM user_tag WHERE JSON_CONTAINS(user_tags, '[43, 47]') AND NOT JSON_CONTAINS(user_tags, '[29]');
19. +----------+
20. | count(*) |
21. +----------+
22. |    11678 |
23. +----------+
24. 1 row in set (0.55 sec)

复制代码

  （3）筛选RFM为重要开头的高价值客户（34,35,36,37），且是微信会员（15），逻辑是（34∪35∪36∪37）∩15，终极筛选出148人
  

1. mysql> SELECT * FROM user_tag WHERE JSON_OVERLAPS(user_tags, '[34, 35, 36, 37]') AND JSON_CONTAINS(user_tags, '[15]') limit 10;
2. +---------+--------------------------------------------------------+
3. | user_id | user_tags                                              |
4. +---------+--------------------------------------------------------+
5. |    3110 | [14, 15, 27, 33, 37, 40, 43, 44, 46, 47, 48]           |
6. |    6990 | [14, 15, 23, 27, 34, 37, 40, 43, 44, 47, 48, 49]       |
7. |   11214 | [14, 15, 24, 27, 33, 37, 40, 43, 44, 47]               |
8. |   13447 | [0, 2, 14, 15, 27, 33, 37, 40, 43, 44, 46, 47, 48]     |
9. |   18356 | [14, 15, 25, 34, 38, 43]                               |
10. |   22016 | [1, 2, 14, 15, 27, 33, 37, 40, 43, 44, 46, 47, 48, 49] |
11. |   22392 | [14, 15, 23, 27, 33, 37, 40, 41, 43, 44, 48, 49]       |
12. |   22721 | [1, 7, 14, 15, 27, 33, 37, 40, 42, 44, 48]             |
13. |   22800 | [14, 15, 23, 27, 33, 37, 40, 43, 44, 48]               |
14. |   25122 | [14, 15, 26, 34, 37, 40, 43, 47, 48, 49]               |
15. +---------+--------------------------------------------------------+
16. 10 rows in set (0.06 sec)
18. mysql> SELECT count(*) FROM user_tag WHERE JSON_OVERLAPS(user_tags, '[34, 35, 36, 37]') AND JSON_CONTAINS(user_tags, '[15]');
19. +----------+
20. | count(*) |
21. +----------+
22. |      148 |
23. +----------+
24. 1 row in set (0.75 sec)

复制代码

  给这张用户画像表增加一下多值索引
  

1. ALTER TABLE user_tag ADD INDEX idx_info((cast(user_tags as unsigned array)));

复制代码

  结果是SQL不能得到准确结果，之前能检索到人群现在检索结果为符合条件的人为0，可能是在多值索引的情况下，不能组合多个JSON函数的缘故起因
  

1. mysql> SELECT count(*) FROM user_tag WHERE JSON_OVERLAPS(user_tags, '[34, 35, 36, 37]') AND JSON_CONTAINS(user_tags, '[15]');
2. +----------+
3. | count(*) |
4. +----------+
5. |        0 |
6. +----------+
7. 1 row in set (0.00 sec)

复制代码

  另外在多次测试下，就算倒霉用JSON函数组合，单个利用JSON_CONTAINS，JSON_OVERLAPS也可能会导致索引失效，具体缘故起因不明，如果利用NOT条件多值索引直接失效
       末了编辑于：2025-02-05 20:55:48       ©    著作权归作者全部,转载或内容合作请联系作者     

喜欢的朋侪记得点赞、收藏、关注哦！！！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。