MYSQL复杂json 查询实战

最新推荐文章于 2023-10-30 10:07:40 发布
最新推荐文章于 2023-10-30 10:07:40 发布
阅读量715 收藏
点赞数
分类专栏： 数据库 文章标签： mysql json 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_28204635/article/details/125558622
版权

查询的数据库数据：
在这里插入图片描述

json数据如下：

{"16055797135198692227118": [{"id": "51000006", "desc": "51000006", "value": "118.800", "quality": "0"}, {"id": "50001002", "desc": "50001002", "value": 
"1811927936.000", "quality": "0"}, {"id": "51000005", "desc": "51000005", "value": "0.000", "quality": "0"}, {"id": "51000004", "desc": "51000004", "value": "118.800",
 "quality": "0"}, {"id": "51000010", "desc": "51000010", "value": "8386.399", "quality": "0"}, {"id": "51000003", "desc": "51000003", "value": "43.200", "quality": "0"}, 
 {"id": "51000002", "desc": "51000002", "value": "22222", "quality": "0"}, {"id": "51000001", "desc": "51000001", "value": "42.800", "quality": "0"}, {"id": "51000011", 
 "desc": "51000011", "value": "3080.000", "quality": "0"}]}

需求：
获取deviceId=16055797135198692227118, id=50001002的value。

sql如下：

SELECT a.DATA_TIME ,JSON_UNQUOTE(JSON_EXTRACT(a.val,JSON_UNQUOTE(REPLACE(a.path,'id','value'))))  value from (
SELECT DATA_TIME,JSON_EXTRACT( DATA_VALUE, '$."16055797135198692227118"' ) val,JSON_SEARCH(JSON_EXTRACT( DATA_VALUE, '$."16055797135198692227118"' ),'one','50001002') path
from dt_curve
where data_time like '2022-06-29 %' and DEVICE_ID ='16055797135198692227118' ORDER BY DATA_TIME desc ) a

===================================================================================
以下是建表语句和数据。可以直接测试。

