Mysql how to flatten a result set

Question

I have a stored procedure that will return a list of ids. I want to return that list as a comma separated string, i.e. "1,2,3,4,5".

I know I can do this using a cursor but is there an easier way to turn a resultset into a flattened string?

Answer 1

MySQL has the group_concat() aggregate function:

SELECT group_concat(some_column) FROM mytable;

Will return all some_column values from table joined by commas.

Caveat: Beware that the result is limited by the group_concat_max_len system variable, which defaults to only 1024 bytes! To avoid hitting this wall, you should execute this before running the query:

SET SESSION group_concat_max_len = 65536;

Or more, depending on how many results you expect. But this value cannot be larger than max_allowed_packet

Answer 2

select group_concat(id) from table

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat