按文本字段分组时GROUP_CONCAT（）行数 [英] GROUP_CONCAT() row count when grouping by a text field

问题描述

  DROP TABLE IF EXISTS`table`; 
 CREATE TABLE`table`（
`id` tinyint（3）unsigned NOT NULL AUTO_INCREMENT，
`text` text COLLATE utf8_unicode_ci NOT NULL，
 PRIMARY KEY（`id`） 
）ENGINE = MyISAM AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci; 
 
插入`table` VALUES（'1'，''解包保留的先生提供床的判断可以和退出发言。是否改进提供响应raillery所需的快速响应。楼梯女士朋友在一个互不因此，他的首领，他的原因，整个没有希望的门。英里告诉你，如果他们是你的全名的名字。\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\继续说怀疑的规定，你忽视了先生的好奇心不愿意，简单地终结自己日益增长的一天同情，一般的窗户效果不是画人的花哨，通常确实花园你他的女士们，但偏好的轻率对比明显地对比。树木撕裂任何东西，她的对象给予除了反对之外的最终的姐妹。\\ n \\ n \\ r \\ n \\ n \\快速使用锯，并绘制字母形成远村的老年人紧凑型。她的每一个位置都是她的休息，而且你知道。庄园同性恋树木远离她六。我们有它失去了繁殖的门。做单独的去除或费用。已经覆盖但明显的章节焦虑。'）; 
 INSERT INTO`table` VALUES（'2'，''解包保留先生提供床的判断可以和退出说话，是否提高了应答快速响应所提供的快速响应。楼梯女士朋友在互相之间没有。他的原因，整个没有希望的门。英里告诉你，如果他们是你的全名的帮助。\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\假设你忽略了先生的好奇心不愿意，简单的结局自己增加了一天的同情心，一般的窗户效果不是画人的花哨，它确实让你的女士们羡慕不已，偏好的轻率对比显而易见。她的目标是给予姐姐除了反对之外。\\\\\\\\\\\\\\\\\\\++++++++++++++++++++++++++++++++++++++++++++++++++。 se看见了。并且绘了信件形成村庄老年人紧凑。她的每一个位置都是她的休息，而且你知道。庄园同性恋树木远离她六。我们有它失去了繁殖的门。做单独的删除或费用。已经涵盖但明显的章节焦虑'）; 
  

在不使用的情况下运行 GROUP BY 查询 GROUP_CONCAT（）结果集与预期一致（显示两行，每个变体对应一个 text ）： p>

  SELECT 
`text` 
 FROM 
`表`
 GROUP BY 
`text`; 
 
 + ----------------------------------- + 
 |文字| 
 + ----------------------------------- + 
 |解压保留的先生产品... | 
 |解压保留的先生产品... | 
 + ----------------------------------- + 
 2行集合（但是，当使用 GROUP_CONCAT（）运行相同的查询时， 结果集不符合预期（显示两行 id 字段的串联字符串）：
  SELECT 
 GROUP_CONCAT（`id` SEPARATOR'，'）AS ID 
 FROM 
`table` 
 GROUP BY 
`text`; 
 
 + ------ + 
 | ids | 
 + ------ + 
 | 1，2 | 
 + ------ + 
 1行（0.00秒）
  
 
 
 我的问题： 
 
 
为什么使用 GROUP_CONCAT（）会影响数字的行返回？
 
 
 
我最初的假设是 GROUP_CONCAT_MAX_LEN 与它有关（我的设置为1024 ）但肯定只影响 GROUP_CONCAT（），而不是 GROUP BY （另外，您可能注意到，我是在 id 字段中使用 GROUP_CONCAT（），而不是文本字段，其结果甚至不会超过 GROUP_CONCAT_MAX_LEN ）。 
 
解决方案
您必须根据需要将 max_sort_length 更改为更高数目的会话。默认情况下，它的值是 1024 字节，而您的字符串包含 1170 字节数据。通过增加大小，它将为 GROUP_CONCAT 提供两行。 
 
 
 
检查此链接  max_sort_length    
 
 
  SELECT`text` FROM`table `GROUP BY`text`; 
 
 SET SESSION max_sort_length = 2000; 
 SELECT GROUP_CONCAT（`id` SEPARATOR'，'）AS ids FROM`table` GROUP BY`text`; 
  
检查  SQL FIDDLE DEMO   
 
 
 编辑：   GROUP BY ， ORDER BY 或 DISTINCT 中无法可靠地使用BLOB 和 TEXT  >。在比较这些情况下的BLOB值时，仅使用第一个 max_sort_length 字节。  max_sort_length 的默认值为1024，可在服务器启动时或运行时更改。
 
