从MySQL的校验和表中选择并仅返回校验和(不是表) [英] Select and return only Checksum (not Table) from checksum table in mysql

问题描述

当我运行"mysql> CHECKSUM TABLE mytable;"时，得到以下结果:

+------------------+------------+
| Table            | Checksum   |
+------------------+------------+
| mydb.mytable     | 1679935596 |
+------------------+------------+

如何在一个mysql语句中选择并返回上述结果中的校验和(非表)? 像"SELECT Checksum FROM(CHECKSUM TABLE mytable);"之类的东西?尝试了几次，但不知道.

我想要的是:

+------------+
| Checksum   |
+------------+
| 1679935596 |
+------------+

解决方案

您可以按列或列总和进行操作.下面是对我的一张桌子的测试.

SELECT sum(crc32(email)) as crc from users;
+-------------+
| crc         |
+-------------+
| 10679459550 |
+-------------+


select sum(crc32(concat(user_id,first_name,last_name,email,reportingManager))) as crc from users;
+------------+
| crc        |
+------------+
| 7196315383 |
+------------+

编辑

在传递数据库名称和表名称的存储过程中，以下内容将返回crc.会转到特殊的 INFORMATION_SCHEMA 数据库，为您检索列名称，然后使用所有这些列和值来生成校验和.

存储过程:

drop procedure if exists getTableCRC32;
DELIMITER $$
create procedure getTableCRC32
(   dbname varchar(80),
    tableName varchar(80)
)
BEGIN
    set @sql1="select GROUP_CONCAT(`column_name` SEPARATOR ',') into @colNames";
    set @sql1=concat(@sql1," FROM `INFORMATION_SCHEMA`.`COLUMNS`");
    set @sql1=concat(@sql1," WHERE `TABLE_SCHEMA`='",dbName,"'");
    set @sql1=concat(@sql1," AND `TABLE_NAME`='",tableName,"'");
    -- select @sql1;
    PREPARE stmt1 FROM @sql1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    -- select @colNames;
    set @sql2=concat( 'select sum(crc32(concat(',  @colNames, '))) as crc from ',tableName);
    -- select @sql2;
    PREPARE stmt2 FROM @sql2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
END
$$
DELIMITER ;

测试:

call getTableCRC32('so_gibberish','users');
+------------+
| crc        |
+------------+
| 7196315383 |
+------------+
call getTableCRC32('so_gibberish','fish');
+------------+
| crc        |
+------------+
| 3273020843 |
+------------+

crc32的手册页

When I run "mysql> CHECKSUM TABLE mytable;", I got the following result:

+------------------+------------+
| Table            | Checksum   |
+------------------+------------+
| mydb.mytable     | 1679935596 |
+------------------+------------+

How to select and return only the Checksum (not Table) in the above result in one mysql statement? Something like "SELECT Checksum FROM (CHECKSUM TABLE mytable);"??? Tried several times, but no idea.

What I want is:

+------------+
| Checksum   |
+------------+
| 1679935596 |
+------------+

解决方案

You can do it by column, or sum of columns. Below is a test on a table of mine.

SELECT sum(crc32(email)) as crc from users;
+-------------+
| crc         |
+-------------+
| 10679459550 |
+-------------+


select sum(crc32(concat(user_id,first_name,last_name,email,reportingManager))) as crc from users;
+------------+
| crc        |
+------------+
| 7196315383 |
+------------+

Edit

In a stored procedure being passed a database name and a table name, the below will return the crc. It goes to the special INFORMATION_SCHEMA db to retrieve the column names for you, and uses all those columns and values to generate a checksum.

Stored procedure:

drop procedure if exists getTableCRC32;
DELIMITER $$
create procedure getTableCRC32
(   dbname varchar(80),
    tableName varchar(80)
)
BEGIN
    set @sql1="select GROUP_CONCAT(`column_name` SEPARATOR ',') into @colNames";
    set @sql1=concat(@sql1," FROM `INFORMATION_SCHEMA`.`COLUMNS`");
    set @sql1=concat(@sql1," WHERE `TABLE_SCHEMA`='",dbName,"'");
    set @sql1=concat(@sql1," AND `TABLE_NAME`='",tableName,"'");
    -- select @sql1;
    PREPARE stmt1 FROM @sql1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
    -- select @colNames;
    set @sql2=concat( 'select sum(crc32(concat(',  @colNames, '))) as crc from ',tableName);
    -- select @sql2;
    PREPARE stmt2 FROM @sql2;
    EXECUTE stmt2;
    DEALLOCATE PREPARE stmt2;
END
$$
DELIMITER ;

Test it:

call getTableCRC32('so_gibberish','users');
+------------+
| crc        |
+------------+
| 7196315383 |
+------------+
call getTableCRC32('so_gibberish','fish');
+------------+
| crc        |
+------------+
| 3273020843 |
+------------+

Manual page for crc32

这篇关于从MySQL的校验和表中选择并仅返回校验和(不是表)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！