Mysql截断GROUP_CONCAT函数的连接结果 [英] Mysql truncates concatenated result of a GROUP_CONCAT Function

问题描述

我创建了一个视图，该视图使用GROUP_CONCAT来连接名称为concat_products的列中数据类型为'varchar(7) utf8_general_ci'的产品列查询的结果. 问题是mysql截断了concat_products列的值. phpMyAdmin表示concat_products列的数据类型为varchar(341) utf8_bin

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

餐桌产品:

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视图:

concat_products_vw View:

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之间的值.

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.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.

1. 为什么mysql为varchar concat_products列指定的字符数超过255个?(已解决！)

1. Why mysql specifies more than 255 characters for varchar concat_products column?(solved!)

为什么用uf8_bin代替utf8_general_ci?

Why uf8_bin instead of utf8_general_ci?

是否可以将视图中的列的数据类型(例如在我的情况下)更改为concat_products列的文本?

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?

如果不能，如何防止mysql截断concat_products列?

If not what can i do to prevent mysql from truncating concat_products column?

推荐答案

正如我在前面的评论中所写，

As I already wrote in an earlier comment, the MySQL manual says:

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

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.

所以问题不在于字段的数据类型.

So the problem is not with the data type of the field.

MySQL手册还说:

结果被截断为由 group_concat_max_len系统变量，其默认值为 1024.虽然返回值的有效最大长度受的值限制，但可以将值设置得更高. max_allowed_pa​​cket.更改值的语法 运行时的group_concat_max_len如下，其中val是一个 无符号整数: SET [全球| SESSION] group_concat_max_len = val;

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的值的选项为:

Your options for changing the value of group_concat_max_len are:

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;

1. changing the value at MySQL startup by appending this to the command:
   --group_concat_max_len=your_value_here
2. adding this line in your MySQL configuration file (mysql.ini): group_concat_max_len=your_value_here
3. running this command after MySQL startup:
   SET GLOBAL group_concat_max_len=your_value_here;
4. running this command after opening a MySQL connection:
   SET SESSION group_concat_max_len=your_value_here;

文档: SET ， 查看全文