图文实录｜MySQL技术内幕系列分享：JSON相关特性详解

点击上方蓝字关注我们

本文金句

随着MySQL官方在对JSON数据处理的支持的逐步完善，在MySQL中使用JSON数据将会变得越来越方便和快捷。

• 数据可以灵活处理：字段内容可以快速拓展，避免了加列操作；JSON数据是key-value pair的组织形式，其中的信息可以根据需要灵活增减，所以无需像传统表那样需要加减column才能增减新的数据信息。
  
  
• 减少非必要的存储空间：避免了稀疏字段的NULL值，减少其所占用的存储空间；JSON数据可以不用耗费空间存储NULL值信息，所以可以节省了部分此类数据的存储空间。
  
  
• 支持对JSON元素的索引：相比于字符串格式的JSON，JSON类型支持索引做特定的查询优化；目前官方在8.0版本已经支持了基于生成列的索引以及多值索引，使得对于JSON数据的查询更加快速高效。
  
  
• 跨数据类型查询：结构化数据和非结构化数据联合查询更方便。由于结构化数据和非结构化数据可以放在同一张表里，使得采用一句SQL就可以对这些数据进行联合查询。

• 创建含有JSON字段的表；

  

  
  

• 与其他类型字段一样，直接插入或修改JSON数据；

  

  
  

• 类似字符串类型一样简单查询JSON字段内容；

  

  
  

• 可以直接通过指定JSON字段的属性名称来查询对应的属性值；

  

  
  

• 通过JSON_REMOVE来删除JSON值；

  

  
  

• 通过JSON_SET,JSON_INSERT,JSON_REPLACE来update属性值；

• JSON_EXTRACT：用来从 JSON 数据中提取所需要的字段内容。
  
  
• 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对象。

• 基于生成列的索引(Indexing JSON via a generated column)

1：MySQL可以通过创建生成列并在上面增加索引的方式来索引JSON数据，例如；

ALTER TABLE customers ADD COLUMN zip_code INTEGER GENERATED ALWAYS as (user_info->"$.zipcodel");
ALTER TABLE customers ADD INDEX zip_code (zip_code) USING BTREE;

2：MySQL8.0.13后的版本可通过直接创建函数索引的方式来索引JSON数据，例如；

ALTER TABLE customers ADD INDEX zip_code (CAST(user_info->"$.zip_code" as INTEGER)) USING BTREE;

• 多值索引（Multi valued indexing）

• 创建多值索引：

CREATE INDEX zips ON customers ( (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );
ALTER TABLE customers ADD INDEX comp(id, modified, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)) );

• 利用索引查询：

SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.zipcode’);
SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->‘$.zipcode’,CAST(‘[94507,94582]’ AS JSON));
SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.zipcode',CAST('[94507,94582]' AS JSON));

END

同时欢迎大家扫码👇添加小助手（备注：加入Klustron技术交流群）欢迎大家在交流群共同探讨更多问题及主题。