Description:
Hi!
I am doing some data migration work at moment and am finding the GROUP_CONCAT function rather limited, it only allows for max of 1024 characters when I need 16K.
Please review this limitation.
How to repeat:
-- 1. Create DB called `test`
-- 2. Execute SP below
-- 3. Run SP below (takes just over 2 minutes)
-- 4. Click the disk icon in the results and save to \Desktop\results.txt
-- 5. Load results.txt into Notepad++ and move cursor to EOL
-- RESULT Cursor Column in Notepad++ reads no more than 'col : 1025'
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`MyProc` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `MyProc`()
BEGIN
DECLARE c INT;
-- vars
SET c = 0;
-- create talbe
DROP TABLE IF EXISTS `new table`;
CREATE TABLE `new table` (
`id` int(10) unsigned NOT NULL auto_increment,
`description` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
-- truncate sale-business
truncate table classic.`sale-business`;
-- insert
WHILE c < 3100 DO
-- INSERT INTO `new table`(description) VALUES(CAST(1 AS CHAR)); #too slow
INSERT INTO `new table`(description) VALUES(c);
SET c = c + 1;
END WHILE;
-- group_concat error
SELECT GROUP_CONCAT(id) FROM `new table`;
END $$
DELIMITER ;
Suggested fix:
May I suggest max length of (16384) or (32768)
