mysql Averaging varchar column

Question

We have same table in two different DBs (Table structure: PK(Long), X(VarChar), Version(int)), want to compare those two and see either they are in sync or not. X - holds String, not numeric values.

We don't want to compare record by record. We thought of comparing Avg(Pk) and Avg(X) with the other tables Avg(Pk) and Avg(X) - if both are matching - we assume it is proper and proceed. I know some scenarios it fails, its ok as we do repeatedly this - we believe it will catch next time.

But when I tried

select avg(pk), avg(X) from TEST_varchar20

It shows warning

Truncated incorrect DOUBLE value

and does not give correct value.

Is it possible to use Avg() for Varchar or any other solution.

Answer 1

I guess i got the answer - still going thru it :

select CONV(BIT_XOR(CAST(CRC32(PK) AS UNSIGNED)), 10, 16), CONV(BIT_XOR(CAST(CRC32(X) AS UNSIGNED)), 10, 16) from TEST_varchar20 ;

Source Create an aggregate checksum of a column