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);
I follow a few rules:
- Primary keys should be as small as necessary. Prefer a numeric type because numeric types are stored in a much more compact format than character formats. This is because most primary keys will be foreign keys in another table as well as used in multiple indexes. The smaller your key, the smaller the index, the less pages in the cache you will use.
- Primary keys should never change. Updating a primary key should always be out of the question. This is because it is most likely to be used in multiple indexes and used as a foreign key. Updating a single primary key could cause of ripple effect of changes.
- Do NOT use "your problem primary key" as your logic model primary key. For example passport number, social security number, or employee contract number as these "natural keys" can change in real world situations. Make sure to add UNIQUE constraints for these where necessary to enforce consistency.
On surrogate vs natural key, I refer to the rules above. If the natural key is small and will never change it can be used as a primary key. If the natural key is large or likely to change I use surrogate keys. If there is no primary key I still make a surrogate key because experience shows you will always add tables to your schema and wish you'd put a primary key in place.
Best Answer
The error happens because MySQL can index only the first N chars of a BLOB or
TEXT
column. So The error mainly happens when there is a field/column type ofTEXT
or BLOB or those belong toTEXT
orBLOB
types such asTINYBLOB
,MEDIUMBLOB
,LONGBLOB
,TINYTEXT
,MEDIUMTEXT
, andLONGTEXT
that you try to make a primary key or index. With fullBLOB
orTEXT
without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when usingBLOB
orTEXT
types as an index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support a key length limit onTEXT
orBLOB
.TEXT(88)
simply won’t work.The error will also pop up when you try to convert a table column from
non-TEXT
andnon-BLOB
type such asVARCHAR
andENUM
intoTEXT
orBLOB
type, with the column already been defined as unique constraints or index. The Alter Table SQL command will fail.The solution to the problem is to remove the
TEXT
orBLOB
column from the index or unique constraint or set another field as primary key. If you can't do that, and wanting to place a limit on theTEXT
orBLOB
column, try to useVARCHAR
type and place a limit of length on it. By default,VARCHAR
is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.eVARCHAR(200)
will limit it to 200 characters long only.Sometimes, even though you don’t use
TEXT
orBLOB
related type in your table, the Error 1170 may also appear. It happens in a situation such as when you specifyVARCHAR
column as primary key, but wrongly set its length or characters size.VARCHAR
can only accepts up to 256 characters, so anything such asVARCHAR(512)
will force MySQL to auto-convert theVARCHAR(512)
to aSMALLTEXT
datatype, which subsequently fails with error 1170 on key length if the column is used as primary key or unique or non-unique index. To solve this problem, specify a figure less than 256 as the size forVARCHAR
field.Reference: MySQL Error 1170 (42000): BLOB/TEXT Column Used in Key Specification Without a Key Length