本文转载自微信公众号《数据与云》，作者崔虎龙。转载本文请联系数据和云公众号。现在在很多应用环境中都能看到JSON灵活的影子。可以很好地区分每个阶段数据层次的递归层次。一直很期待MySQL的JSON，最近才有时间研究。JSON理解JSON是一串串的字符串，只是其中的元素会用特定的符号来标记。例如：{}双括号代表对象[]方括号代表数组""双引号是属性或值：冒号代表后者是前者的值。在关系数据库中实现JSON的难点在于关系数据库需要定义数据库和表结构。针对这一点，从MySQL5.7开始，MySQL支持JavaScriptObjectNotation(JSON)数据类型。以前，此类数据不是单独的数据类型，而是作为字符串存储。新的JSON数据类型提供自动验证的JSON文档和优化的存储格式。MySQL中的JSON文档以二进制格式存储，它提供了以下功能：自动验证存储在JSON列中的JSON文档。无效文档会产生错误。优化存储格式。存储在JSON列中的JSON文档被转换为允许对文档元素进行快速读取访问的内部格式。以二进制格式存储的JSON值。快速读取文档元素。当服务器再次读取JSON文档时，不需要重新解析文本来获取值。无需读取文档中的所有值，直接通过键或数组索引查找子对象或嵌套值。存储JSON文档所需的空间与LONGBLOB或LONGTEXT大致相同。存储在JSON列中的任何JSON文档的大小都限于max_allowed_pa??cket系统变量的值。在MySQL8.0.13之前，JSON列不能有非NULL默认值。JSON操作数据保存到MySQL，在操作方面有什么支持？目前MySQL8.0版本的JSON一共支持32个常用函数和2个空间函数：1.Index：JSON列和其他二进制类型的列一样，不直接是Index；相反，您可以在生成的列上创建索引以从JSON列中提取标量值。有关详细示例，请参阅索引生成的列以提供JSON列索引。MySQL优化器还在虚拟列上查找与JSON表达式匹配的兼容索引。在MySQL8.0.17及更高版本中，InnoDB存储引擎支持JSON数组上的多值索引。请参见多值索引。MySQLNDBCluster8.0支持JSON列和MySQLJSON函数，包括在从JSON列生成的列上创建索引，作为无法索引JSON列的解决方法。每个NDB表最多支持3个JSON列。2、JSON值的比较和排序：JSON值可以使用=、<、<=、>、>=、<>、!=、<=>运算符进行比较。JSON值不支持以下比较运算符和函数：BETWEENIN()GREATEST()LEAST()对于列出的比较运算符和函数，解决方法是将JSON值转换为本机MySQL数字或字符串数??据类型，以便它们具有一致的非JSON标量类型。也就是说转换成需要的MySQL字段再继续转换也是一种妥协。JSON值有两个级别的比较。第一级比较是基于被比较的值的JSON类型。如果类型不同，则只有哪种类型优先才能确定比较结果。如果两个值具有相同的JSON类型，则使用特定于类型的规则进行二级比较。BLOB>BIT>OPAQUE>DATETIME>TIME>DATE>BOOLEAN>ARRAY>OBJECT>STRING>INTEGER,DOUBLE>NULL。3、JSON值与非JSON值之间的转换：MySQL在JSON值与其他类型值之间转换时遵循的规则：CAST（其他类型ASJSON）的结果是一个JSON类型的NULL值。mysql>SET@j5='{"id":123,"name":"kevin","age":20,"time":"2021-06-0101:00:00"}';QueryOK,0rowsaffected(0.00sec)mysql>SELECTCAST(JSON_EXTRACT(@j5,'$.age')ASUNSIGNED);+----------------------------------------------+|CAST(JSON_EXTRACT(@j5,'$.age')ASUNSIGNED)|+------------------------------------------+|20|+--------------------------------------------+1rowinset(0.00sec)4.JSON值聚合：对于JSON值对于聚合，NULL值像其他数据类型一样被忽略。除MIN()、MAX()、GROUP_CONCAT()外，非NULL值都转为数值类型进行聚合。对于数值标量的JSON值，（取决于值）可能会发生截断和精度损失。JSON使用索引：MySQLJSON列无法创建索引，索引是通过从JSON列中提取标量值来创建的。这样可以更有效的结合MySQL的优点。MySQL优化器在与JSON表达式匹配的虚拟列上查找兼容索引。在MySQL8.0.17及之后的版本中，InnoDB存储引擎支持在JSON数组上建立多值索引MySQLNDBCluster8.0支持JSON列和MySQLJSON函数，包括在从JSON列生成的列上创建索引，作为non-indexableJSONcolumns的解决方案。每个NDB表最多支持3个JSON列。1、虚拟列索引：col_namedata_type[GENERATEDALWAYS]AS(expr)[VIRTUAL|STORED][NOTNULL|NULL][UNIQUE[KEY]][[PRIMARY]KEY][COMMENT'string']VIRTUAL或STORED关键字表示列值如何它的存储对列的使用有很大影响：VIRTUAL：不存储列值，而是在读取行时在任何[BEFOREtrigger]之后立即计算列值。虚拟列不占用存储空间，只是暂时驻留在内存中。目前没有官方限制设置。STORED：当插入或更新一行时，计算并存储列值。存储列需要存储空间并且可以被索引。如果未指定关键字，则默认为VIRTUAL。mysql>DROPTABLEIFEXISTS`jemp`;mysql>CREATETABLE`jemp`(idBIGINTNOTNULLAUTO_INCREMENTPRIMARYKEY,cJSON,dJSON,gINTGENERATEDALWAYSAS(c->"$.id")STORED,INDEXi(g));QueryOK,0rowsaffected(0.02sec)mysql>INSERTINTOjemp(c,d)VALUES('{"id":"1","name":"Fred"}','{"user":"Fred","user_id":1,"zipcode":"[14471,14531]"}'),('{"id":"2","name":"Wilma"}','{"user":"Wilma","user_id":2,"zipcode":[24472,24532]}'),('{"id":"3","name":"Jack"}','{"user":"Jack","user_id":3,"zipcode":[34473,34533]}'),('{"id":"4","name":"Betty"}','{"user":"Betty","user_id":4,"zipcode":[44474,44534]}');QueryOK,4rowsaffected(0.02sec)Records:4Duplicates:0Warnings:0mysql>EXPLAINSELECTc->>"$.name"ASnameFROMjempWHEREg>2\G;******************************1.row***************************id:1select_type:SIMPLEtable:jemppartitions:NULLtype:rangepossible_keys:ikey:ikey_len:5ref:NULLrows:2filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)ERROR:Noqueryspecifiedmysql>SHOWWARNINGS\G******************************1.row***************************Level:NoteCode:1003Message:/*select#1*/selectjson_unquote(json_extract(`db1`.`jemp`.`c`,'$.name'))AS`name`from`db1`.`jemp`where(`db1`.`jemp`.`g`>2)1rowinset(0.00sec)2.使用多值搜索直接接口：MEMBEROF(),JSON_CONTAINS(),JSON_OVERLAPS()mysql>ALTERTABLEjempADDINDEXzips((CAST(d->'$.zipcode'ASUNSIGNEDARRAY)));#MEMBEROFmysql>EXPLAINSELECT*FROMjempWHERE24472MEMBEROF(d->'$.zipcode')\G***************************1.行***************************id:1select_type:SIMPLEtable:jemppartitions:NULLtype:refpossible_keys:zipskey:zipskey_len:9ref:constrows:1filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)#JSON_CONTAINSmysql>EXPLAINSELECT*FROMjempWHEREJSON_CONTAINS(d->'$.zipcode',CAST('[14471,14531]'ASJSON))\G;***************************1.行***************************id:1select_type:SIMPLEtable:jemppartitions:NULLtype:rangepossible_keys:zipskey:zipskey_len:9ref:NULLrows:2filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)#JSON_OVERLAPSmysql>EXPLAINSELECT*CPSjempWHERE$JSONd-OVER-z('LAINSELECT*CPSjempWHERE$JSONd-OVER-'LANSELECT[44474,94582]'ASJSON))\G;******************************1.row***************************id:1select_type:SIMPLEtable:jemppartitions:NULLtype:rangepossible_keys:zipskey:zipskey_len:9ref:NULLrows:2filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)从上面的例子来看，数据查询还是基于MySQLB+tree，JSON只是一种数据存储的机制。通过提供对虚拟列的快速访问，它很好地解决了JSON支持的问题。总结MySQL中JSON的结合非常实用，虚拟列索引解决了查询性能问题。JSON的大小确实是个难题，谨慎使用（空间与LONGBLOB或LONGTEXT大致相同，文档大小限制为max_allowed_pa??cket系统变量的值）。实际场景中只能选择适中的JSON长度，大页面可以考虑使用。作者简介崔虎龙，云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，熟悉数据中心运行管理、自动化运维和管理的流程和规范。维护等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障排除、备份恢复、升级迁移、性能优化。自学通过MySQLOCP5.6和MySQLOCP5.7认证。2年以上开发经验，10年数据库运维工作经验，其中8年全职MySQL工作；曾担任项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职位；相关行业：金融（银行业、财务管理）、物流、游戏、医疗、重工业等。
