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

Question

CREATE TABLE dummy ( id INT UNDEFINED NOT ZERO AUTO_INCRIMENT PRIMARY KEY, name VARCHAR (30) NOT NULL) ENGINE = MYISAM;

and execute this query:

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

This query joins a column of names in all rows with || in one column. BUT, the result is truncated with mysql configuration, as described in the mysql manual :

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

The manual also says that this parameter can be changed at runtime with the following syntax:

 SET [GLOBAL | SESSION] group_concat_max_len = val; 

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

In addition, I think that changing the configuration will not solve my problem, because I do not know what to set to the value of group_concat_max_len, since the number of rows in the dummy table can be any number.

Thanks.

Answer 1

Have you tried using a stored procedure to accomplish your task? You can create a temporary table with one row / column and add to it when extracting rows from your table. As a result, just SELECT one value from the temporary table. You can find information about stored procedures in mysql manual and other places.