Search code examples
mysqlgroup-concat

MySql group_concat behaviour when used with insert and a limit

I'm a little confused here.

I have table_a and table_b, and run the SQL below.

table_a

"id"    "addedUser" "addedName"
"1"     "1"         "james"
"2"     "1"         "kirk"
"3"     "1"         "lars"
"4"     "2"         "michael"

table_b

"id"    "userName"  "userDisplay"   "userFreinds"
"1"     "norman"    "james"         "james,kirk,lars"

insert into table_b (
userName,
userDisplay,
userFreinds
) select 'norman', addedName, group_concat(addedName) from table_a where addedUser = 1 limit 1;

My idea, is to get the top most record from addedName in table_a into table_b, along with a list of comma separated values of all the addedName values from table_a for a particular user.

I use a limit 1 in the statement, but mysql seems to give me a string of all the addedName values for userFriends for a user I specify (which is good and exactly what I need).

But, is this the right/optimum way to do this? How is MySql returning only a single value for userDisplay, but all values for userFriends? Is group_concat running a seperate select in there? Will I run into performance issues if I keep it this way?

Strangely, the results are the same, even without the LIMIT. Should I be keeping the limit in there?

Is there a better way to do this? The results are exactly what I need. What am I doing right or wrong here?

Solution

  • This is what happens when you run 

    select 'norman', addedName, group_concat(addedName) from table_a where addedUser=1 limit 1

    First, all rows where addedUser = 1 are found (3 rows from your table_a).

    Then, since you are using an aggregate function (group_concat), but without a GROUP BY clause, all of the 3 rows are considered to be one single group.

    So finally, you'll get 1 grouped row, with the values james,kirk,lars etc. You don't need to specify LIMIT 1 because you will always get one row only as a result.

    MySQL allows combining aggregate functions like COUNT() or GROUP_CONCAT() with non-aggregate columns (like addedName in your query), so it is perfectly fine to use it as you did, without the limit or with it.