代码之家 › 专栏 › 技术社区 › echoblaze

MySQL:比较两个表之间的差异

mysql sql

echoblaze · 技术社区 · 17 年前

同 oracle diff: how to compare two tables?

更确切地说,我试图找出一个简单的SQL查询,它告诉我t1中一行的数据是否与t2中对应行的数据不同

SELECT * FROM robot intersect SELECT * FROM tbd_robot

[错误代码:1064,SQL状态:42000]您的SQL中有错误句法;查看与MySQL服务器版本对应的手册在第1行的“SELECT*FROM tbd_robot”附近使用正确的语法

我在语法上做错了什么吗?如果没有,我可以使用另一个查询吗?

编辑:另外,我正在通过免费版本的DbVisualizer进行查询。不确定这是否是一个因素。

10 回复 | 直到 5 年前

Abhijeet Ashok Muneshwar Quassnoi 6 年前

INTERSECT 需要在中模拟 MySQL :

SELECT  'robot' AS `set`, r.*
FROM    robot r
WHERE   ROW(r.col1, r.col2, â¦) NOT IN
        (
        SELECT  col1, col2, ...
        FROM    tbd_robot
        )
UNION ALL
SELECT  'tbd_robot' AS `set`, t.*
FROM    tbd_robot t
WHERE   ROW(t.col1, t.col2, â¦) NOT IN
        (
        SELECT  col1, col2, ...
        FROM    robot
        )

Roee Adler 17 年前

您可以使用UNION手动构造交点。如果您在两个表中都有一些唯一的字段,例如ID,则很容易:

SELECT * FROM T1
WHERE ID NOT IN (SELECT ID FROM T2)

UNION

SELECT * FROM T2
WHERE ID NOT IN (SELECT ID FROM T1)

zzapper 8 年前

 select t1.user_id,t2.user_id 
 from t1 left join t2 ON t1.user_id = t2.user_id 
 and t1.username=t2.username 
 and t1.first_name=t2.first_name 
 and t1.last_name=t2.last_name

试试这个。这将比较您的表并找到所有匹配的对,如果有任何不匹配,将在左侧返回NULL。

Steve Folly 9 年前

link

SELECT MIN (tbl_name) AS tbl_name, PK, column_list
FROM
 (
  SELECT ' source_table ' as tbl_name, S.PK, S.column_list
  FROM source_table AS S
  UNION ALL
  SELECT 'destination_table' as tbl_name, D.PK, D.column_list
  FROM destination_table AS D 
)  AS alias_table
GROUP BY PK, column_list
HAVING COUNT(*) = 1
ORDER BY PK

clearlight anky_believeMe 9 年前

根据Haim的回答,我创建了一个PHP代码来测试和显示两个数据库之间的所有差异。您必须更改您的详细信息<>变量内容。

