Why does the GROUP_CONCAT function in MySQL return the BLOB large object type? (Why GROUP_CONCAT returns BLOB ?), Group_concatblob

Why does the GROUP_CONCAT function in MySQL return the BLOB large object type? (Why GROUP_CONCAT returns BLOB ?), Group_concatblob

Why does the GROUP_CONCAT function in MySQL return the BLOB large object type? (Why GROUP_CONCAT returns BLOB ?)

Beautiful Life of the sun and fire god (http://blog.csdn.net/opengl_es)

This article follows the "signature-non-commercial use-consistency" creation public agreement

Reprinted please keep this sentence: Sun huoshen's beautiful life-this blog focuses on Agile development and mobile and IOT device research: iOS, Android, Html5, Arduino, pcDuino, otherwise, this blog post is rejected or reprinted. Thank you for your cooperation.

Similar articles are listed below,

My problem is CONCAT (field name, ''). This is used to convert the Value Field represented by the field name into a string.

From the reply on the third floor below, we can find: CAST (user_id as char)

Use the CAST function to convert the value of a field to the struct type.

The cause of the problem is that the function compute result of CONCAT is returned in bytes, which leads to a large object mistaken for BLOB binary.

Therefore, when converting to a string, use this function with caution and use CAST for conversion.

When using tools such as WorkBench, there will be a setting option that can treat binary or mutable binary return results as non-binary strings, so that such tools execute similar SQL statements, the returned function compute field value will not be treated as BLOB, but will directly display its string value.

Why GROUP_CONCAT returns BLOB?

Last week, when usingGROUP_CONCAT()Function on a MySQL database, I got an unexpected result.

Indeed, instead of getting my resultVARCHARTypes, I got itBLOBTypes! For information,BLOBIs a binary large object that can hold a variable amount of data:
Http://dev.mysql.com/doc/refman/5.0/en/blob.html
BecauseBLOBValues are treated as binary strings, it is not easy to use. This is why we wowould prefer to haveVARCHARValues.

So the question is how to get around this frustrating problem?

The answer is, for once, very simple!
You simply need:

• Open your my. ini or my. cnf file;
• Change the value ofgroup_concat_max_lenSystem variable to 512 (no 'K' suffix );
• Restart the mysql service

To verify if the value has been successfully updated, execute the following command in your mysql client:

mysql> show variables like "%concat%";

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| group_concat_max_len | 512   |

+----------------------+-------+

1 row in set (0.00 sec)

Note that you cannot set the valuegroup_concat_max_lenTo less than 1Kb using the MySQL Administrator GUI. which means that the only way to set this system variable to 512 (which is less than 1Kb) is to edit your MySQL configuration file as described above.

Blob, group_concat (), group_concat_max_len, MySQL, varchar

This entry was posted on 11 Feb 2010, and is filed under MySQL, Tricks. you can follow any responses to this entry through RSS 2.0. you can leave a response, or trackback from your own site.

• COMMENTS (5)
• RELATED POSTS

1. #1Gerald MengisenOn 15 Sep 2010-20:34

   The solution above doesn' t quite work for shared hosting. however, the solution in the link below worked for me (I'm just adding this here because your entry was one of the first ones in Google about this problem ):
   Http://stackoverflow.com/questions/2133936/using-group-concat-in-phpmyadmin-will-show-the-result-as-blob-3b

2. #2SmoreauOn 15 Sep 2010-

   Thank you very much for sharing this link.
   Indeed, it wouldn't be possible to change the value of a system variable on a shared hosting.
   It is actually a nice trick to convert the column value to a string!

3. #3MartOn 13 Apr 2011-11: 25

   Had the same problem, found answer
   SELECT rec_id, GROUP_CONCAT (CAST (user_id as char ))
   FROM t1
   Group by rec_id

   @ Http://stackoverflow.com/questions/2133936/using-group-concat-in-phpmyadmin-will-show-the-result-as-blob-3b

4. #4RakonDarkOn 13 Jul 2012-17: 46

   Dear mart
   I found a problem with your CAST on MySQL

   When I have INT and I Gdo this
   SELECT rec_id, GROUP_CONCAT (CAST (user_id as char ))
   FROM t1
   Group by rec_id

   Then I have always only 1 char space for the int value, this may work for 0-9 but not when your INT grow

   For me it was fine to CAST as CHAR (7) to hold
   Biggest value 9999999 value from my INT :)

   SELECT rec_id, GROUP_CONCAT (CAST (user_id as char (7 )))
   FROM t1
   Group by rec_id

5.