mysql - mysql存储过程concat()长字符串

Question

我正在尝试执行动态查询，因为我正在使用 CONCAT() 函数。但我认为我的字符串/查询很长，所以 CONCAT() 不起作用。

set @query:=CONCAT(' SELECT (select GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN 
Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid in (', @MultiDoctorIds ,') 
) as drspec,pm.id as profileid,dam.city,um.profile_url FROM Profile_Master pm LEFT JOIN DAddress_Master dam on 
dam.profileid = pm.id join Unique_Url_Master um on um.clinicid =dam.id WHERE pm.id in (',@MultiDoctorIds,') 
and dam.id=',@clinicId,'  order by CASE WHEN um.clinic_url=''',@ClinicUrl,''' THEN 1 ELSE 2  end,um.clinic_url   ');

select @query;

这是存储过程的一部分，当我执行我的 SP 时，它会显示 @query NULL 但如果我缩短我的查询，那么我会得到完整的查询。

还有其他连接方式吗？？

感谢任何帮助。

Answer 1

临时解决方案是，将您的动态查询存储在局部变量而不是 session 变量中最大长度 .like below

Declare temp_query varchar(4000);

set @query:=CONCAT(' SELECT (select GROUP_CONCAT(sp.specialization) FROM DSpecialization_Master dsp LEFT JOIN 
Specialization_Master sp on sp.id = dsp.specialization WHERE dsp.profileid in (', @MultiDoctorIds ,') 
) as drspec,pm.id as profileid,dam.city,um.profile_url FROM Profile_Master pm LEFT JOIN DAddress_Master dam on 
dam.profileid = pm.id join Unique_Url_Master um on um.clinicid =dam.id WHERE pm.id in (',@MultiDoctorIds,') 
and dam.id=',@clinicId,'  order by CASE WHEN um.clinic_url=''',@ClinicUrl,''' THEN 1 ELSE 2  end,um.clinic_url   ');

select @query into temp_query;
select temp_query;