Checksum of SELECT results in MySQL

Question

Trying to get a check sum of results of a SELECT statement, tried this

SELECT sum(crc32(column_one))
FROM database.table;

Which worked, but this did not work:

SELECT CONCAT(sum(crc32(column_one)),sum(crc32(column_two)))
FROM database.table;

Open to suggestions, main idea is to get a valid checksum for the SUM of the results of rows and columns from a SELECT statement.

Oxen

What do you mean "did not work"? Error messages? Unexpected result? Server crashed? – Disaccredit

Answer 1

The problem is that CONCAT and SUM are not compatible in this format.

CONCAT is designed to run once per row in your result set on the arguments as defined by that row.

SUM is an aggregate function, designed to run on a full result set.

CRC32 is of the same class of functions as CONCAT.

So, you've got functions nested in a way that just don't play nicely together.

You could try:

SELECT CONCAT(
    (SELECT sum(crc32(column_one)) FROM database.table),
    (SELECT sum(crc32(column_two)) FROM database.table)
);

or

SELECT sum(crc32(column_one)), sum(crc32(column_two))
FROM database.table;

and concatenate them with your client language.

Preposterous

Please always keep in mind for your large tables of "Expected collisions" https://mcmap.net/q/1922958/-probability-of-collision-when-using-a-32-bit-hash Which potentially could give you a false positive – Insole

Answer 2

SELECT SUM(CRC32(CONCAT(column_one, column_two)))
FROM database.table;

or

SELECT SUM(CRC32(column_one) + CRC32(column_two))
FROM database.table;

Frigid