MySQL - A way to join all found rows in one column

Question

Hi, I have this table:

CREATE TABLE `dummy` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 30 ) NOT NULL
) ENGINE = MYISAM ;

and running this query:

SELECT GROUP_CONCAT(`name` SEPARATOR "||") FROM `dummy`

This query joins name column in all rows with || in a single column. BUT, the result is truncated with mysql configuration as explained in mysql manual :

"... result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024 ..."

Also in manual it is said that this setting can be changed in run time by the following syntax:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Does changing this configuration works in all mysql server environments? If not, how can I achieve the same result without GROUP_CONCAT without limits?

Also, I think that changing the configuration will not solve my problem because I don't know what to set the value of group_concat_max_len because number of rows in dummy table can be any number.

Thanks.

Answer 1

Have you tried using stored procedure to accomplish your task? You can create a temporary table with a single row/column and append to it while fetching rows from your table. In the end just SELECT the single value from the temporary table. You can find information about stored routines in mysql manual and other places.