Why does the group_concat of primary keys (integers) generate a BLOB as a result?

Question

Trying to answer this issue (as the AP even presented, without resorting to normalization) I tried to use the group_concat it returns me the object of type BLOB.

Table:

CREATE TABLE `teste` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nome` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Data in the Table:

Consultation:

SELECT group_concat(t.id) FROM teste t;

Upshot:

Is there a problem in Mysql Query Browser? In query?

Note:

In the matter at hand, I saw examples that worked right over here nothing else. In the Workbench worked well, the same query.

Answer 1

According to the sql manual: group_concat

The Return value is a nonbinary or Binary string, Depending on whether the Arguments are nonbinary or Binary strings. The result type is TEXT or BLOB unless group_concat_max_len is Less than or Equal to 512, in which case the result type is VARCHAR or VARBINARY.

In summary there are two factors that determine the type of return of the aggregate function group_concat:

1. If the variable group_concat_max_len is less than or equal to 512 variable size types will be used (VARCHAR orVARBINARY). If larger than 512 lobs will be used (TEXT or BLOB).
2. The source type format: If it is a binary string, a binary string will be generated (VARBINARY,or BLOB), if it is textual non-binary types will be used (VARCHAR or TEXT).

In your specific case your variable group_concat_max_len is set to a value greater than 512 and the id is the type int. In summer 5.1 this number is interpreted (converted implicitly) as a binary string, whereas in recent versions it is interpreted as a non-binary string.

So the function returns:

• a guy BLOB for version 5.1, or
• a guy TEXT for modern versions (unless you convert the type to a binary string SELECT group_concat(BINARY t.id) AS coluna FROM teste t;)

---

Examples:

• Mysql 5.1.61 (LONGBLOB)
• Mysql 5.5.32 (TEXT)
• Mysql 5.6.6 M9 - Binary (BLOB)