岳阳网站岳阳建站,常用网站推荐,网站积分商城该怎么建立,推广公众号有哪些方法数据库#xff1a;mysql 5.7版本以上
问题#xff1a;一对多数据#xff0c;实现输出一条数据#xff0c;并将多条数据转换成json对象输出#xff0c;可以实现一对多个字段。
项目中关系较为复杂#xff0c;以下简化数据关系如下#xff1a;
t1是数据表#xff0c;t… 数据库mysql 5.7版本以上
问题一对多数据实现输出一条数据并将多条数据转换成json对象输出可以实现一对多个字段。
项目中关系较为复杂以下简化数据关系如下
t1是数据表t2是用户表一条数据对应授权给多个用户给出每条数据对应授权所有用户和用户名信息 SELECTt1.id,CAST( GROUP_CONCAT( json_object( id, t2.user_id name, t2.name)) AS CHAR ( 10000 ) CHARACTER SET utf8 ) AS jsonStr
FROMt1LEFT JOIN t2 ON t1.id t2.user_id
GROUP BYt1.id
结果
05033d2aa90823bb7ea09851 {id: 01, name: 张三} 150aa723a9ae1e60d6e2c646 {id: 02, name: 王五},{id: 04, name: 李四} 254e636931f52799432933de {id: null, name: null}
ps:如果连表数据为空这里任然有json{id: null, name: null}
这种sql结果有一个弊端json字符串无法直接转换成List的json对象需要加中括号[]
方案一java代码中拼接 StringBuffer stringBuffer new StringBuffer();stringBuffer.append([);stringBuffer.append(data.get(shareJsonStr));stringBuffer.append(]);ListMapString, String shareJsonList JSONArray.parseObject(stringBuffer.toString(), List.class);
方案二concat函数实现
SELECTt1.id,CAST( CONCAT([,GROUP_CONCAT( json_object( id, t2.user_id name, t2.name)),]) AS CHAR ( 10000 ) CHARACTER SET utf8 ) AS jsonStr
FROMt1LEFT JOIN t2 ON t1.id t2.user_id
GROUP BYt1.id
结果
05033d2aa90823bb7ea09851 [{id: 01, name: 张三}] 150aa723a9ae1e60d6e2c646 [{id: 02, name: 王五},{id: 04, name: 李四}] 254e636931f52799432933de [{id: null, name: null}]
但是我在项目中并未使用方案二而是使用方案一
因为在复杂情况下方案二的处理速度过慢不如在代码中使用括号拼接速度快。
我查询资料理论上数据库执行会比java中拼接快可能与具体场景、数据索引、数据库版本执行速度有关。
大数据量场景需要具体问题具体分析。 知识点
1.json_object函数将多个字段转成一个json对象这里用了两个字段。
json_object( id, t2.user_id name, t2.name) mysql需要5.7版本其他数据库大多数有次函数
2.CAST函数json输出类型转换
CAST( json AS CHAR ( 10000 ) CHARACTER SET utf8 ) 数据库json对象是二进制输出字符串需要转换成utf8的方式。 其中char10000代表字符最长字段 mysql可以使用此函数也可以是用convert函数但是convert其他数据库不一定支持相对cast通用性更高。
3.GROUP_CONCAT函数进行分组拼接多条jason对象为一个字符串输出
GROUP_CONCAT( json_object( id, t2.user_id name, t2.name))...GROUP BY t1.id
5.使用中需要加中括号[]实现json的list解析使用
