在PHP的leravel里编写sql,使用了group_conca函数,结果数据长度总是只拿到1024个字节,后面的全部截断了。
$floors = DB::select("
select
build,
floor_id,
floor,
GROUP_CONCAT(func_id,'&&',func,'&&',rooms SEPARATOR '^^') as funcs
from (
select
any_value(b.build) as build,
any_value(bf.id) as floor_id,
any_value(bf.floor) as floor,
any_value(rf.id) as func_id,
any_value(rf.func) as func,
GROUP_CONCAT(r.id,'||',r.prefix,'||',r.room,'||',rt.type,'||',r.r_price,'||',r.`status`,'||',if(req.equipment is null,'',req.equipment),'||',if(wifi.wifi is null,'',wifi.wifi) order by r.room asc SEPARATOR ',,') as rooms
from hotel_company_build_floor_room as r
left join hotel_company_room_func as rf on rf.id = r.room_func_id
left join hotel_company_room_type as rt on rt.id = r.room_type_id
left join hotel_company_build_floor as bf on r.floor_id=bf.id
left join hotel_company_build as b on b.id=bf.build_id
LEFT JOIN hotel_company_build_floor_room_eq as req on r.id=req.room_id
LEFT JOIN hotel_company_build_floor_room_eq_wifi as wifi on req.equipment=wifi.equipment
where r.deleted_at is NULL
and rf.deleted_at is NULL
and rt.deleted_at is NULL
and bf.deleted_at is NULL
and b.deleted_at is null ".$whereStr."
GROUP BY bf.id,rf.id
) as nr GROUP BY floor_id
order by build asc,floor asc
");
问题分析:
这个原因在于,MySQL中的group_concat_max_len默认最大限制是1024长度,所以,只要想办法改变了这个长度,即可解决问题。
参考了网上的说明,解决办法如下:
方法1:
简单粗暴,直接修改mysql配置文件my.cnf,在配置文件中加载如下内容:
group_concat_max_len = 1024000
具体操作:
找到配置文件:find / -name my.cnf
编辑配置文件:vim my.cnf
编辑完成后,重启mysql即可。
方法2:
将代码里登录mysql的用户权限设置为root超级用户。
在PHP代码里增加下面的语句:
DB::statement('SET GLOBAL group_concat_max_len = 1024000');
即可完成!
这样,数据查询到长度即可超过1024字节。
参考:
https://www.cnblogs.com/mr-wuxiansheng/p/7911506.html
http://cn.voidcc.com/question/p-mzveoeon-bhx.html
https://stackoverflow.com/questions/11946012/how-to-add-super-privileges-to-mysql-database