Last week, when using the GROUP_CONCAT() function on a MySQL database, I got an unexpected result. :(
Indeed, instead of getting my result as VARCHAR types, I got it as BLOB types! For information, a BLOB is a binary large object that can hold a variable amount of data:
http://dev.mysql.com/doc/refman/5.0/en/blob.html
Because BLOB values are treated as binary strings, it is not easy to use. This is why we would prefer to have VARCHAR values.
So the question is how to get around this frustrating problem?
The answer is, for once, very simple! :D
You simply need to:
Open your my.ini or my.cnf file;
Change the value of the group_concat_max_len system 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 value of group_concat_max_len to 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.
Indeed, instead of getting my result as VARCHAR types, I got it as BLOB types! For information, a BLOB is a binary large object that can hold a variable amount of data:
http://dev.mysql.com/doc/refman/5.0/en/blob.html
Because BLOB values are treated as binary strings, it is not easy to use. This is why we would prefer to have VARCHAR values.
So the question is how to get around this frustrating problem?
The answer is, for once, very simple! :D
You simply need to:
Open your my.ini or my.cnf file;
Change the value of the group_concat_max_len system 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 value of group_concat_max_len to 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.
Comments
Post a Comment
Thanks for your valuable comments.