Mysql 截断 GROUP_CONCAT 函数的连接结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12001919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql truncates concatenated result of a GROUP_CONCAT Function
提问by pouya
I've created a view which uses GROUP_CONCAT to concatenate results from a query on products column with data type of 'varchar(7) utf8_general_ci'in a column named concat_products.
The problem is that mysql truncates value of concat_products column.
phpMyAdmin says the data type of concat_products column is varchar(341) utf8_bin
我创建了一个视图，该视图使用 GROUP_CONCAT 将来自产品列的查询的结果连接到'varchar(7) utf8_general_ci'名为concat_products. 问题是 mysql 截断了 concat_products 列的值。phpMyAdmin 说 concat_products 列的数据类型是varchar(341) utf8_bin
table 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 View:
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`
according to mysql manual
根据mysql手册
Values in VARCHAR columns are variable-length strings
Length can be specified as a value from 1 to 255 before MySQL 4.0.2 and 0 to 255 as of MySQL 4.0.2.
VARCHAR 列中的值是可变长度字符串
长度可以指定为 MySQL 4.0.2 之前的 1 到 255 和 MySQL 4.0.2 之前的 0 到 255 的值。
edit:
编辑：
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
Why mysql specifies more than 255 characters for varchar concat_products column?(solved!)
Why uf8_bin instead of utf8_general_ci?
Is it possible to change the data type of a column in a view for example in my case to text for concat_products column?
If not what can i do to prevent mysql from truncating concat_products column?
为什么mysql为varchar concat_products列指定了超过255个字符？（已解决！）
为什么是 uf8_bin 而不是 utf8_general_ci？
是否可以将视图中列的数据类型（例如在我的情况下）更改为 concat_products 列的文本？
如果不是，我该怎么做才能防止 mysql 截断 concat_products 列？
回答by Jocelyn
As I already wrote in an earlier comment, the MySQL manualsays:
正如我在之前的评论中已经写过的，MySQL 手册说：
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
VARCHAR 列中的值是可变长度的字符串。长度可以指定为 0 到 65,535 之间的值。
So the problem is not with the data type of the field.
所以问题不在于字段的数据类型。
The MySQL manualalso says:
在MySQL手册也说：
The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer: SET [GLOBAL | SESSION] group_concat_max_len = val;
结果被截断为 group_concat_max_len 系统变量给出的最大长度，其默认值为 1024。该值可以设置得更高，尽管返回值的有效最大长度受 max_allowed_packet 值的限制。在运行时更改 group_concat_max_len 值的语法如下，其中 val 是一个无符号整数：SET [GLOBAL | SESSION] group_concat_max_len = val;
Your options for changing the value of group_concat_max_len are:
更改 group_concat_max_len 值的选项是：
- changing the value at MySQL startup by appending this to the command:
--group_concat_max_len=your_value_here - adding this line in your MySQL configuration file (mysql.ini):
group_concat_max_len=your_value_here - running this command after MySQL startup:
SET GLOBAL group_concat_max_len=your_value_here; - running this command after opening a MySQL connection:
SET SESSION group_concat_max_len=your_value_here;
- 通过将其附加到命令来更改 MySQL 启动时的值：
--group_concat_max_len=your_value_here - 在您的 MySQL 配置文件 (mysql.ini) 中添加这一行：
group_concat_max_len=your_value_here - 在 MySQL 启动后运行此命令：
SET GLOBAL group_concat_max_len=your_value_here; - 打开 MySQL 连接后运行此命令：
SET SESSION group_concat_max_len=your_value_here;
Documentation: SET, Server System Variables: group_concat_max_len
回答by JoshS
As Jocelyn mentioned, the size of a GROUP_CONCAT()result is bounded by group_concat_max_len, however there is an additional interaction with ORDER BYthat results in a further truncation to 1/3 of group_concat_max_len. For an example, see this related answer.
正如 Jocelyn 所提到的，GROUP_CONCAT()结果的大小受限制group_concat_max_len，但是还有一个额外的交互作用ORDER BY会导致进一步截断到 的 1/3 group_concat_max_len。有关示例，请参阅此相关答案。
The default value for group_concat_max_lenis 1024, and 1024 / 3 = 341 probably explains why the type of concat_products shows up as varchar(341)in the original example. If you were to remove the GROUP BY productIdclause, concat_products should show up as varchar(1024).
的默认值为group_concat_max_len1024，而 1024 / 3 = 341 可能解释了为什么 concat_products 的类型显示为varchar(341)原始示例中的原因。如果您要删除该GROUP BY productId子句，则 concat_products 应显示为varchar(1024).
I have not found this interaction between GROUP_CONCAT()and ORDER BYmentioned in the MySQL Manual, but it affects at least MySQL Server 5.1.
我在MySQL 手册中没有发现GROUP_CONCAT()和ORDER BY提到的这种交互，但它至少影响 MySQL Server 5.1。