<?php

    $User = "<DatabaseUser>";
    $Pass = "<DatabasePassword>";
    $SourceDB = "<SourceDatabase>";
    $TestDB = "<DatabaseToTest>";

    $link = new mysqli( "p:". "localhost", $User, $Pass, "" );

    if ( mysqli_connect_error() ) {

        die('Connect Error ('. mysqli_connect_errno() .') '. mysqli_connect_error());

    }

    mysqli_set_charset( $link, "utf8" );
    mb_language( "uni" );
    mb_internal_encoding( "UTF-8" );

    $sQuery = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="'. $SourceDB .'";';

    $SourceDB_Content = query( $link, $sQuery );

    if ( !is_array( $SourceDB_Content) ) {

        echo "Table $SourceDB cannot be accessed";
        exit(0);

    }

    $sQuery = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="'. $TestDB .'";';

    $TestDB_Content = query( $link, $sQuery );

    if ( !is_array( $TestDB_Content) ) {

        echo "Table $TestDB cannot be accessed";
        exit(0);

    }

    $SourceDB_Tables = array();
    foreach( $SourceDB_Content as $item ) {
        $SourceDB_Tables[] = $item["TABLE_NAME"];
    }

    $TestDB_Tables = array();
    foreach( $TestDB_Content as $item ) {
        $TestDB_Tables[] = $item["TABLE_NAME"];
    }
    //var_dump( $SourceDB_Tables, $TestDB_Tables );
    $LookupTables = array_merge( $SourceDB_Tables, $TestDB_Tables );
    $NoOfDiscrepancies = 0;
    echo "

    <table border='1' width='100%'>
    <tr>
        <td>Table</td>
        <td>Found in $SourceDB (". count( $SourceDB_Tables ) .")</td>
        <td>Found in $TestDB (". count( $TestDB_Tables ) .")</td>
        <td>Test result</td>
    <tr>

    ";

    foreach( $LookupTables as $table ) {

        $FoundInSourceDB = in_array( $table, $SourceDB_Tables ) ? 1 : 0;
        $FoundInTestDB = in_array( $table, $TestDB_Tables ) ? 1 : 0;
        echo "

    <tr>
        <td>$table</td>
        <td><input type='checkbox' ". ($FoundInSourceDB == 1 ? "checked" : "") ."></td> 
        <td><input type='checkbox' ". ($FoundInTestDB == 1 ? "checked" : "") ."></td>   
        <td>". compareTables( $SourceDB, $TestDB, $table ) ."</td>  
    </tr>   
        ";

    }

    echo "

    </table>
    <br><br>
    No of discrepancies found: $NoOfDiscrepancies
    ";


    function query( $link, $q ) {

        $result = mysqli_query( $link, $q );

        $errors = mysqli_error($link);
        if ( $errors > "" ) {

            echo $errors;
            exit(0);

        }

        if( $result == false ) return false;
        else if ( $result === true ) return true;
        else {

            $rset = array();

            while ( $row = mysqli_fetch_assoc( $result ) ) {

                $rset[] = $row;

            }

            return $rset;

        }

    }

    function compareTables( $source, $test, $table ) {

        global $link;
        global $NoOfDiscrepancies;

        $sQuery = "

    SELECT column_name,ordinal_position,data_type,column_type FROM
    (
        SELECT
            column_name,ordinal_position,
            data_type,column_type,COUNT(1) rowcount
        FROM information_schema.columns
        WHERE
        (
            (table_schema='$source' AND table_name='$table') OR
            (table_schema='$test' AND table_name='$table')
        )
        AND table_name IN ('$table')
        GROUP BY
            column_name,ordinal_position,
            data_type,column_type
        HAVING COUNT(1)=1
    ) A;    

        ";

        $result = query( $link, $sQuery );

        $data = "";
        if( is_array( $result ) && count( $result ) > 0 ) {

            $NoOfDiscrepancies++;
            $data = "<table><tr><td>column_name</td><td>ordinal_position</td><td>data_type</td><td>column_type</td></tr>";

            foreach( $result as $item ) {

                $data .= "<tr><td>". $item["column_name"] ."</td><td>". $item["ordinal_position"] ."</td><td>". $item["data_type"] ."</td><td>". $item["column_type"] ."</td></tr>";

            }

            $data .= "</table>";

            return $data;

        }
        else {

            return "Checked but no discrepancies found!";

        }

    }

?>

shareef 5 年前

下面的问题,是我做大更新前后对比表!。

,您可以使用以下命令:

在终端中,

mysqldump -hlocalhost -uroot -p schema_name_here table_name_here > /home/ubuntu/database_dumps/dump_table_before_running_update.sql

mysqldump -hlocalhost -uroot -p schema_name_here table_name_here > /home/ubuntu/database_dumps/dump_table_after_running_update.sql

diff -uP /home/ubuntu/database_dumps/dump_some_table_after_running_update.sql /home/ubuntu/database_dumps/dump_table_before_running_update.sql > /home/ubuntu/database_dumps/diff.txt

您将需要在线工具为了

格式化从转储导出的SQL,

例如 http://www.dpriver.com/pp/sqlformat.htm [不是我见过的最好的]

diff.txt
在线对两条线路进行差异分析;+英寸 diff.txt

例如 https://www.diffchecker.com [您可以保存和共享它,并且对文件大小没有限制!]

