MariaDB – group_concat_max_len

測試環境為 CentOS 8 x86_64 (虛擬機)

group_concat 是有長度的限制,可以透過以下指令查看目前支援的長度為何.

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE 'group_concat_max_len';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| group_concat_max_len | 1048576 |
+----------------------+---------+
1 row in set (0.002 sec)

可以透過 SQL Command Line 指令立刻更改,但 MariDB / MySQL 服務重新啟動後就回歸原狀.

MariaDB [(none)]> SET GLOBAL group_concat_max_len = 102400;
MariaDB [(none)]> SET SESSION group_concat_max_len = 102400;

MariaDB [(none)]> SHOW VARIABLES LIKE 'group_concat_max_len';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| group_concat_max_len | 102400 |
+----------------------+--------+
1 row in set (0.001 sec)

要永久生效須改設定檔,在 [mysqld] 區塊新增.可以使用 group_concat_max_len=-1 代表使用多大的位元組設定.

[root@localhost ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
group_concat_max_len=-1

並重新啟動服務.

[root@localhost ~]# systemctl restart mariadb

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

上限是 4294967295 (2^32 – 1) 這應該是 LONGTEXT 的字元上限.

MariaDB [(none)]> SHOW VARIABLES LIKE 'group_concat_max_len';
+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| group_concat_max_len | 4294967295 |
+----------------------+------------+
1 row in set (0.001 sec)