Using GROUP_CONCAT() on bit fields returns garbage in MySQL? How to fix?

To fix, use group_concat() with addition of 0 with column. Let us first create a table −

mysql> create table DemoTable1856      (      Id int,      Value bit(1)      ); Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1856 values(101,1); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1856 values(102,0); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1856 values(101,0); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1856 values(102,1); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1856 values(101,0); Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1856;

This will produce the following output −

+------+-------+ | Id   | Value | +------+-------+ |  101 |       | |  102 |       | |  101 |       | |  102 |       | |  101 |       | +------+-------+ 5 rows in set (0.00 sec)

Here is the query to use group_concat() on bit fields and avoid returning garbage value −

mysql> select group_concat(Value+0) from DemoTable1856      group by Id;

This will produce the following output −

+-----------------------+ | group_concat(Value+0) | +-----------------------+ | 1,0,0                 | | 0,1                   | +-----------------------+ 2 rows in set (0.00 sec)