group_concat 上的 Mysql 错误

Question

我发布了 in this link但我应该发布一个新的

我是 MySQL 初学者我尝试了上面的 exampleon 链接，但得到了一些错误代码

我的选择看起来像这样

SELECT YEAR(TimeStamp) AS "YEAR", MONTHNAME(TimeStamp) AS "MONTH", DAY(TimeStamp) AS "DAY",
       min(Temperature) AS "min", max(Temperature) AS "max",
       ROUND(AVG(Temperature),1) AS "avg",
       substring_index(group_concat(TimeStamp order by Temperature asc), ',', 1) as min_TimeStamp,
       substring_index(group_concat(TimeStamp order by Temperature desc), ',', 1) as max_TimeStamp
FROM ute_temp_min
    WHERE TimeStamp BETWEEN '2014-11-05' and NOW()
GROUP BY YEAR(TimeStamp), MONTH(TimeStamp),  DAY(TimeStamp);

这是我的输入表

index","TimeStamp","Temperature"
2721909,"2014-11-05 00:00:08",1.4
2721910,"2014-11-05 00:01:12",1.4
2721911,"2014-11-05 00:02:13",1.4
2721912,"2014-11-05 00:03:06",1.4

这是我的输出，看起来没问题

"YEAR","MONTH","DAY","min","max","avg","min_TimeStamp","max_TimeStamp"
2014,"November",5,-3.4,1.4,-0.6,"2014-11-05 22:15:16","2014-11-05 00:28:12"
2014,"November",6,-2.6,0.3,-0.9,"2014-11-06 01:34:08","2014-11-06 12:52:12"

但我得到这个错误(更多但相同但不同的行

Row 52 was cut by GROUP_CONCAT() errornr 1260

我的错误是什么？

斯文奥克

Answer 1

尝试设置您的 session 最大长度。我整个星期都在使用它来解决我们服务器上的数据完整性问题，因此 group concat 对此非常有用(可以直观地看到不应链接在一起的用户)，但我也遇到了数据被切断的问题

试试这个

SET SESSION group_concat_max_len = 999999999;
SELECT ... -- your query here

这很棒的事情是它只在你的当前 session 中(它是一个临时函数)..所以你没有在服务器上分配内存(磁盘空间)所以它不会减慢速度任何。 session 结束后，长度将变回默认值 (1024)!

编辑:

稍微清理一下您的查询

SELECT 
    YEAR(TimeStamp) AS "YEAR", 
    MONTHNAME(TimeStamp) AS "MONTH", 
    DAY(TimeStamp) AS "DAY",
    min(Temperature) AS "min", 
    max(Temperature) AS "max",
    ROUND(AVG(Temperature),1) AS "avg",
    substring_index(group_concat(TimeStamp order by Temperature asc), ',', 1) as min_TimeStamp,
    substring_index(group_concat(TimeStamp order by Temperature desc), ',', 1) as max_TimeStamp
FROM ute_temp_min
WHERE DATE(TimeStamp) BETWEEN '2014-11-05' and CURDATE()
GROUP BY DATE(TimeStamp);

注意我更改了 WHERE 来进行日期比较(这样 MySQL 就不必自己将字符串日期转换为日期时间)
我还将 GROUP BY 更改为 DATE() 以减少计算