mysql (mariadb)存储proc错误
提问于 2015-12-27 23:29:28
我有两个表-一个父表和一个带有元数据的表。我有一个存储的proc (我发誓它曾经工作过)，它将基于行的数据从元表转换为列，并使用左联接将父表与行合并成列数据。我现在得到一个错误1064。
更新:为了调试目的，我在proc中输出了查询，我发现最后一行到列的枢轴从不提供列名(从metakey值中提取)。不管最后一列是什么，它总是在n个字符之后被截断(在截断之前似乎不一致)！以下是查询的完整输出.
SELECT q.*,
MAX(IF(m.metakey = 'account_number', m.metavalue, NULL)) AS `account_number`,MAX(IF(m.metakey = 'area', m.metavalue, NULL)) AS `area`,MAX(IF(m.metakey = 'attachment_name', m.metavalue, NULL)) AS `attachment_name`,MAX(IF(m.metakey = 'attachment_token', m.metavalue, NULL)) AS `attachment_token`,MAX(IF(m.metakey = 'attachment_url', m.metavalue, NULL)) AS `attachment_url`,MAX(IF(m.metakey = 'description', m.metavalue, NULL)) AS `description`,MAX(IF(m.metakey = 'device_name', m.metavalue, NULL)) AS `device_name`,MAX(IF(m.metakey = 'email', m.metavalue, NULL)) AS `email`,MAX(IF(m.metakey = 'engine_files_attachment_name', m.metavalue, NULL)) AS `engine_files_attachment_name`,MAX(IF(m.metakey = 'engine_files_attachment_token', m.metavalue, NULL)) AS `engine_files_attachment_token`,MAX(IF(m.metakey = 'engine_files_attachment_url', m.metavalue, NULL)) AS `engine_files_attachment_url`,MAX(IF(m.metakey = 'fname_lname', m.metavalue, NULL)) AS `fname_lname`,
/** THIS IS WHERE THE ISSUE OCCURS **/
MAX(IF(m.metakey = 'how_connected_network', m.metavalue, NULL)) AS ,
m.json
FROM support_ticket_queue q LEFT JOIN support_ticket_queue_meta m ON q.id = m.ticket_queue_id GROUP BY q.id该程序：
DELIMITER $$
CREATE PROCEDURE `p_support_ticket_queue`()
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(
DISTINCT CONCAT(
'MAX(IF(m.metakey = ''',m.metakey,''', m.metavalue, NULL)) AS ','`',m.metakey,'`'
)
ORDER BY m.metakey
)
INTO @sql FROM
support_ticket_queue_meta m;
SET @sql = CONCAT ( 'SELECT q.*,' , @sql , ' , m.json FROM support_ticket_queue q LEFT JOIN support_ticket_queue_meta m ON q.id = m.ticket_queue_id GROUP BY q.id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
END$$
DELIMITER ;调用此proc将返回以下响应错误：
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM support_ticket_queue q LEFT JOIN support_ticket_queue_meta m ON q.id = m.ti' at line 1关于这两个表的概述情况如下：
support_ticket_queue (the parent table)
---------------------------------------
id (primary auto increment)
vertical (varchar)
created_on (datetime)
updated_on (datetime)
support_ticket_queue_meta
---------------------------------------
id (primary auto incremenet)
ticket_queue_id (foreign key to id from parent table)
metakey (varchar)
metavalue (text)
回答 1
Stack Overflow用户
发布于 2015-12-28 09:43:09
做了一些调试测试，并注意到我的GROUP_CONCAT模式在1024个字符后被截断。在网上做了一些进一步的搜索，发现GROUP_CONCAT实际上有1024的长度限制。
通过将group_concat_max_len值提高到更高的极限，解决了我的问题。
SET SESSION group_concat_max_len = 2500;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/34489371
复制相关文章
腾讯云开发者
