MySQL 截断 GROUP_CONCAT 函数的连接结果

2021-12-25 00:00:00 types mysql truncate view

我创建了一个视图，该视图使用 GROUP_CONCAT 将产品列查询的结果与数据类型 'varchar(7) utf8_general_ci' 连接到名为的列中concat_products.

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.

问题是 MySQL 截断了concat_products"的值；柱子.phpMyAdmin 表示concat_products"的数据类型；列是 varchar(341) utf8_bin

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

The "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 手册:

According to MySQL manual:

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.

编辑

VARCHAR 列中的值是可变长度的字符串.长度可以指定为 0 到 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?

推荐答案

正如我在之前的评论中所写，MySQL 手册 说:

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 是一个无符号整数:设置 [全球 |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;

文档:SET、服务器系统变量:group_concat_max_len

相关文章