SQL存储过程-变量太短 [英] SQL Stored Procedure - variable too short

问题描述

我有一个mySQL存储过程，该存储过程只处理表中的少量数据:

I have a mySQL stored procedure that work with few data in a table :

SQL小提琴演示 ->感谢Mahmoud Gamal对此.

SQL Fiddle Demo -> thanks to Mahmoud Gamal for this.

问题在于此过程不适用于许多元数据:

The problem is that this precedure doesn't work with many metadata :

CREATE TABLE Metadata (`metadata_id` int, `metadata_name` varchar(25)) ;

INSERT INTO Metadata
    (`metadata_id`, `metadata_name`)
VALUES
    (1, 'duration'),
    (2, 'extension'),
    (3, 'size'),
    (4, 'duration2'),
    (5, 'extension2'),
    (6, 'size2'),
    (7, 'duration3'),
    (8, 'extension3'),
    (9, 'size3'),
    (10, 'duration4'),
    (11, 'extension4'),
    (12, 'size4'),
    (13, 'duration5'),
    (14, 'extension5'),
    (15, 'size5'),
    (16, 'duration6'),
    (17, 'extension6'),
    (18, 'size6')
;

似乎用@声明的会话变量太短了！

It seems that sessions variables declared with @ are too short !

此表的末尾将包含约150个元数据.

And this table will have around 150 metadata at the end.

SET @sql = NULL;
SET @cols = NULL;

SELECT
  GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(m.metadata_name = ''',
      m.metadata_name, ''', v.value, 0)) AS ', '''',   m.metadata_name, '''')
  ) INTO @cols
FROM Metadata AS m;

SET @sql = CONCAT('
  SELECT 
    v.file, ', @cols ,'
  FROM `values` AS v
  INNER JOIN metadata AS m ON v.metadata_id = m.metadata_id
  GROUP BY v.file');

prepare stmt 
FROM @sql;

execute stmt;

这两个变量@sql和@cols在此演示中的简单情况下都可以正常工作，但是在我的情况下，我有150个元数据，在这种情况下，我收到了变量太短的错误.

The two variables @sql and @cols work fine for simple scenarios like in this demo, but in my case I have 150 metadata, in this case I got an error that the variable is too short.

如何声明两个变量，以使它们使用来自连接150个元数据名称的太长文本?

预先感谢

推荐答案

是的，这可能是GROUP_CONCAT函数的问题，它有一个局限性.

Yes, this can be a problem of GROUP_CONCAT function, it has a limitation.

运行此查询-SELECT @@local.group_concat_max_len;它将返回GROUP_CONCAT()函数的最大允许结果长度.

Run this query - SELECT @@local.group_concat_max_len; It will return the maximum allowed result length for the GROUP_CONCAT() function.

您需要增加此值，例如-

You need to increase this value, for example -

SET @@local.group_concat_max_len=10000;

这篇关于SQL存储过程-变量太短的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！