MySQL_JSON格式存取
昨天说了，今天单独拿一篇出来写一下JSON格式的读取。在很久很久以前，MySQL是没有JSON格式的，那个时候还很羡慕非关系型数据库，它们可以自由存储各种数据。MySQL从5.7版本开始就支持JSON格式的，从此也可以对外号称关系型数据和非关系型数据都通吃了。但是最开始对JSON格式数据的优化不是很优化，效率有点低。但这个问题他们自己也肯定知道的，先有再优嘛，于是8.0后MySQL对JSON格式的优化已经很好了，现在使用起来如果数据量不是特别特别大，使用效果还是很好的。
下面就主要介绍一下JSON格式的创建、插入、读取和修改，基本如果不是开发人员、数据库管理人员，作为一个数据分析师应该是够用了。
【创建】
可以使用SQL语句创建，如下，最后就创建了一个带JSON格式的表格↓
CREATE TABLE `salary` ( `姓名` varchar(6) NOT NULL, `月份` varchar(3) NOT NULL, `工资` decimal(12,2) DEFAULT NULL, `个税` decimal(12,2) DEFAULT NULL, `其他费用` json DEFAULT NULL, PRIMARY KEY (`姓名`,`月份`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;当然也是可以通过Navicat直接创建就行了↓
【插入数据】
然后使用常规的INSERT INTO插入数据就行了，需要注意的是JSON格式使用键值对组成的，也可以内部嵌套数据，我们这里插入7条数据，SQL语句如下↓
INSERT INTO salary VALUES('李华','7月',2000,11,'{'chailv':333,'gaowen':222,'xiayu':11,'zhaodai':1200}');INSERT INTO salary VALUES('李华','8月',2200,11,'{'chailv':335,'gaowen':225,'xiayu':45}');INSERT INTO salary VALUES('韩梅梅','7月',4400,33,'{'chailv':158,'gaowen':222}');INSERT INTO salary VALUES('韩梅梅','8月',5500,66,'{'chailv':456,'gaowen':222,'zhaodai':5000}');INSERT INTO salary VALUES('张三三','7月',2000,11,'{'chailv':100,'gaowen':222}');INSERT INTO salary VALUES('张三三','8月',3000,14,'{'chailv':200,'gaowen':222}');INSERT INTO salary VALUES('张三三','10月',4000,51,'{'chailv':300,'gaowen':222,'qita':{'q1':111,'q2':222}}');除了使用{}插入JSON格式数据，还可以使用JSON_OBJECT()函数转换成JSON格式的数据，下面再插入一条数据，SQL语句如下↓
INSERT INTO salary VALUES('李华','9月',2454,15,JSON_OBJECT('chailv',999,'zhaodai',1111));【读取数据】
一般情况下数据分析就用这个查询功能就行了。json_extract()函数：从json中返回想要的字段，用法是，json_extract(字段名,$.json字段名)。也可以使用 json字段名->'$.json属性'进行查询，下面分别有两种查询方式进行演示，把其他费用里面的各项费用单独查询出来。
SELECT 姓名,月份,工资,个税, JSON_EXTRACT(其他费用, '$.chailv') AS 差旅费, JSON_EXTRACT(其他费用, '$.gaowen') AS 高温费, 其他费用->'$.xiayu' AS 下雨费, 其他费用->'$.zhaodai' AS 招待费FROM salaryJSON里面的字段也是可以作为条件进行查询的，比如我们要查询差旅费大于300的人员和月份，SQL预计如下↓
SELECT 姓名,月份,工资,个税, JSON_EXTRACT(其他费用, '$.chailv') AS 差旅费FROM salaryWHERE JSON_EXTRACT(其他费用, '$.chailv')>300如果遇到JSON嵌套的情况，也是可以用两层JSON_EXTRACT函数进行查询的，比如张三三10月其他费用里面有个q1和q2两个费用，现在需要单独查询出来，SQL语句如下↓
SELECT 姓名,月份,工资,个税, JSON_EXTRACT(JSON_EXTRACT(其他费用, '$.qita'),'$.q1') AS Q1费用, JSON_EXTRACT(JSON_EXTRACT(其他费用, '$.qita'),'$.q2') AS Q2费用FROM salaryJSON_KEYS函数可以查询出JSON字段里面的键名字，SQL语句如下↓
SELECT 姓名,月份,工资,个税, JSON_KEYS(`其他费用`)FROM salary【更新数据】
UPDATE salary SET `其他费用` = JSON_SET( `其他费用`, '$.chailv', 10000, '$.gaowen', 2000 ) WHERE 姓名 = '韩梅梅' AND `月份` = '8月'End