CREATE TABLE `dt_curve` (
  `DEVICE_ID` varchar(128) NOT NULL,
  `SN` varchar(32) DEFAULT NULL,
  `DATA_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `DATA_VALUE` json DEFAULT NULL,
  `REFRESH_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`DEVICE_ID`,`DATA_TIME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='曲线数据';

INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 16:30:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811927936.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"8386.399\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"43.200\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"22222\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"42.800\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"3080.000\", \"quality\": \"0\"}]}', '2022-06-29 16:30:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 16:20:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811926144.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"8258.800\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"43.200\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"42.400\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"3080.000\", \"quality\": \"0\"}]}', '2022-06-29 16:20:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 16:10:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811924864.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"8245.600\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"42.800\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"42.800\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"3080.000\", \"quality\": \"0\"}]}', '2022-06-29 16:10:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 16:00:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811923456.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"5491.200\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"26.800\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"39.200\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"2200.000\", \"quality\": \"0\"}]}', '2022-06-29 16:00:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 15:50:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811922560.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"5051.200\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"24.000\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"25.200\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"880.000\", \"quality\": \"0\"}]}', '2022-06-29 15:50:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 15:40:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811921664.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"5020.400\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"25.200\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"25.200\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"880.000\", \"quality\": \"0\"}]}', '2022-06-29 15:40:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 15:30:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811921408.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"4835.600\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"23.200\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"24.400\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"1320.000\", \"quality\": \"0\"}]}', '2022-06-29 15:30:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 15:20:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811920000.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"4659.600\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"22.400\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"22.400\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"880.000\", \"quality\": \"0\"}]}', '2022-06-29 15:20:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 15:10:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811919488.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"3854.400\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"18.800\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"18.400\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"0.000\", \"quality\": \"0\"}]}', '2022-06-29 15:10:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 15:00:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811919104.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"3779.600\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"18.000\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"19.200\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"440.000\", \"quality\": \"0\"}]}', '2022-06-29 15:00:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 14:50:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811918336.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"3722.400\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"18.400\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"18.400\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"440.000\", \"quality\": \"0\"}]}', '2022-06-29 14:50:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 14:40:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811917824.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"3801.600\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"18.400\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"17.200\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"440.000\", \"quality\": \"0\"}]}', '2022-06-29 14:40:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 14:30:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811917440.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"3810.400\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"18.400\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"18.400\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"440.000\", \"quality\": \"0\"}]}', '2022-06-29 14:30:00');
INSERT INTO `test`.`dt_curve` (`DEVICE_ID`, `SN`, `DATA_TIME`, `DATA_VALUE`, `REFRESH_TIME`) VALUES ('16055797135198692227118', '1605579685416505559820', '2022-06-29 14:20:00', '{\"16055797135198692227118\": [{\"id\": \"51000006\", \"desc\": \"51000006\", \"value\": \"117.700\", \"quality\": \"0\"}, {\"id\": \"50001002\", \"desc\": \"50001002\", \"value\": \"1811916544.000\", \"quality\": \"0\"}, {\"id\": \"51000005\", \"desc\": \"51000005\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000004\", \"desc\": \"51000004\", \"value\": \"118.800\", \"quality\": \"0\"}, {\"id\": \"51000010\", \"desc\": \"51000010\", \"value\": \"3612.400\", \"quality\": \"0\"}, {\"id\": \"51000003\", \"desc\": \"51000003\", \"value\": \"17.200\", \"quality\": \"0\"}, {\"id\": \"51000002\", \"desc\": \"51000002\", \"value\": \"0.000\", \"quality\": \"0\"}, {\"id\": \"51000001\", \"desc\": \"51000001\", \"value\": \"16.800\", \"quality\": \"0\"}, {\"id\": \"51000011\", \"desc\": \"51000011\", \"value\": \"440.000\", \"quality\": \"0\"}]}', '2022-06-29 14:20:00');
程小明的coding
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
Mysql45讲pdf版
05-23
14. 其他高级特性：包括分区表、窗口函数、JSON支持、全文搜索等MySQL的高级功能。 通过《MySQL实战45讲》的学习，读者应该能够掌握MySQL的基础知识，并具备解决实际问题的能力。不过，实际应用中，理论知识与实践...
manacher
weiya040214的博客
10-15 80
#include<bits/stdc++.h> #define maxn 51000010 using namespace std; int n,f[maxn]; char a[maxn],s[maxn<<1]; void change() { s[0]='#'; s[1]='#'; for (int i=0;i<n;i++) s[i*2+2]=a[i],...
Mysql中json类型数据查询
m0_64053569的博客
07-26 3422
在实际开发中用到，在这里浅谈一下。
mysql复杂 json 查询,在mysql中按键查找json
weixin_36158842的博客
02-18 322
I have a json and i want to extract the value of a key. I know the key name but not the location. My json is complex and would look like this in mysql:set @jsonstr:='{"glossary": {"title": "example gl...
Mysql5.7 + 查询并解析json数据方法
stuild stuil 的博客
09-02 5314
Mysql5.7 + 查询并解析json数据方法
MySQL_关于JSON数据的查询
weixin_44826433的博客
01-06 6964
MySQL中关于JSON类型数据的查询
实战派MySQL高阶应用指南.rar
12-29
最后，书中会介绍MySQL的最新特性和趋势，如InnoDB存储引擎的改进、JSON支持、分布式事务处理等，让你保持对MySQL技术发展的敏锐度。 总的来说，《实战派MySQL高阶应用指南》是一本全面覆盖MySQL高级特性的参考书，...
《MySQL 数据库实战应用》课程教学.zip
最新发布
05-20
《MySQL 数据库实战应用》是一门深度探讨数据库管理和开发的课程，主要针对使用MySQL进行数据存储、查询和管理的专业人士。这门课程的教学资源压缩包包含了一篇详细的教学文档，旨在帮助学习者全面掌握MySQL的核心...
mysql45讲.zip
04-20
16. **JSON支持**：MySQL 5.7及以后版本对JSON数据类型的内置支持，以及如何处理JSON字段。 17. **窗口函数与行级操作**：如ROW_NUMBER()、RANK()、LEAD()和LAG()等，用于复杂数据分析。 18. **存储引擎的扩展性**...
go实战之mysql、redis、web
03-20
在本项目中，“Go实战之MySQL、Redis、Web”涵盖了三个关键领域的应用：数据库操作（MySQL）、内存数据存储（Redis）以及Web开发。这是一份深入学习Go语言实际开发技能的资源，通过实践来提升对这些技术的理解和运用...
MySQL对JSON数据进行查询
qq_44742278的博客
05-19 9705
MySQL根据JSON字段的内容检索查询数据
mysql 处理json数组
weixin_38081382的博客
10-28 2025
mysql json数组拆分
MySql查询JSON字符串(包含多层级复杂JSON查询)
summer的博客
10-30 1541
{“a”: “zhangsan”, “b”: “13”, “person”: {“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]}][{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}][{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}2.精确查询json类型字段。
MYSQL中对复杂JSON的更新
学无止境
06-24 4048
用法：JSON_REPLACE(json_doc, path, val[, path, val] …)用例： 条件更新表A中的复杂JSON字段componment_data中的数据 附上官方链接​​​​mysql官方文档JSON函数
MySQL JSON：对象及数组查询详解
fantasy27149的博客
03-02 3803
MySQL允许开发人员将JSON字段作为关系数据库表中的行存储，并且还提供了一些新的函数和运算符来实现查询这些字段的数据。MySQL的JSON支持有助于简化开发和减轻数据库管理员的压力，同时还能够提高数据库的性能和可靠性。
MySql中json类型数据的查询以及在MyBatis-Plus中的使用
m0_73311735的博客
09-15 1万+
INSERT INTO `ctts_dev`.`json_test`(`id`, `roles`, `project`) VALUES (1, '[{"id": 10001, "name": "管理员"}, {"id": 10002, "name": "开发"}, {"id": 10003, "name": "测试"}]', '{"id": 11111, "name": "项目1"}');如果是字符串，一个箭头返回的结果是带双引号的（只针对字符串），两个箭头是不带的。"name": "测试1111",
mysql 复杂类型_MySQL 复杂数据类型之JSON数据
weixin_39900468的博客
01-19 455
JSON数据： 是Java script Object Notation 的简称。JSON是用于存储和交换数据的语法。JSON是使用JavaScript对象表示法编写的文本。在浏览器和服务器之间交换数据时，数据只能是文本，JSON是文本，我们可以将任何JavaScript对象转换为JSON，然后将JSON发送到服务器，也可以将从服务器接收到的任何JSON转换为JavaScript 对象。这样，我们...
mysql获取json数据
m0_45457434的博客
08-05 3273
mysql取json数据
怎么通过SQL取出数据库中JSON字段中的值
热门推荐
Counter-Strike大牛
03-12 1万+
我们的数据库中经常会遇到很多JSON的字段，自己写的也好，别人写的也好。一般我们取这个值的话，会创建一个typeHandler来取值。那么如果我们想直接取到JSON里的值该怎么办呢？其实很简单，SQL自带的函数就可以解决。 加入现在有一个字段叫detial，字段内容如下： {"name":"Bruce","age":24,"gender":"男"} 那么我们取出name的话，写法如下： select JSON_EXTRACT(detail, '$.name') from ...; 这样就可以直接取到JS
7、NIFI综合应用场景-将mysql查询出的json数据转换成txt后存储至HDFS中
05-29
在NIFI的综合应用场景中，本文探讨了如何将MySQL查询得到的JSON数据经过处理转换为TXT格式，并将其存储到HDFS（Hadoop分布式文件系统）中。这一过程是在之前章节“6、NIFI综合应用场景-离线同步Mysql数据到HDFS中”...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程小明的coding

你的打赏是我研究最大的动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值