如何在整个子查询上使用group_concat? [英] How can I use group_concat on an entire subquery?

问题描述

...无需进行不必要的比较

...without making unnecessary comparisons

我想获取一系列行的md5哈希值.由于带宽限制，我希望它可以在服务器端进行.

I want to get an md5 hash of a range of rows. Due to bandwidth limitations, I want it to happen server-side.

这有效:

create table some_table (id int auto_increment,
                         col1 varchar(1),
                         col2 int,
                         primary key (id));

insert into some_table (col1, col2)
                values ('a', 1),
                       ('b', 11),
                       ('c', 12),
                       ('d', 25),
                       ('e', 50);

select group_concat(id,col1,col2) from
    (select * from some_table
     where id >= 2 and id < 5
     order by id desc) as some_table
group by 1 = 1;

输出:

+----------------------------+
| group_concat(id,col1,col2) |
+----------------------------+
| 2b11,3c12,4d25             |
+----------------------------+

并带有哈希:

select md5(group_concat(id,col1,col2)) from
    (select * from some_table
     where id >= 2 and id < 5
     order by id desc) as some_table
group by 1 = 1;

输出:

+----------------------------------+
| md5(group_concat(id,col1,col2))  |
+----------------------------------+
| 32c1f1dd34d3ebd33ca7d95f3411888e |
+----------------------------------+

但是我觉得应该有更好的方法.

But I feel like there should be a better way.

特别是，我想避免将1与1百万次进行比较，这是我发现将行范围划分为一组所必需的，以便使用group_concat，而我需要使用group_concat md5在多行上.

Particularly, I want to avoid comparing 1 with 1 millions of times, something I found necessary in order to get the row range into a group, which I needed in order to use group_concat, which I needed in order to use md5 on mutiple rows.

是否有一种方法可以在行范围上使用group_concat(或类似名称)，而无需进行不必要的比较?

Is there a way to use group_concat (or similar) on a row range, without the needless comparison?

修改

我想对多行进行哈希处理，以便可以比较不同服务器上产生的哈希值.如果它们不同，则可以得出结论，子查询返回的行有所不同.

I want to hash multiple rows so that I can compare the resulting hashes on different servers. If they are different I can conclude that there is a difference in the rows returned by the subquery.

推荐答案

解决方案是完全省略group by 1 = 1.我以为group_concat将要求我为其提供一个组，但可以直接在子查询上使用它，如下所示:

The solution was simply to omit group by 1 = 1 entirely. I had assumed that group_concat would require that I provide it a group, but it can be used directly on a subquery, like so:

select group_concat(id,col1,col2) from
    (select * from some_table
     where id >= 2 and id < 5
     order by id desc) as some_table;

---

请注意，需要将空值强制转换为对concat友好的对象，例如:

---

Be aware that null values will need to be cast to something concat-friendly, like so:

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_max_len定义的group_concat具有最大长度.为了散列 n 个表行的串联，我需要:

---

Another caveat: mysql has a max-length for group_concat defined by the variable: group_concat_max_len. In order to hash a concatenation of n table rows, I needed to:

1. 哈希行，使其以32位表示，而不管其具有多少列
2. 确保group_concat_max_len > (n * 33)(多余的字节用于添加逗号)
3. 散列散列行的group_concat.

1. Hash row so that it is represented in 32 bits, regardless of how many columns it has
2. Ensure that group_concat_max_len > (n * 33) (the extra byte accounts for added commas)
3. Hash the group_concat of the hashed rows.

最终，我最终使用了客户端语言来检查每列的名称，编号和可空性，然后构建如下查询:

Ultimately I ended up using the client language to examine the name, number, and nullability of each column, and then build queries like this:

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;

有关更多详细信息，您可以在我的代码此处 a>(请参阅函数:find_diff_intervals).

For more detail, you can poke through my code here (see function: find_diff_intervals).

这篇关于如何在整个子查询上使用group_concat?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！