Home > Article > Backend Development > Data table migration data consistency verification
It is still very useful to do some necessary verification when migrating the database. For example, whether the number of data items before and after migration is consistent, and whether the data is consistent. What should I do at this time? It is easy to verify the number of items, but what if I verify whether the data is consistent? For important data, of course, every piece of data must be error-free. Random sampling verification is definitely not possible. If it is missed, it will be troublesome, and the two tables are no longer on the same server. What to do at this time? There is a way:
The above method was thought up at the same time, and it is not bad, but I think there is room for improvement:
My thoughts are this:
The advantage of the second method is that the output file will be reduced within a certain range, making it easy to compare. However, it also has disadvantages. It cannot directly locate the location of different data through key fields like the first method.
The following is the specific code implementation of the second method’s effect sum:
<?php /** * 使用方法: * php -f mysql_diff.php yes dir 10 * 是否计算条数 是否计算输出d5并保存到文件 合并数据的级别 * */ if(php_sapi_name() != 'cli') { die("请在CLI模式下运行"); } array_shift($argv); if(empty($argv)) { die("at letase contain one info"); } $is_count = array_shift($argv); $is_md5 = empty($argv) ? false : array_shift($argv); $conbine_num = empty($argv) ? 1 : intval(array_shift($argv)); if($is_md5 && !is_dir($is_md5) && !mkdir($is_md5, 777, true)) { die("error info : md5 info must be input to a file"); } $dbinfos = array( 'host' => 'localhost', 'port' => '3306', 'user' => 'root', 'pswd' => '123456', 'charset' => 'utf8', 'tables' => array( 'lagou.pos', 'lagou.pos_innodb', ), ); //验证格式 if(!$link = mysql_connect($dbinfos['host'].":".$dbinfos['port'],$dbinfos['user'], $dbinfos['pswd'])) { die("connect to [{$host}@{$port}] failed!!"); } if(!mysql_query("set names {$dbinfos['charset']}")) { die("set charset error : ".mysql_error()); } foreach ($dbinfos['tables'] as $table) { if($is_count) { $sql = "select count(*) as nums from {$table}"; $ret = mysql_query($sql); if(!$ret) { die("error : ".mysql_error()); } $ret = mysql_fetch_array($ret, MYSQL_ASSOC); echo "{$table} : {$ret['nums']}\n"; } if($is_md5) { $path = $is_md5.DIRECTORY_SEPARATOR.$table; $sql = "select * from {$table}"; $ret = mysql_query($sql); $flag = 0; $fields = ''; while ($_ret = mysql_fetch_array($ret, MYSQL_NUM)) { $flag++; while($_ret) { $fields .= array_pop($_ret); } if($flag % $conbine_num == 0) { file_put_contents($path, md5($fields)."\n", FILE_APPEND); $fields = ''; } } if($flag % $conbine_num != 0 && $flag > 0) { file_put_contents($path, md5($fields)."\n", FILE_APPEND); } echo "save to file info : ".realpath($path)."\n"; } }
The above introduces the data consistency verification of data table migration, including aspects of the content. I hope it will be helpful to friends who are interested in PHP tutorials.