管她前浪，还是后浪？
能浪的浪，才是好浪！
每天 10:33更新文章，每天掉亿点点头发…
源码精品专栏
原创 | Java 2021超神之路，很肝~
中文详细注释的开源项目
RPC 框架 Dubbo 源码解析
网络应用框架 Netty 源码解析
消息中间件 RocketMQ 源码解析
数据库中间件 Sharding-JDBC 和 MyCAT 源码解析
作业调度中间件 Elastic-Job 源码解析
分布式事务中间件 TCC-Transaction 源码解析
Eureka 和 Hystrix 源码解析
Java 并发源码
article/details/116594654
JSON 数据类型业务表结构设计实战用户登录设计用户画像设计总结
关系型的结构化存储存在一定的弊端，因为它需要预先定义好所有的列以及列对应的类型。但是业务在发展过程中，或许需要扩展单个列的描述功能，这时，如果能用好 JSON 数据类型，那就能打通关系型和非关系型数据的存储之间的界限，为业务提供更好的架构选择。
当然，很多同学在用 JSON 数据类型时会遇到各种各样的问题，其中最容易犯的误区就是将类型 JSON 简单理解成字符串类型 。但当你看完这篇文章后，会真正认识到 JSON 数据类型的威力，从而在实际工作中更好地存储非结构化的数据。
JSON 数据类型
JSON（JavaScript Object Notation）主要用于互联网应用服务之间的数据交换。MySQL 支持RFC 7159定义的 JSON 规范，主要有 JSON 对象 和 JSON 数组 两种类型。下面就是 JSON 对象，主要用来存储图片的相关信息：
{“Image”:{“Width”:800,”Height”:600,”Title”:”Viewfrom15thFloor”,”Thumbnail”:{“Url”:”http://www.example.com/image/481989943″,”Height”:125,”Width”:100},”IDs”:[116,943,234,38793]}}
从中你可以看到， JSON 类型可以很好地描述数据的相关内容，比如这张图片的宽度、高度、标题等（这里使用到的类型有整型、字符串类型）。
JSON对象除了支持字符串、整型、日期类型，JSON 内嵌的字段也支持数组类型，如上代码中的 IDs 字段。
另一种 JSON 数据类型是数组类型，如：
[{“precision”:”zip”,”Latitude”:37.7668,”Longitude”:-122.3959,”Address”:””,”City”:”SANFRANCISCO”,”State”:”CA”,”Zip”:”94107″,”Country”:”US”},{“precision”:”zip”,”Latitude”:37.371991,”Longitude”:-122.026020,”Address”:””,”City”:”SUNNYVALE”,”State”:”CA”,”Zip”:”94085″,”Country”:”US”}]
上面的示例演示的是一个 JSON 数组，其中有 2 个 JSON 对象。
到目前为止，可能很多同学会把 JSON 当作一个很大的字段串类型，从表面上来看，没有错。但本质上，JSON 是一种新的类型，有自己的存储格式，还能在每个对应的字段上创建索引，做特定的优化，这是传统字段串无法实现的。JSON 类型的另一个好处是无须预定义字段 ，字段可以无限扩展。而传统关系型数据库的列都需预先定义，想要扩展需要执行 ALTER TABLE … ADD COLUMN … 这样比较重的操作。
需要注意是，JSON 类型是从 MySQL 5.7 版本开始支持的功能，而 8.0 版本解决了更新 JSON 的日志性能瓶颈。如果要在生产环境中使用 JSON 数据类型，强烈推荐使用 MySQL 8.0 版本。
讲到这儿，你已经对 JSON 类型的基本概念有所了解了，接下来，我们进入实战环节：如何在业务中用好JSON类型？
“
推荐下自己做的 Spring Boot 的实战项目：
https://github.com/YunaiV/ruoyi-vue-pro
业务表结构设计实战用户登录设计
在数据库中，JSON 类型比较适合存储一些修改较少、相对静态的数据 ，比如用户登录信息的存储如下：
DROPTABLEIFEXISTSUserLogin;CREATETABLEUserLogin(userIdBIGINTNOTNULL,loginInfoJSON,PRIMARYKEY(userId));
接着，插入下面的数据：
而如果不采用 JSON 数据类型，就要用下面的方式建表：
SELECTuserId,JSON_UNQUOTE(JSON_EXTRACT(loginInfo,”$.cellphone”))cellphone,JSON_UNQUOTE(JSON_EXTRACT(loginInfo,”$.wxchat”))wxchatFROMUserLogin; ——– ————- ————– |userId|cellphone|wxchat| ——– ————- ————– |1|13918888888|破产码农||2|15026888888|NULL| ——– ————- ————– 2rowsinset(0.01sec)
当然了，每次写 JSON_EXTRACT、JSON_UNQUOTE 非常麻烦，MySQL 还提供了 ->> 表达式，和上述 SQL 效果完全一样：
SELECTuserId,loginInfo->>”$.cellphone”cellphone,loginInfo->>”$.wxchat”wxchatFROMUserLogin;
当 JSON 数据量非常大，用户希望对 JSON 数据进行有效检索时，可以利用 MySQL 的 函数索引 功能对 JSON 中的某个字段进行索引。
ALTERTABLEUserLoginADDCOLUMNcellphoneVARCHAR(255)AS(loginInfo->>”$.cellphone”);ALTERTABLEUserLoginADDUNIQUEINDEXidx_cellphone(cellphone);
上述 SQL 首先创建了一个虚拟列 cellphone，这个列是由函数 loginInfo->>”$.cellphone” 计算得到的。然后在这个虚拟列上创建一个唯一索引 idx_cellphone。这时再通过虚拟列 cellphone 进行查询，就可以看到优化器会使用到新创建的 idx_cellphone 索引：
EXPLAINSELECT*FROMUserLoginWHEREcellphone=’13918888888’\G***************************1.row***************************id:1select_type:SIMPLEtable:UserLoginpartitions:NULLtype:constpossible_keys:idx_cellphonekey:idx_cellphonekey_len:1023ref:constrows:1filtered:100.00Extra:NULL1rowinset,1warning(0.00sec)
当然，我们可以在一开始创建表的时候，就完成虚拟列及函数索引的创建。如下表创建的列 cellphone 对应的就是 JSON 中的内容，是个虚拟列；uk_idx_cellphone 就是在虚拟列 cellphone 上所创建的索引。
CREATETABLEUserLogin(userIdBIGINT,loginInfoJSON,cellphoneVARCHAR(255)AS(loginInfo->>”$.cellphone”),PRIMARYKEY(userId),UNIQUEKEYuk_idx_cellphone(cellphone));用户画像设计
某些业务需要做用户画像（也就是对用户打标签），然后根据用户的标签，通过数据挖掘技术，进行相应的产品推荐。比如：
在电商行业中，根据用户的穿搭喜好，推荐相应的商品；在音乐行业中，根据用户喜欢的音乐风格和常听的歌手，推荐相应的歌曲；在金融行业，根据用户的风险喜好和投资经验，推荐相应的理财产品。
在这，我强烈推荐你用 JSON 类型在数据库中存储用户画像信息，并结合 JSON 数组类型和多值索引的特点进行高效查询。假设有张画像定义表：
CREATETABLETags(tagIdbigintauto_increment,tagNamevarchar(255)NOTNULL,primarykey(tagId));SELECT*FROMTags; ——- ————– |tagId|tagName| ——- ————– |1|70后||2|80后||3|90后||4|00后||5|爱运动||6|高学历||7|小资||8|有房||9|有车||10|常看电影||11|爱网购||12|爱外卖| ——- ————–
可以看到，表 Tags 是一张画像定义表，用于描述当前定义有多少个标签，接着给每个用户打标签，比如用户 David，他的标签是 80 后、高学历、小资、有房、常看电影；用户 Tom，90 后、常看电影、爱外卖。
若不用 JSON 数据类型进行标签存储，通常会将用户标签通过字符串，加上分割符的方式，在一个字段中存取用户所有的标签：
——- ————————————— |用户|标签| ——- ————————————— |David |80后；高学历；小资；有房；常看电影||Tom |90后；常看电影；爱外卖| ——- —————————————
这样做的缺点是 ：不好搜索特定画像的用户，另外分隔符也是一种自我约定，在数据库中其实可以任意存储其他数据，最终产生脏数据。
用 JSON 数据类型就能很好解决这个问题：
DROPTABLEIFEXISTSUserTag;CREATETABLEUserTag(userIdbigintNOTNULL,userTagsJSON,PRIMARYKEY(userId));INSERTINTOUserTagVALUES(1,'[2,6,8,10]’);INSERTINTOUserTagVALUES(2,'[3,10,12]’);
其中，userTags 存储的标签就是表 Tags 已定义的那些标签值，只是使用 JSON 数组类型进行存储。
MySQL 8.0.17 版本开始支持 Multi-Valued Indexes，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。所以你可以在表 UserTag 上创建 Multi-Valued Indexes：
ALTERTABLEUserTagADDINDEXidx_user_tags((cast((userTags->”$”)asunsignedarray)));
如果想要查询用户画像为常看电影的用户，可以使用函数 MEMBER OF：
EXPLAINSELECT*FROMUserTagWHERE10MEMBEROF(userTags->”$”)\G***************************1.row***************************id:1select_type:SIMPLEtable:UserTagpartitions:NULLtype:refpossible_keys:idx_user_tagskey:idx_user_tagskey_len:9ref:constrows:1filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)SELECT*FROMUserTagWHERE10MEMBEROF(userTags->”$”); ——– ————— |userId|userTags| ——– ————— |1|[2,6,8,10]||2|[3,10,12]| ——– ————— 2rowsinset(0.00sec)
如果想要查询画像为 80 后，且常看电影的用户，可以使用函数 JSON_CONTAINS：
EXPLAINSELECT*FROMUserTagWHEREJSON_CONTAINS(userTags->”$”,'[2,10]’)\G***************************1.row***************************id:1select_type:SIMPLEtable:UserTagpartitions:NULLtype:rangepossible_keys:idx_user_tagskey:idx_user_tagskey_len:9ref:NULLrows:3filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)SELECT*FROMUserTagWHEREJSON_CONTAINS(userTags->”$”,'[2,10]’); ——– ————— |userId|userTags| ——– ————— |1|[2,6,8,10]| ——– ————— 1rowinset(0.00sec)
如果想要查询画像为 80 后、90 后，且常看电影的用户，则可以使用函数 JSON_OVERLAP：
EXPLAINSELECT*FROMUserTagWHEREJSON_OVERLAPS(userTags->”$”,'[2,3,10]’)\G***************************1.row***************************id:1select_type:SIMPLEtable:UserTagpartitions:NULLtype:rangepossible_keys:idx_user_tagskey:idx_user_tagskey_len:9ref:NULLrows:4filtered:100.00Extra:Usingwhere1rowinset,1warning(0.00sec)SELECT*FROMUserTagWHEREJSON_OVERLAPS(userTags->”$”,'[2,3,10]’); ——– ————— |userId|userTags| ——– ————— |1|[2,6,8,10]||2|[3,10,12]| ——– ————— 2rowsinset(0.01sec)“
推荐下自己做的 Spring Cloud 的实战项目：
https://github.com/YunaiV/onemall
总结
JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题。最后，我总结下今天的重点内容：
使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes；JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；JSON 数据类型推荐使用在不经常更新的静态数据存储。
欢迎加入我的知识星球，一起探讨架构，交流源码。加入方式，长按下方二维码噢：
