MySQL varchar column on group_concat shows as text in View

Question

I have column in base table as acct_num varchar(25) and I am creating a View and GROUP_CONCAT() is set to this column. This is shown as text datatype in show create view. Is it possible to have VARCHAR(25) datatype for GROUP_CONCAT(acct_num) column. Please advise.

What I learnt from MySQL reference:

• I though to cast as varchar(25) but CAST can be applied as CHAR not as VARCHAR
• There is option to set GLOBAL_SET_GROUP_CONCAT_VALUE = 512 so that you can get output of GROUP_CONCAT() as varchar() - but it didn't work out for me.

Answer 1

You can set group_concat_max_len to achieve that.

Here is a demo:

SQL:

-- To change the setting globally
set global group_concat_max_len = 512;
-- To change the setting only for current session
set group_concat_max_len = 512;
create table t1(acct_num varchar(25));
create view v1 as select group_concat(acct_num) as gc_acct_num from t1;
desc v1;

Output:

mysql> -- To change the setting globally
mysql> set global group_concat_max_len = 512;
Query OK, 0 rows affected (0.00 sec)

mysql> -- To change the setting only for current session
mysql> set group_concat_max_len = 512;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(acct_num varchar(25));
Query OK, 0 rows affected (0.01 sec)

mysql> create view v1 as select group_concat(acct_num) as gc_acct_num from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> desc v1;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| gc_acct_num | varchar(512) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)