备注 :如果是敏感/生产数据,请格外小心!

zifang zhuge 3 年前

你可以试试大数据比较平台 https://github.com/zhugezifang/dataCompare

这是它的介绍

开源大数据比对平台的设计与实践

1.背景和;当前形势

在开发大量数据的过程中,经常会遇到数据迁移或升级,或者不同的业务方根据自己的需求处理了数据,但认为双方的数据仍然相同,因此有必要手动比较数据。那么,双方的数据是否一致?如果没有,有什么区别?

如果没有平台,您需要手动编写一些SQL脚本进行比较,并且没有评估标准。这是低效的。

《阿里巴巴的大数据之路》实际上提到了这样一个平台,但由于它不对外使用,书中的介绍相对简单。基于以往的工作经验,开发了一个名为dataCompare的大数据比较平台来协助验证数据。

(1) 验证数据和数据比较,浪费大量劳动力成本

(2) 如果没有一套标准,验证结果很难评估

(3) 通过界面交互、检查或低代码可以实现自动数据验证和比较 [在此处输入图像描述][1]

2.目的

(1) 通过界面交互、检查或低代码可以实现自动数据验证和比较。

(2) 数据团队的数据比较效率至少提高了约50%。

(3) 一套统一的数据验证方案,满足数据验证和比较的标准规范

3.系统架构设计

4.当前版本实现了以下功能

(1) 低代码简单配置完成数据比对的核心功能

(2) 数据量比较和数据一致性比较

5.后续发展计划

(1) 不一致案件认定 (2) 数据指针检测——枚举值检测、范围检测、数值检测、主键模式检测

6.核心代码在githup中打开

https://github.com/zhugezifang/dataCompare

[在此处输入图像描述][1]

Robert Sinclair 7 年前

根据Haim的回答,这里有一个简化的例子,如果你想比较两个表中存在的值,否则如果一个表中有一行但另一个表没有,它也会返回它。。。。

我花了几个小时才弄清楚。这是一个经过充分测试的简单查询,用于比较“tbl_a”和“tbl_b”

SELECT ID, col
FROM
(
    SELECT
    tbl_a.ID, tbl_a.col FROM tbl_a
    UNION ALL
    SELECT
    tbl_b.ID, tbl_b.col FROM tbl_b
) t
WHERE ID IN (select ID from tbl_a) AND ID IN (select ID from tbl_b)
GROUP BY
ID, col
HAVING COUNT(*) = 1
 ORDER BY ID

因此,您需要添加额外的“where in”子句:

WHERE ID IN(从tbl_a中选择ID)和ID IN(在tbl_b中选择ID

也:

为了便于阅读,如果您想指示表名,可以使用以下内容:

SELECT tbl, ID, col
FROM
(
    SELECT
    tbl_a.ID, tbl_a.col, "name_to_display1" as "tbl" FROM tbl_a
    UNION ALL
    SELECT
    tbl_b.ID, tbl_b.col, "name_to_display2" as "tbl" FROM tbl_b
) t
WHERE ID IN (select ID from tbl_a) AND ID IN (select ID from tbl_b)
GROUP BY
ID, col
HAVING COUNT(*) = 1
 ORDER BY ID

Hardeep Singh 4 年前

你可以使用我自己开发的工具

https://github.com/hardeepvicky/MySql-Schema-Compare

brent groves 4 年前

我尝试了上面的答案,但发现如果一个表有空值,而第二个表在一列中有值,那么上面的交集代码就不会报告这一事实。

select p.pcn,p.period,p.account_no,p.ytd_debit,a.ytd_debit 
-- select count(*) -- 157,283
from Plex.account_period_balance p -- 157,283/202207,148,998
join Azure.account_period_balance a  -- 157,283/202207,148,998
on p.pcn = a.pcn 
and p.period = a.period 
and p.account_no = a.account_no -- 157,283 
where p.period_display = a.period_display -- 157,283 
and p.debit = a.debit  -- 157,283 
-- and p.ytd_debit = a.ytd_debit -- 148,998
-- and p.ytd_debit != a.ytd_debit -- 0