mysql 查询json类型数据
来源:互联网 发布:南宁广电网络资费 编辑:程序博客网 时间:2024/06/05 20:59
先查询mysql的版本,如果mysql版本在5.7及以上版本可以使用json格式
select version()
如果 t1表里有一个extra字段,字段是text类型,数据为json格式 {"value":"XMjgxIqqqqqqqqqq"}
通过jSON_EXTRACT可以获取json里面value对于的值
JSON_EXTRACT(t1.extra,'$.value')
得到 "XMjgxIqqqqqqqqqq"
如果想去除两侧引号,需要先做类型转换再做trim
trim(both '"' from cast(JSON_EXTRACT(t1.extra,'$.value') as char))
得到 XMjgxIqqqqqqqqqq
select version()
如果 t1表里有一个extra字段,字段是text类型,数据为json格式 {"value":"XMjgxIqqqqqqqqqq"}
通过jSON_EXTRACT可以获取json里面value对于的值
JSON_EXTRACT(t1.extra,'$.value')
得到 "XMjgxIqqqqqqqqqq"
如果想去除两侧引号,需要先做类型转换再做trim
trim(both '"' from cast(JSON_EXTRACT(t1.extra,'$.value') as char))
得到 XMjgxIqqqqqqqqqq
原始sql和原始结果
select t1.id AS item_id, t1.title AS item_name,t6.id AS topic_id, t6.title AS topic_name, t1.extra AS media_id, t1.biz_type from tem t1 join component_item t2 on t1.id = t2.item_id join component t3 on t2.component_id = t3.id join drawer t4 on t4.id = t3.drawer_id join channel_drawer t5 on t5.drawer_id = t4.id join channel t6 on t6.id = t5.channel_id where t1.biz_type in ("JUMP_TO_SHOW","JUMP_TO_VIDEO") and t1.extra IS NOT NULL and t6.topic_property IS NOT NULL
优化后的sql
select t1.id AS item_id, t1.title AS item_name,t6.id AS topic_id, t6.title AS topic_name, trim(both '"' from cast(JSON_EXTRACT(t1.extra,'$.value') as char)) AS media_id, trim(LEADING 'JUMP_TO_' from t1.biz_type) AS biz_type from item_pre t1 join component_item_pre t2 on t1.id = t2.item_id join component_pre t3 on t2.component_id = t3.id join drawer_pre t4 on t4.id = t3.drawer_id join channel_drawer_pre t5 on t5.drawer_id = t4.id join channel_pre t6 on t6.id = t5.channel_id where t1.biz_type in ("JUMP_TO_SHOW","JUMP_TO_VIDEO") and t1.extra IS NOT NULL and t6.topic_property IS NOT NULL and JSON_EXTRACT(t6.topic_property,'$.group')= "电影"
阅读全文
0 0
- mysql 查询JSON类型数据
- mysql 查询json类型数据
- mysql json类型,java查询出来乱码
- php查询mysql,返回json格式数据
- Mysql,Date类型数据分类查询
- Mysql Long类型时间 当日数据查询
- (转)mysql 查询json编码过的数据
- mysql数据用json对象查询并且存入js数组
- mysql json类型操作
- JSON数据解析类型
- MySQL不规则json查询
- mysql 查询多个类型多条数据并汇总
- mysql查询类型转换
- Mysql里查询字段为Json格式的数据模糊查询方法
- 利用GSON对json数据中的时间类型字符串进行处理,并且进行数据库查询
- Action向页面传送Json类型数据以及myBatis一对多查询
- mysql json类型快速入门
- C#操作json类型数据
- Lesson7 图形用户界面1
- 实现android屏幕共享到pc总结
- Linux添加/删除用户和用户组
- Maven项目打包遇到的两个问题
- 字典映射代替switch
- mysql 查询json类型数据
- Java学习笔记6——Math工具类
- OS学习笔记——多线程模型
- 多线程并发ThreadLocal
- OM模块功能&API详解
- [分享]钛极OS(TiJOS) 应用开发教程Step by Step [强烈推荐]
- Android源码解析之应用程序内部启动Activity过程(startActivity)的源代码分析
- 【多项式】多项式逆元/开方
- Ajax同步顺序执行