MySQL截断了GROUP_CONCAT函数的连接结果

Question

我创建了一个视图，使用GROUP_CONCAT将查询结果连接在一起，这些结果来自于数据类型为'varchar(7) utf8_general_ci'的products列，最终呈现在名为concat_products的列中。

问题是MySQL截断了concat_products列的值。phpMyAdmin显示concat_products列的数据类型为varchar(341) utf8_bin。

表格products:

CREATE TABLE `products`(
    `productId` tinyint(2) unsigned NOT NULL AUTO_INCREMENT, 
    `product` varchar(7) COLLATE utf8_general_ci NOT NULL, 
    `price` mediumint(5) unsigned NOT NULL, 
    PRIMARY KEY (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci

“concat_products_vw”视图：

CREATE VIEW concat_products_vw AS
SELECT
  `userId`,
    GROUP_CONCAT(CONCAT_WS('_', `product`, `productId`, `price`) 
        ORDER BY `productId` ASC SEPARATOR '*') AS concat_products
FROM
  `users`
LEFT JOIN `products` 
ON `users`.`accountBalance` >= `product`.`price`
GROUP BY `productId` 

根据MySQL手册：

VARCHAR列中的值是可变长度字符串
长度可以在MySQL 4.0.2之前指定为1到255的值，MySQL 4.0.2及以后版本为0到255。

---

编辑

VARCHAR列中的值是可变长度字符串。长度可以指定为0到65,535。

1. 为什么MySQL针对“concat_products”列指定超过255个字符的varchar？（已解决!）

2. 为什么使用utf8_bin而不是utf8_general_ci？

3. 是否可以更改视图中列的数据类型，例如在我的情况下更改“concat_products”列的数据类型为文本？

4. 如果不能，我该如何防止MySQL截断“concat_products”列？

- pouya

---

你正在使用哪个版本的MySQL？ - gbn

请仔细检查您的资料来源。MySQL手册指出：VARCHAR列中的值是可变长度字符串。长度可以指定为0到65,535之间的值。 - Jocelyn

@Jocelyn，你说得对，那么为什么在MySQL可以在varchar列中存储多达65535个字符的情况下会发生截断呢？ - pouya

Answer 1

正如我在早前的评论中所写的，MySQL手册表明：

VARCHAR列中的值是可变长度字符串。长度可以指定为0到65,535之间的值。

因此问题并不在于字段的数据类型。

MySQL手册还指出：

结果被截断为由group_concat_max_len系统变量给出的最大长度，其默认值为1024。这个值可以设置得更高，但返回值的有效最大长度受max_allowed_packet值的限制。在运行时更改group_concat_max_len值的语法如下，其中val是一个无符号整数： SET [GLOBAL | SESSION] group_concat_max_len = val;

更改group_concat_max_len值的选项包括：

1. 通过在命令中添加以下内容来更改MySQL启动时的值：
   --group_concat_max_len=your_value_here
2. 在MySQL配置文件（mysql.ini）中添加以下行：group_concat_max_len=your_value_here
3. 在MySQL启动后运行此命令：
   SET GLOBAL group_concat_max_len=your_value_here;
4. 在打开MySQL连接后运行此命令：
   SET SESSION group_concat_max_len=your_value_here;

文档：SET语句，服务器系统变量：group_concat_max_len

- Jocelyn

---

感谢MySQL不再截断concat_products列。 - pouya

我个人使用我列出的第四个选项，因为只有少数脚本需要计算“大型”GROUP_CONCAT，并且这些脚本不经常运行。 - Jocelyn

Answer 2

正如Jocelyn所提到的，GROUP_CONCAT()结果的大小受group_concat_max_len限制，但在与ORDER BY交互时会进一步截断为group_concat_max_len的1/3。例如，请参见此相关答案。 group_concat_max_len的默认值为1024，且1024 / 3 = 341可能解释了为什么在原始示例中concat_products的类型显示为varchar(341)。 如果您删除GROUP BY productId子句，则concat_products应显示为varchar(1024)。

我没有在MySQL手册中找到有关GROUP_CONCAT()和ORDER BY之间的这种交互的提及，但它至少会影响MySQL Server 5.1。

- JoshS

Answer 3

2023年，在Linux服务器上，您可以编辑my.cnf文件，然后设置：

[mysqld]
group_concat_max_len = 2048

- Ale DC