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

Navigation
6

Trying to answer this issue (such as AP I tried to use 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:

Inquiry:

SELECTgroup_concat(t.id)FROMtestet;

Result:

Is there a problem in the MySQL Query Browser? In the query?

Note:

In the question at hand, I've seen examples that worked right here plus nothing. No Workbench worked fine, same query.

    
asked by anonymous 14.10.2014 / 12:17

1 answer

7

As per 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 return type of the aggregate function group_concat :

  • If variable group_concat_max_len is less than or equal to 512 variable size types ( VARCHAR or VARBINARY ) will be used. If it is greater than 512 lobs will be used ( TEXT or BLOB ).
  • The type of the source type: If it is a binary string a binary string will be generated ( VARBINARY , or BLOB ), if it is textual it will use non-binary types ( VARCHAR or TEXT ). / li>

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

    Then, the function returns:

    • a type BLOB for version 5.1, or
    • a type 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:

    • 14.10.2014 / 14:01
    How to make dynamic animations with CSS3? How to generate Mega-Sena results by ignoring some numbers?