using group_concat in PHPMYADMIN will show the result as [BLOB - 3B]

Question

I have a query which uses the GROUP_CONCAT of mysql on an integer field.
I am using PHPMYADMIN to develop this query. My problem that instead of showing 1,2 which is the result of the concatenated field, I get [BLOB - 3B].

Query is

SELECT rec_id,GROUP_CONCAT(user_id)
FROM t1
GROUP BY rec_id

(both fields are unsigned int, both are not unique)

What should I add to see the actual results?

score 88 · Answer 1 · answered Apr 26 '10 at 19:40

Looks as though GROUP_CONCAT expects that value to be a string. I just ran into the same problem. Solved it by converting the int column to a string like so:

SELECT rec_id,GROUP_CONCAT(CONVERT(user_id, CHAR(8)))
FROM t1
GROUP BY rec_id

Figured I'd share in case you were still having an issue with this.

score 44 · Answer 2 · answered Nov 05 '10 at 11:23

According to the MySQL documentation, CAST(expr AS type) is standard SQL and should thus be perferred. Also, you may omit the string length. Therefore, I’d suggest the following:

SELECT rec_id, GROUP_CONCAT(CAST(user_id AS CHAR))
FROM t1
GROUP BY rec_id

score 22 · Answer 3 · answered Jan 11 '13 at 09:03

22

For me, this helped (found it in this blog post):

In my case the parameter to GROUP_CONCAT was string but the function still resulted in a BLOB, but converting the result of the GROUP_CONCAT worked.

CONVERT(GROUP_CONCAT(user_id) USING 'utf8')

answered Jan 11 '13 at 09:03

Janne

3,447
5
25
34

2

Thanks Janne. Great solution. – Jonas T Jul 16 '14 at 05:18
That helps me :) Thanks Janne – senior Nov 11 '14 at 08:03
I want to hug you and kick the guy who designed the database I am using for using 'latin1' encoding. Thanks. – biniam Oct 08 '15 at 16:27

score 20 · Accepted Answer · answered Jun 16 '11 at 15:46

20

Just above the query result (to the left) you will see +options. Press it and mark

Show BLOB contents

answered Jun 16 '11 at 15:46

Itay Moav -Malimovka

48,785
58
182
262

Just to help out users who can't see the `+options`, not all configurations of phpmyadmin have it – Ejaz Jul 10 '15 at 17:09

score 0 · Answer 5 · edited Feb 11 '16 at 11:01

0

You can do this:

set session group_concat_max_len = 512;

If group_concat_max_len is more than 512 the query will return byte[]. But you can pass to a string.

System.Text.Encoding.Default.GetString((byte[])DataTable.Rows[0][0]);

edited Feb 11 '16 at 11:01

Misa Lazovic

2,765
9
30
38

answered Feb 11 '16 at 10:58

Joe

1
2

score 0 · Answer 6 · answered Dec 09 '19 at 05:18

In the latest Phpmyadmin

After running query, you will see some results and then dot ..

so just click on options (which is on top of the query result)

Then Just select

Full texts

radio button, default is Partial texts

Then press Go button and you will see full result

score -1 · Answer 7 · answered Jun 23 '11 at 23:28

If you have access to the config.inc.php file in the phpMyAdmin directory, then I think the best solution is to change this line:

$cfg['Servers'][$i]['extension'] = 'mysql';

to this:

$cfg['Servers'][$i]['extension'] = 'mysqli';

If you have the mysqli extension available, use it. It is more secure, a bit more optimized, and it handles the BLOB type of utf-8 better by default. Your [BLOB] entries should start showing up as their values without having to add in any other special configuration options.

using group_concat in PHPMYADMIN will show the result as [BLOB - 3B]

7 Answers7

Linked

Related