CREATEDEFINER=`***项目必要,已隐藏**`@`%`PROCEDURE`compare_twotb_data`(IN`TABLE1`VARCHAR(50),IN`TABLE2`VARCHAR(50),IN`timestr`VARCHAR(50))LANGUAGESQLNOTDETERMINISTICCONTAINSSQLSQL SECURITY DEFINERCOMMENT'比较两张表的数据是否一致'BEGINDECLARE cnt INT;DECLARE colname VARCHAR(100)DEFAULTNULL;DECLARE done INTDEFAULT0;-- 声明游标DECLARE cur CURSORFORselect COLUMN_NAME from information_schema.COLUMNSwhere TABLE_NAME = TABLE1
AND COLUMN_NAME NOTIN('ID','COMCODE','ENDDATA','DATAFLAG');DECLARECONTINUEHANDLERFORNOT FOUND SET done =1;-- 打开游标OPEN cur ;-- 使用repeat循环语法REPEAT-- 批读取数据到指定变量上FETCH cur INTO colname;set@stmt= CONCAT('SELECT COUNT(1) FROM ',TABLE1,' a
INNER JOIN ',TABLE2,' b ON a.COMCODE = b.COMCODE
‘/*
此处省略(原为两字段的匹配项)
*/’
WHERE a.ENDDATE = ',timestr,'
AND a.',colname ,' != b.',colname,' into @cnt;');PREPARE stmt1 FROM@stmt;EXECUTE stmt1;deallocateprepare stmt1;SET cnt =@cnt;if cnt >0thenINSERTINTO compare_table_date_log(TABLE1,TABLE2,COLNAME,diffcnt)VALUES(TABLE1,TABLE2,colname,cnt);ENDif;-- 循环结束条件
UNTIL done
ENDREPEAT;-- 关闭游标CLOSE cur ;END
背景:比如生产库数据和测试库数据,我现在有脚本的变更或者优化,我先再需要比较同一批次的数据是否一致思路:每一条比对上的字段对应的数据都需要一致,于是我先比对每一条字段的数据是否一致,然后对字段进行循环即可。大体代码奉上:CREATE DEFINER=`***项目必要,已隐藏**`@`%` PROCEDURE `compare_twotb_data`( IN `TABLE1` VARCHAR(50), IN `TABLE2` VARCHAR(50), IN `timestr` VARCHAR