MySQL 比较mysql中两个表之间的差异

compare differences between two tables in mysql

提问by echoblaze

Same as oracle diff: how to compare two tables?except in mysql.

与oracle diff相同：如何比较两个表？除了在 mysql 中。

Suppose I have two tables, t1 and t2 which are identical in layout but which may contain different data.

What's the best way to diff these two tables?

假设我有两个表 t1 和 t2，它们的布局相同，但可能包含不同的数据。

区分这两个表的最佳方法是什么？

To be more precise, I'm trying to figure out a simple SQL query that tells me if data from one row in t1 is different from the data from the corresponding row in t2

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

It appears I cannot use the intersect nor minus. When I try

看来我不能使用相交或减号。当我尝试

SELECT * FROM robot intersect SELECT * FROM tbd_robot

I get an error code:

我收到一个错误代码：

[Error Code: 1064, SQL State: 42000] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROM tbd_robot' at line 1

[错误代码：1064，SQL 状态：42000] 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解要在第 1 行的“SELECT * FROM tbd_robot”附近使用的正确语法

Am I doing something syntactically wrong? If not, is there another query I can use?

我在语法上做错了吗？如果没有，我可以使用另一个查询吗？

Edit: Also, I'm querying through a free version DbVisualizer. Not sure if that might be a factor.

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

回答by Quassnoi

INTERSECTneeds to be emulated in MySQL:

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
        )

回答by Roee Adler

You can construct the intersection manually using UNION. It's easy if you have some unique field in both tables, e.g. ID:

您可以使用 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)

If you don't have a unique value, you can still expand the above code to check for all fields instead of just the ID, and use AND to connect them (e.g. ID NOT IN(...) AND OTHER_FIELD NOT IN(...) etc)

如果您没有唯一值，您仍然可以扩展上述代码以检查所有字段而不仅仅是 ID，并使用 AND 连接它们（例如 ID NOT IN(...) AND OTHER_FIELD NOT IN(. ..） 等等）

回答by Haim Evgi

I found another solution in this 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

回答by yasir kk

 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

try this. This will compare your table and find all matching pairs, if any mismatch return NULL on left.

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

回答by Bogdan Draganica

Based on Haim's answer I created a PHP code to test and display all the differences between two databases. This will also display if a table is present in source or test databases. You have to change with your details the <> variables content.

根据 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!";

        }

    }

?>

回答by Robert Sinclair

Based on Haim's answer here's a simplified example if you're looking to compare values that exist in BOTH tables, otherwise if there's a row in one table but not the other it will also return it....

如果您希望比较两个表中存在的值，则根据 Haim 的回答，这里有一个简化示例，否则如果一个表中有一行但另一个表中没有，它也会返回它....

Took me a couple of hours to figure out. Here's a fully tested simply query for comparing "tbl_a" and "tbl_b"

我花了几个小时才弄清楚。这是一个经过全面测试的简单查询，用于比较“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

So you need to add the extra "where in" clause:

所以你需要添加额外的“where in”子句：

WHERE ID IN (select ID from tbl_a) AND ID IN (select ID from tbl_b)

WHERE ID IN（从 tbl_a 中选择 ID）和 ID IN（从 tbl_b 中选择 ID）

---

---

Also:

还：

For ease of reading if you want to indicate the table names you can use the following:

为了便于阅读，如果您想指明表名，可以使用以下内容：

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