mysql 导入数据后的校验程序

来源：互联网发布：网络招聘信息平台编辑：程序博客网时间：2024/06/01 15:54

参考mysql导入样本数据库employees之后的数据校验，可以使用md5或者sha，

原理与思路：首先在将要备份的数据库中生成每个表里的每行每列数据的累加计算md5值，接着hardcode在测试单元文件中，作为期望值。

以下是md5的校验方法

USE employees;SELECT 'TESTING INSTALLATION' as 'INFO';SET storage_engine=MyISAM;DROP TABLE IF EXISTS expected_values, found_values;CREATE TABLE expected_values (    table_name varchar(30) not null primary key,    recs int not null,    crc_sha varchar(100) not null,    crc_md5 varchar(100) not null) ENGINE=MyISAM;CREATE TABLE found_values LIKE expected_values;INSERT INTO `expected_values` VALUES ('employees',   300024,'4d4aa689914d8fd41db7e45c2168e7dcb9697359',                        '4ec56ab5ba37218d187cf6ab09ce1aa1'),('departments',      9,'4b315afa0e35ca6649df897b958345bcb3d2b764',                       'd1af5e170d2d1591d776d5638d71fc5f'),('dept_manager',    24,'9687a7d6f93ca8847388a42a6d8d93982a841c6c',                       '8720e2f0853ac9096b689c14664f847e'),('dept_emp',    331603, 'd95ab9fe07df0865f592574b3b33b9c741d9fd1b',                       # 'f16f6ce609d032d6b1b34748421e9195c5083da8', Bug#320513                       'ccf6fe516f990bdaa49713fc478701b7'),                       # 'c2c4fc7f0506e50959a6c67ad55cac31'),('titles',      443308,'d12d5f746b88f07e69b9e36675b6067abb01b60e',                       'bfa016c472df68e70a03facafa1bc0a8'),('salaries',   2844047,'b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f',                       'fd220654e95aea1b169624ffe3fca934');SELECT table_name, recs AS expected_records, crc_md5 AS expected_crc FROM expected_values;DROP TABLE IF EXISTS tchecksum;CREATE TABLE tchecksum (chk char(100)) ENGINE=myisam;SET @crc= '';INSERT INTO tchecksum     SELECT @crc := MD5(CONCAT_WS('#',@crc,                emp_no,birth_date,first_name,last_name,gender,hire_date))     FROM employees ORDER BY emp_no;INSERT INTO found_values VALUES ('employees', (SELECT COUNT(*) FROM employees), @crc,@crc);TRUNCATE tchecksum; -- if BlackHole is not availableSET @crc = '';INSERT INTO tchecksum     SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,dept_name))     FROM departments ORDER BY dept_no;INSERT INTO found_values values ('departments', (SELECT COUNT(*) FROM departments), @crc,@crc);TRUNCATE tchecksum; SET @crc = '';INSERT INTO tchecksum     SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,emp_no, from_date,to_date))     FROM dept_manager ORDER BY dept_no,emp_no;INSERT INTO found_values values ('dept_manager', (SELECT COUNT(*) FROM dept_manager), @crc,@crc);TRUNCATE tchecksum; SET @crc = '';INSERT INTO tchecksum     SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,emp_no, from_date,to_date))     FROM dept_emp ORDER BY dept_no,emp_no;INSERT INTO found_values values ('dept_emp', (SELECT COUNT(*) FROM dept_emp), @crc,@crc);TRUNCATE tchecksum; SET @crc = '';INSERT INTO tchecksum     SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no, title, from_date,to_date))     FROM titles order by emp_no,title,from_date;INSERT INTO found_values values ('titles', (SELECT COUNT(*) FROM titles), @crc,@crc);TRUNCATE tchecksum; SET @crc = '';INSERT INTO tchecksum     SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no, salary, from_date,to_date))     FROM salaries order by emp_no,from_date,to_date;INSERT INTO found_values values ('salaries', (SELECT COUNT(*) FROM salaries), @crc,@crc);DROP TABLE tchecksum;SELECT table_name, recs as 'found_records   ', crc_md5 as found_crc from found_values;SELECT      e.table_name,     IF(e.recs=f.recs,'OK', 'not ok') AS records_match,     IF(e.crc_md5=f.crc_md5,'ok','not ok') AS crc_match from     expected_values e INNER JOIN found_values f USING (table_name); DROP TABLE expected_values,found_values;

0 0