在MySQL中出现GROUP_CONCAT和Longtext问题 [英] Trouble with GROUP_CONCAT and Longtext in MySQL

问题描述

SQL ...

UPDATE Threads t 
SET t.Content = (
    SELECT GROUP_CONCAT(a.Content ORDER BY a.PageID SEPARATOR '<!-- pagebreak -->') 
    FROM MSarticlepages a
    WHERE a.ArticleID = t.MSthreadID GROUP BY a.ArticleID
)

如您所见，它占据了文章的所有页面(每个页面 以longtext格式存储在单独的行中)，然后GROUP_CONCAT将它们合并为一个 单个长文本行.问题是结果只有这么多 字符，然后它被完全截断，损失了大约90％ 内容. CONCAT处理长文本的效果不是很好还是在那里 我做错了什么吗?

As you can see it takes all of an article's pages (which are each stored as longtext in separate rows) and GROUP_CONCATs them into a single longtext row. The problem is the results are only so many characters and then it gets completely truncated, losing about 90% of the contents. Does CONCAT not handle longtext very well or is there something else I am doing wrong?

推荐答案

根据

According to the MySQL manual, the maximum length of GROUP_CONCAT is defined by the group_concat_max_len system variable, which defaults to 1024.

可以使用以下命令来增加该值:

This value can be increased, by using the following command:

SET group_concat_max_len = <int>

但是，应注意，group_concat_max_len的值本身受另一个系统变量max_allowed_packet的值限制，该变量默认为1,048,576.

It should be noted, however, that the value of group_concat_max_len is itself limited by the value of another system variable, max_allowed_packet, which defaults to 1,048,576.

使用相同的语法，该值最多可以增加到1,073,741,824:

This value can be increased to a maximum of 1,073,741,824, using the same syntax:

SET max_allowed_packet = <int>

这篇关于在MySQL中出现GROUP_CONCAT和Longtext问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！