当前位置：文江博客话题详情

MySQL truncated

结果被截断，MYSQL bug？

发布于 2024-07-24 16:28:34 字数 11777 浏览 9 评论 0原文

下面的查询被截断，“created”仅显示“cre”（首先滚动到最右边:)：

 mysql> SELECT  GROUP_CONCAT(CONCAT('<comment><body><![CDATA[',body,']]></body>','<replier>',if(uid is not null,uid,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '')
        ->                                          FROM   idiscussion
        ->
        ->                                          WHERE  idiscussion.iask_id = 1;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GROUP_CONCAT(CONCAT('<comment><body><![CDATA[',body,']]></body>','<replier>',if(uid is not null,uid,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | <comment><body><![CDATA[haha]]></body><replier></replier><created>2009-05-27 04:57:51</created></comment><comment><body><![CDATA[haha]]></body><replier></replier><created>2009-05-27 04:57:52</created></comment><comment><body><![CDATA[haha]]></body><replier></replier><created>2009-05-27 04:57:52</created></comment><comment><body><![CDATA[haha]]></body><replier></replier><created>2009-05-27 04:57:53</created></comment><comment><body><![CDATA[r]]></body><replier></replier><created>2009-05-29 21:55:44</created></comment><comment><body><![CDATA[s]]></body><replier></replier><created>2009-05-29 23:04:39</created></comment><comment><body><![CDATA[dddret]]></body><replier></replier><created>2009-05-29 23:15:41</created></comment><comment><body><![CDATA[sser]]></body><replier></replier><created>2009-05-29 23:17:13</created></comment><comment><body><![CDATA[aaaaaaassaaaaaa]]></body><replier></replier><created>2009-05-29 23:23:32</created></comment><comment><body><![CDATA[testjjjjjjjjjjj]]></body><replier></replier><cre |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

这是数据表结构和数据记录：

mysql> show create table idiscussion;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| idiscussion | CREATE TABLE `idiscussion` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `iask_id` int(10) unsigned DEFAULT NULL,
  `ianswer_id` int(10) unsigned DEFAULT NULL,
  `uid` int(10) unsigned DEFAULT NULL,
  `body` varchar(600) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `i_idiscussion_uid` (`uid`),
  KEY `i_idiscussion_iask` (`iask_id`),
  KEY `i_idiscussion_ianswer` (`ianswer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select *from idiscussion;
+----+---------+------------+------+----------------------------+---------------------+
| id | iask_id | ianswer_id | uid  | body                       | created             |
+----+---------+------------+------+----------------------------+---------------------+
|  1 |       1 |       NULL | NULL | haha                       | 2009-05-27 04:57:51 |
|  2 |       1 |       NULL | NULL | haha                       | 2009-05-27 04:57:52 |
|  3 |       1 |       NULL | NULL | haha                       | 2009-05-27 04:57:52 |
|  4 |       1 |       NULL | NULL | haha                       | 2009-05-27 04:57:53 |
|  5 |    NULL |          1 | NULL | hey!                       | 2009-05-29 01:56:22 |
|  6 |    NULL |          1 | NULL | hey!                       | 2009-05-29 01:56:23 |
|  7 |    NULL |          1 | NULL | hey!                       | 2009-05-29 01:56:25 |
|  8 |    NULL |          2 | NULL | hey!                       | 2009-05-29 01:56:44 |
| 13 |    NULL |          1 | NULL | haha,works!                | 2009-05-29 21:29:50 |
| 14 |       1 |       NULL | NULL | r                          | 2009-05-29 21:55:44 |
| 15 |    NULL |          1 | NULL | so                         | 2009-05-29 21:55:57 |
| 16 |    NULL |          2 | NULL |                            | 2009-05-29 22:05:41 |
| 17 |    NULL |          3 | NULL | sss                        | 2009-05-29 22:16:27 |
| 18 |    NULL |          3 | NULL | hi                         | 2009-05-29 23:00:10 |
| 19 |    NULL |          1 | NULL | hi                         | 2009-05-29 23:03:06 |
| 20 |       1 |       NULL | NULL | s                          | 2009-05-29 23:04:39 |
| 21 |       1 |       NULL | NULL | dddret                     | 2009-05-29 23:15:41 |
| 22 |       1 |       NULL | NULL | sser                       | 2009-05-29 23:17:13 |
| 23 |       1 |       NULL | NULL | aaaaaaassaaaaaa            | 2009-05-29 23:23:32 |
| 24 |    NULL |          1 | NULL | aasasqwqwqw aa             | 2009-05-29 23:23:56 |
| 25 |    NULL |          6 | NULL | dianpinyixia...            | 2009-05-30 00:58:04 |
| 26 |       1 |       NULL | NULL | testjjjjjjjjjjj            | 2009-06-15 23:12:58 |
| 27 |       1 |       NULL | NULL | hhhhhhhhhhhhhhhhhhh        | 2009-06-15 23:14:08 |
| 28 |       1 |       NULL | NULL | hey..................      | 2009-06-15 23:18:47 |
| 29 |    NULL |          6 | NULL | kkkkkkkkkkkkkkkkkkkkk      | 2009-06-15 23:22:30 |
| 30 |    NULL |          6 | NULL | jjjjjjjjjjjjjjjjj          | 2009-06-15 23:24:58 |
| 31 |    NULL |          6 | NULL | ssssssssssssssssssssssssss | 2009-06-15 23:26:01 |
| 32 |       1 |       NULL | NULL | hahahahhaahhahahah         | 2009-06-15 23:26:30 |
| 33 |       1 |       NULL |    4 | .faint.................    | 2009-06-15 23:28:16 |
+----+---------+------------+------+----------------------------+---------------------+
29 rows in set (0.00 sec)

The query below is truncated,with 'created' showing only 'cre'(scroll to the right most first:):

 mysql> SELECT  GROUP_CONCAT(CONCAT('<comment><body><![CDATA[',body,']]></body>','<replier>',if(uid is not null,uid,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '')
        ->                                          FROM   idiscussion
        ->
        ->                                          WHERE  idiscussion.iask_id = 1;
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | GROUP_CONCAT(CONCAT('<comment><body><![CDATA[',body,']]></body>','<replier>',if(uid is not null,uid,''),'</replier>','<created>',created,'</created></comment>') SEPARATOR '')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | <comment><body><![CDATA[haha]]></body><replier></replier><created>2009-05-27 04:57:51</created></comment><comment><body><![CDATA[haha]]></body><replier></replier><created>2009-05-27 04:57:52</created></comment><comment><body><![CDATA[haha]]></body><replier></replier><created>2009-05-27 04:57:52</created></comment><comment><body><![CDATA[haha]]></body><replier></replier><created>2009-05-27 04:57:53</created></comment><comment><body><![CDATA[r]]></body><replier></replier><created>2009-05-29 21:55:44</created></comment><comment><body><![CDATA[s]]></body><replier></replier><created>2009-05-29 23:04:39</created></comment><comment><body><![CDATA[dddret]]></body><replier></replier><created>2009-05-29 23:15:41</created></comment><comment><body><![CDATA[sser]]></body><replier></replier><created>2009-05-29 23:17:13</created></comment><comment><body><![CDATA[aaaaaaassaaaaaa]]></body><replier></replier><created>2009-05-29 23:23:32</created></comment><comment><body><![CDATA[testjjjjjjjjjjj]]></body><replier></replier><cre |
    +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set, 1 warning (0.00 sec)

Here is datable structure and data records:

mysql> show create table idiscussion;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| idiscussion | CREATE TABLE `idiscussion` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `iask_id` int(10) unsigned DEFAULT NULL,
  `ianswer_id` int(10) unsigned DEFAULT NULL,
  `uid` int(10) unsigned DEFAULT NULL,
  `body` varchar(600) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `i_idiscussion_uid` (`uid`),
  KEY `i_idiscussion_iask` (`iask_id`),
  KEY `i_idiscussion_ianswer` (`ianswer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select *from idiscussion;
+----+---------+------------+------+----------------------------+---------------------+
| id | iask_id | ianswer_id | uid  | body                       | created             |
+----+---------+------------+------+----------------------------+---------------------+
|  1 |       1 |       NULL | NULL | haha                       | 2009-05-27 04:57:51 |
|  2 |       1 |       NULL | NULL | haha                       | 2009-05-27 04:57:52 |
|  3 |       1 |       NULL | NULL | haha                       | 2009-05-27 04:57:52 |
|  4 |       1 |       NULL | NULL | haha                       | 2009-05-27 04:57:53 |
|  5 |    NULL |          1 | NULL | hey!                       | 2009-05-29 01:56:22 |
|  6 |    NULL |          1 | NULL | hey!                       | 2009-05-29 01:56:23 |
|  7 |    NULL |          1 | NULL | hey!                       | 2009-05-29 01:56:25 |
|  8 |    NULL |          2 | NULL | hey!                       | 2009-05-29 01:56:44 |
| 13 |    NULL |          1 | NULL | haha,works!                | 2009-05-29 21:29:50 |
| 14 |       1 |       NULL | NULL | r                          | 2009-05-29 21:55:44 |
| 15 |    NULL |          1 | NULL | so                         | 2009-05-29 21:55:57 |
| 16 |    NULL |          2 | NULL |                            | 2009-05-29 22:05:41 |
| 17 |    NULL |          3 | NULL | sss                        | 2009-05-29 22:16:27 |
| 18 |    NULL |          3 | NULL | hi                         | 2009-05-29 23:00:10 |
| 19 |    NULL |          1 | NULL | hi                         | 2009-05-29 23:03:06 |
| 20 |       1 |       NULL | NULL | s                          | 2009-05-29 23:04:39 |
| 21 |       1 |       NULL | NULL | dddret                     | 2009-05-29 23:15:41 |
| 22 |       1 |       NULL | NULL | sser                       | 2009-05-29 23:17:13 |
| 23 |       1 |       NULL | NULL | aaaaaaassaaaaaa            | 2009-05-29 23:23:32 |
| 24 |    NULL |          1 | NULL | aasasqwqwqw aa             | 2009-05-29 23:23:56 |
| 25 |    NULL |          6 | NULL | dianpinyixia...            | 2009-05-30 00:58:04 |
| 26 |       1 |       NULL | NULL | testjjjjjjjjjjj            | 2009-06-15 23:12:58 |
| 27 |       1 |       NULL | NULL | hhhhhhhhhhhhhhhhhhh        | 2009-06-15 23:14:08 |
| 28 |       1 |       NULL | NULL | hey..................      | 2009-06-15 23:18:47 |
| 29 |    NULL |          6 | NULL | kkkkkkkkkkkkkkkkkkkkk      | 2009-06-15 23:22:30 |
| 30 |    NULL |          6 | NULL | jjjjjjjjjjjjjjjjj          | 2009-06-15 23:24:58 |
| 31 |    NULL |          6 | NULL | ssssssssssssssssssssssssss | 2009-06-15 23:26:01 |
| 32 |       1 |       NULL | NULL | hahahahhaahhahahah         | 2009-06-15 23:26:30 |
| 33 |       1 |       NULL |    4 | .faint.................    | 2009-06-15 23:28:16 |
+----+---------+------------+------+----------------------------+---------------------+
29 rows in set (0.00 sec)

收藏 0

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

评论（3）

物价感观 2024-07-31 16:28:34

这是一个配置设置：

my.cnf 中的 group_concat_max_len=4096

或

SET GLOBAL group_concat_max_len=4096

It's a configuration setting:

group_concat_max_len=4096 in my.cnf

or

SET GLOBAL group_concat_max_len=4096

回复收藏 0 原文

遮云壑 2024-07-31 16:28:34

来自文档：

结果将被截断为group_concat_max_len系统变量指定的最大长度，该变量的默认值为1024。尽管返回值的有效最大长度受到 max_allowed_packet 值的限制，但该值可以设置得更高。在运行时更改group_concat_max_len值的语法如下，其中val是一个无符号整数：

 SET [GLOBAL | SESSION] group_concat_max_len = val;

From documentation:

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;

回复收藏 0 原文

为你鎻心 2024-07-31 16:28:34

来自文档：

结果被截断为最大值
长度由下式给出
group_concat_max_len系统变量，
默认值为 1024。

截断的字符串自然长度为 1024 个字符。

回复收藏 0 原文

~没有更多了~

关于作者

暂无简介

0 文章

0 评论

23 人气

关注发私信

相关话题

热门标签

操作系统程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端

推荐作者

隔纱相望

文章 0 评论 0

昵称有卵用

文章 0 评论 0

梨涡

文章 0 评论 0

蓝咒

文章 0 评论 0

白芷

文章 0 评论 0

樱娆

文章 0 评论 0

友情链接

我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的隐私政策了解更多相关信息。单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。

原文