MySQL GROUP_CONCAT returns [BLOB – 14 B]
A tech post today, I really don’t do enough of these. I bumped into an issue recently where I was building a form guide for the cricket league website that I run. The idea was to present the team form on one line based off results in my scorecard table, like so: WWWLLWWNWWLWW
I previously had a nice little PHP script which handled Wins versus Losses quite well but I needed to add Tie, Draw and No Result to the mix. I had a bit of a play with PHP but decided to have a crack in MySQL instead.
SELECT SeasonName, TeamID, TeamName, GROUP_CONCAT(Result SEPARATOR '') AS Form FROM ...
Which works great but the ordering wasn’t as I wanted, for a proper form guide it needs to be in order of games played. Hence I added the ORDER BY:
SELECT SeasonName, TeamID, TeamName, GROUP_CONCAT(Result ORDER BY game_date SEPARATOR '') AS Form FROM …
The problem is that in my environment the value being returned for the Form field was [BLOB – 14 B]. It turns out the result value depends on the system variable group_concat_max_len. The default values for this is 1024 (1kb) and mine was obviously coming out larger. I verified my group_concat_max_len was set to 1024 with the following query:
-- Check length SHOW VARIABLES LIKE 'group%';
If you want to change the the length of that system variable use can use the below query:
-- Update length SET @@group_concat_max_len =524288; -- Check length SHOW VARIABLES LIKE 'group%';
If you have access to my.ini or my.cnf you can also do the following:
- 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
I also found a solution that works, and is working well for me. I converted the GROUP CONCAT to UTF8 with the following SQL:
SELECT SeasonName, TeamID, TeamName, CONVERT( GROUP_CONCAT(Result ORDER BY game_date SEPARATOR '') USING utf8 ) AS Form FROM ...
Hopefully helps someone else having the same issue.
Keep up your tech posts, enjoy the read 🙂
Haha, cheers Troy! This is probably all piddly stuff to you though!
Its usfully page, but can you explain why required CONVERT in UTF 8.
My understanding is because my BLOB is stored as UTF 8.
Thank’s For this Solution…
I have tryed google but fail….
Thanks for the CONVERT tip, helped me out!
Very glad folks!
Hey man, NICE!
this works if you dont receive special chars, but what happens if i use “ñ, à,á”
if you convert to utf8 the words get cut when they find those chars, what else can we use????
ok got it!!!! (latin1)
SPANISH : USING latin1
Nice Guillermo, thanks for your input!