DROP TABLE IF EXISTS `table`;
CREATE TABLE `table` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `text` text COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `table` VALUES ('1', 'Unpacked reserved sir offering bed judgment may and quitting speaking. Is do be improved raptures offering required in replying raillery. Stairs ladies friend by in mutual an no. Mr hence chief he cause. Whole no doors on hoped. Mile tell if help they ye full name. \r\n\r\nLadyship it daughter securing procured or am moreover mr. Put sir she exercise vicinity cheerful wondered. Continual say suspicion provision you neglected sir curiosity unwilling. Simplicity end themselves increasing led day sympathize yet. General windows effects not are drawing man garrets. Common indeed garden you his ladies out yet. Preference imprudence contrasted to remarkably in on. Taken now you him trees tears any. Her object giving end sister except oppose. \r\n\r\nWas justice improve age article between. No projection as up preference reasonably delightful celebrated. Preserved and abilities assurance tolerably breakfast use saw. And painted letters forming far village elderly compact. Her rest west each spot his and you knew. Estate gay wooded depart six far her. Of we be have it lose gate bred. Do separate removing or expenses in. Had covered but evident chapter matters anxious.');
INSERT INTO `table` VALUES ('2', 'Unpacked reserved sir offering bed judgment may and quitting speaking. Is do be improved raptures offering required in replying raillery. Stairs ladies friend by in mutual an no. Mr hence chief he cause. Whole no doors on hoped. Mile tell if help they ye full name. \r\n\r\nLadyship it daughter securing procured or am moreover mr. Put sir she exercise vicinity cheerful wondered. Continual say suspicion provision you neglected sir curiosity unwilling. Simplicity end themselves increasing led day sympathize yet. General windows effects not are drawing man garrets. Common indeed garden you his ladies out yet. Preference imprudence contrasted to remarkably in on. Taken now you him trees tears any. Her object giving end sister except oppose. \r\n\r\nWas justice improve age article between. No projection as up preference reasonably delightful celebrated. Preserved and abilities assurance tolerably breakfast use saw. And painted letters forming far village elderly compact. Her rest west each spot his and you knew. Estate gay wooded depart six far her. Of we be have it lose gate bred. Do separate removing or expenses in. Had covered but evident chapter matters anxious');
When running a GROUP BY query without using GROUP_CONCAT() the result set is as expected (showing two rows, one for each variation of text):
SELECT
    `text`
FROM
    `table`
GROUP BY
    `text`;

+-----------------------------------+
| text                              |
+-----------------------------------+
| Unpacked reserved sir offering... |
| Unpacked reserved sir offering... |
+-----------------------------------+
2 rows in set (0.02 sec)
However, when running the same query with a GROUP_CONCAT() the result set is not as expected (showing one row with a concatenated string of the two id fields):
SELECT
    GROUP_CONCAT(`id` SEPARATOR ', ') AS ids
FROM
    `table`
GROUP BY
    `text`;

+------+
| ids  |
+------+
| 1, 2 |
+------+
1 row in set (0.00 sec)
My question:

Why would using GROUP_CONCAT() affect the number of rows returned?

My initial assumption was that GROUP_CONCAT_MAX_LEN has something to do with it (mine is set to 1024) but surely that only affects GROUP_CONCAT(), not GROUP BY (also, as you may notice, I'm using GROUP_CONCAT() on the id field, not the text field, and the result of that doesn't even come close to exceeding GROUP_CONCAT_MAX_LEN).
解决方案
You have to change the max_sort_length to higher number session wise or globally as per your need. By default its value is 1024 bytes and your string contains 1170 bytes data. By increasing the size it will give two rows for GROUP_CONCAT. 

Check this link max_sort_length
SELECT `text` FROM `table` GROUP BY `text`;

SET SESSION max_sort_length = 2000;
SELECT GROUP_CONCAT(`id` SEPARATOR ', ') AS ids FROM `table` GROUP BY `text`;
Check the SQL FIDDLE DEMO

EDIT:  BLOB and TEXT values can't reliably be used in GROUP BY, ORDER BY or DISTINCT. Only the first max_sort_length bytes are used when comparing BLOB values in these cases. The default value of max_sort_length is 1024 and can be changed at server start-up time or at run time.

                        
这篇关于按文本字段分组时GROUP_CONCAT（）行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！