PostgreSQL's array_agg() and array_to_string in MySQL

Question

I wonder if there's a counterpart of array_agg() and array_to_string of PostgreSQL in MySQL (Or anything close to this function at least) I haven't had any luck on my research trying to find something similar to this two. For non-PostgreSQL users, here's how it works.
Normally, executing:

SELECT my_column FROM my_table;

Will result to:

--------------
| my_column  |
|------------|
|   DATA1    |
|   DATA2    |
|   DATA3    |
|   DATA4    |
--------------

On the other hand, executing the statement:

SELECT array_agg(my_column) FROM my_table;

Will result to:
A single row with all the data from my_column.

-----------------------------------
|       array_agg(my_column)      |
|---------------------------------|
|{"DATA1","DATA2","DATA3","DATA4"}|
-----------------------------------

And array_to_string, as the function name says, it converts the array into a single String.
Converting the array_agg(my_column) will return the following:

-------------------------
|                       |
|-----------------------|
|DATA1,DATA2,DATA3,DATA4|
-------------------------

Depending on your separator. In my case, it's comma.

*******************Edit(SOLUTION)*******************

Solution (Written by IgorRomanchenko):

SELECT GROUP_CONCAT(my_column SEPARATOR ',');

And as for the array_to_string question – MySQL does not have an “Array” data type built-in, so asking for a similar function is rather pointless. If you just want to concatenate a number of singular values with a separator in between, look at CONCAT_WS. And in general, look at the function overview to get an idea of what functions MySQL provides and what purpose you can use them for and on which data types: dev.mysql.com/doc/refman/5.5/en/func-op-summary-ref.html — C3roe
– C3roe, Commented Aug 12, 2013 at 10:45
just notice, in PostgreSQL 9.0 and higher you can use string_agg function instead sequence string_to_array(array_agg(..),',') - it is a little bit faster. — Pavel Stehule
– Pavel Stehule, Commented Aug 12, 2013 at 11:13
@Pavel Stehule Thanks for your input! I'll consider optimizing my procedures. — Michael 'Maik' Ardan
– Michael 'Maik' Ardan, Commented Aug 13, 2013 at 1:36
@CBroe Thanks for sharing your knowledge on MySQL. I'll take a look on your suggested function. — Michael 'Maik' Ardan
– Michael 'Maik' Ardan, Commented Aug 13, 2013 at 1:37

Ihor Romanchenko · Accepted Answer · 2013-08-13 08:11:26Z

7

MySQL does not have bult in array type, but you can use string aggregation (like GROUP_CONCAT) to aggregate multiple values to a single string.

answered Aug 13, 2013 at 8:11

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Danon Over a year ago

If grouped string contains the separator, it can cause problem deserializing it.

Collectives™ on Stack Overflow

PostgreSQL's array_agg() and array_to_string in MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related