Mysql verification program after importing data

Refer to the data verification after mysql imports the sample database employees. You can use md5 or sha.

Principle and idea: First, generate each row and column of data in each table in the database to be backed up. The md5 value is calculated cumulatively, and then hardcoded in the test unit file as the expected value.

The following is the verification method of 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 available

SET @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;

The above is the content of the verification program after mysql imports data. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!