mysql从5.7后引入了json数据类型以及json函数，可以有效得访问json格式得数据。json数据类型相对于字符串，具有以下优点：
1）对于json列数据提供自动校验json格式，错误格式会提示错误；
2）优化存储类型。数据以二进制方式保存，读取效率快；
3）允许通过键值或者数据索引查找对象，无需把整个数据读取出来；
除此之外，json还有以下特点：
1）json存储空间大致于longblob或longtext差不多；
2）mysql8.0.13之后，json允许默认值为null；
3）json列不能设置索引，可通过json中得键值设置索引来提高查询效率；
4）json中null、true、false必须使用小写，
二、使用示例SELECt VERSION(); -- 5.7以上才支持
建表
CREATE TABLE `point_data` ( `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '自增id', `device_id` char(32) COMMENT '设备id', `uid` char(32) COMMENT '设备uid', `product_id` char(32) COMMENT '产品id', `point` json DEFAULT NULL COMMENT 'json类型得字段', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `status` char(1) DEFAULT '1' COMMENT '1.有效 0.无效', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
测试数据
insert into point_data(id,device_id,uid,product_id,point) values(1783248988,'c95f1032397248d39c175b0b30161a52','ee932240a6d3de4e65c34ba8bb79d089','b634af1c1e3b4f84bc22ceb7042c8574','{\\"brightness\\":{\\"i\\":7,\\"n\\":\\"brightness\\",\\"t\\":1,\\"v\\":\\"165\\",\\"time\\":1647928879000},\\"work_mode\\":{\\"i\\":2,\\"n\\":\\"work_mode\\",\\"t\\":1,\\"v\\":\\"198\\",\\"time\\":1647928879000},\\"music_state\\":{\\"i\\":9,\\"n\\":\\"music_state\\",\\"t\\":1,\\"v\\":\\"228\\",\\"time\\":1647928879000},\\"temp_value\\":{\\"i\\":12,\\"n\\":\\"temp_value\\",\\"t\\":4,\\"v\\":\\"21\\",\\"time\\":1647928879000},\\"warm_switch\\":{\\"i\\":11,\\"n\\":\\"warm_switch\\",\\"t\\":9,\\"v\\":\\"false\\",\\"time\\":1647928879000}}');insert into point_data(id,device_id,uid,product_id,point) values(1783248989,'c95f1032397248d39c175b0b30161a53','ee932240a6d3de4e65c34ba8bb79d080','b634af1c1e3b4f84bc22ceb7042c8574','[\\"brightness\\":{\\"i\\":7,\\"n\\":\\"brightness\\",\\"t\\":1,\\"v\\":\\"165\\",\\"time\\":1647928879000},\\"work_mode\\":{\\"i\\":2,\\"n\\":\\"work_mode\\",\\"t\\":1,\\"v\\":\\"198\\",\\"time\\":1647928879000},\\"music_state\\":{\\"i\\":9,\\"n\\":\\"music_state\\",\\"t\\":1,\\"v\\":\\"228\\",\\"time\\":1647928879000},\\"temp_value\\":{\\"i\\":12,\\"n\\":\\"temp_value\\",\\"t\\":4,\\"v\\":\\"21\\",\\"time\\":1647928879000},\\"warm_switch\\":{\\"i\\":11,\\"n\\":\\"warm_switch\\",\\"t\\":9,\\"v\\":\\"false\\",\\"time\\":1647928879000}]');insert into point_data(id,device_id,uid,product_id,point) values(1783248980,'c95f1032397248d39c175b0b30161a54','ee932240a6d3de4e65c34ba8bb79d081','b634af1c1e3b4f84bc22ceb7042c8574','{"brightness":{"i":7,"n":"brightness","t":1,"v":"165","time":1647928879000},"work_mode":{"i":2,"n":"work_mode","t":1,"v":"198","time":1647928879000},"music_state":{"i":9,"n":"music_state","t":1,"v":"228","time":1647928879000},"temp_value":{"i":12,"n":"temp_value","t":4,"v":"21","time":1647928879000},"warm_switch":{"i":11,"n":"warm_switch","t":9,"v":"false","time":1647928879000}}');insert into point_data(id,device_id,uid,product_id,point) values(1783248981,'c95f1032397248d39c175b0b30161a55','ee932240a6d3de4e65c34ba8bb79d082','b634af1c1e3b4f84bc22ceb7042c8574','["brightness":{"i":7,"n":"brightness","t":1,"v":"165","time":1647928879000},"work_mode":{"i":2,"n":"work_mode","t":1,"v":"198","time":1647928879000},"music_state":{"i":9,"n":"music_state","t":1,"v":"228","time":1647928879000},"temp_value":{"i":12,"n":"temp_value","t":4,"v":"21","time":1647928879000},"warm_switch":{"i":11,"n":"warm_switch","t":9,"v":"false","time":1647928879000}]');
查询json字段
SELECT POINT -> '$."work_mode"' work_modeFROM point_dataWHERe id = 1783248988;SELECt POINT -> '$."work_mode"."i"' work_modeFROM point_dataWHERe id = 1783248988;三、JSON函数
感谢分享dev.mysql感谢原创分享者/doc/refman/5.7/en/json-creation-functions.html
感谢分享特别cnblogs感谢原创分享者/ingxx/p/15880021.html
构建json得函数将参数返回成一个JSON数组
SELECt JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME());+---------------------------------------------+| JSON_ARRAY(1, "abc", NULL, TRUE, CURTIME()) |+---------------------------------------------+| [1, "abc", null, true, "11:30:24.000000"] |+---------------------------------------------+
接收键值对参数，并返回成JSON对象
SELECT JSON_OBJECT('id', 87, 'name', 'carrot');+-----------------------------------------+| JSON_OBJECT('id', 87, 'name', 'carrot') |+-----------------------------------------+| {"id": 87, "name": "carrot"} |+-----------------------------------------+
SELECT JSON_QUOTE('null'), JSON_QUOTE('"null"');+--------------------+----------------------+| JSON_QUOTE('null') | JSON_QUOTE('"null"') |+--------------------+----------------------+| "null" | "\\"null\\"" |+--------------------+----------------------+搜索JSON值得函数
判断target中指定path是否含有candidate，返回1表示包含
SET 等j = '{"a": 1, "b": 2, "c": {"d": 4}}';SET 等j2 = '1';SELECT JSON_ConTAINS(等j, 等j2, '$.a');+-------------------------------+| JSON_ConTAINS(等j, 等j2, '$.a') |+-------------------------------+| 1 |+-------------------------------+SELECT JSON_ConTAINS(等j, 等j2, '$.b');+-------------------------------+| JSON_ConTAINS(等j, 等j2, '$.b') |+-------------------------------+| 0 |+-------------------------------+SET 等j2 = '{"d": 4}';SELECT JSON_ConTAINS(等j, 等j2, '$.a');+-------------------------------+| JSON_ConTAINS(等j, 等j2, '$.a') |+-------------------------------+| 0 |+-------------------------------+SELECT JSON_ConTAINS(等j, 等j2, '$.c');+-------------------------------+| JSON_ConTAINS(等j, 等j2, '$.c') |+-------------------------------+| 1 |+-------------------------------+
判断JSON文档中是否包含指定path
'one': 1 if at least one path exists within the document, 0 otherwise.
'all': 1 if all paths exist within the document, 0 otherwise.
SET 等j = '{"a": 1, "b": 2, "c": {"d": 4}}';SELECT JSON_CONTAINS_PATH(等j, 'one', '$.a', '$.e');+---------------------------------------------+| JSON_CONTAINS_PATH(等j, 'one', '$.a', '$.e') |+---------------------------------------------+| 1 |+---------------------------------------------+SELECT JSON_CONTAINS_PATH(等j, 'all', '$.a', '$.e');+---------------------------------------------+| JSON_CONTAINS_PATH(等j, 'all', '$.a', '$.e') |+---------------------------------------------+| 0 |+---------------------------------------------+SELECT JSON_CONTAINS_PATH(等j, 'one', '$.c.d');+----------------------------------------+| JSON_CONTAINS_PATH(等j, 'one', '$.c.d') |+----------------------------------------+| 1 |+----------------------------------------+SELECT JSON_CONTAINS_PATH(等j, 'one', '$.a.d');+----------------------------------------+| JSON_CONTAINS_PATH(等j, 'one', '$.a.d') |+----------------------------------------+| 0 |+----------------------------------------+
根据指定path提取数据
SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]');+--------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]') |+--------------------------------------------+| 20 |+--------------------------------------------+SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]');+----------------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[1]', '$[0]') |+----------------------------------------------------+| [20, 10] |+----------------------------------------------------+SELECT JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]');+-----------------------------------------------+| JSON_EXTRACT('[10, 20, [30, 40]]', '$[2][*]') |+-----------------------------------------------+| [30, 40] |+-----------------------------------------------+
SELECT JSON_EXTRACT (`point`, '$[0]') AS ONE,JSON_EXTRACT (`point`, '$[1]') twoFROM point_data;
