解决方案就是完全省略group by 1 = 1
。我假设这group_concat
将要求我为其提供一个组,但是可以将其直接用于子查询,如下所示:
select group_concat(id,col1,col2) from
(select * from some_table
where id >= 2 and id < 5
order by id desc) as some_table;
请注意,需要将null值强制转换为concat-friendly,例如:
insert into some_table (col1, col2)
values ('a', 1),
('b', 11),
('c', NULL),
('d', 25),
('e', 50);
select group_concat(id, col1, col2) from
(select id, col1, ifnull(col2, 'NULL') as col2
from some_table
where id >= 2 and id < 5
order by id desc) as some_table;
输出:
+------------------------------+
| group_concat(id, col1, col2) |
+------------------------------+
| 2b11,3cNULL,4d25 |
+------------------------------+
另一个警告:MysqL的最大长度group_concat
由变量:定义group_concat_max_len
。为了散列 n个 表行的串联,我需要:
最终,我最终使用了客户端语言来检查每列的名称,编号和可空性,然后构建如下查询:
select md5(group_concat(row_fingerprint)) from
(select concat(id, col1, ifnull(col2, 'null')) as row_fingerprint
from some_table
where id >= 2 and id < 5
order by id desc) as foo;