You can use GROUP_CONCAT
:
SELECT person_id,
GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Ludwig stated in his comment, you can add the DISTINCT
operator to avoid duplicates:
SELECT person_id,
GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY
:
SELECT person_id,
GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
SET group_concat_max_len = 2048;
Of course, you can change 2048
according to your needs. To calculate and assign the value:
SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id) AS UNSIGNED);
You make a good point that you can avoid some number of joined queries by using what's called a natural key instead of a surrogate key. Only you can assess if the benefit of this is significant in your application.
That is, you can measure the queries in your application that are the most important to be speedy, because they work with large volumes of data or they are executed very frequently. If these queries benefit from eliminating a join, and do not suffer by using a varchar primary key, then do it.
Don't use either strategy for all tables in your database. It's likely that in some cases, a natural key is better, but in other cases a surrogate key is better.
Other folks make a good point that it's rare in practice for a natural key to never change or have duplicates, so surrogate keys are usually worthwhile.
Best Answer
Create a table with one int column of type InnoDB:
Then when you want to get a new value for the key you must start a transaction (using the API you are using) and then:
Then read the value returned, it is the value for your primary key. Then to increment the value while still in the same transaction execute:
Then commit your transaction.
This will ensure complete consistency if and only if you use transaction as described above and you use the InnoDB table type.