group_concat 上的 MySQL varchar 列在 View 中显示为文本

Question

我在基表中有列作为 acct_num varchar(25)，我正在创建一个 View 并将 GROUP_CONCAT() 设置到该列。这在 show create View 中显示为 text 数据类型。 GROUP_CONCAT(acct_num) 列是否可以有 VARCHAR(25) 数据类型。请指教。

我从 MySQL 引用资料中学到的东西:

• 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

您可以设置 group_concat_max_len 来实现这一点。

这是一个演示:

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;

输出:

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)