mysql의 두 테이블 간의 차이점 비교

mysql의 두 테이블 간의 차이점 비교

---

Oracle Diff 와 동일 : 두 테이블을 비교하는 방법? mysql을 제외하고.

레이아웃이 동일하지만 다른 데이터를 포함 할 수있는 두 개의 테이블 t1과 t2가 있다고 가정합니다.

이 두 테이블을 비교하는 가장 좋은 방법은 무엇입니까?

좀 더 정확하게 말하자면, t1의 한 행의 데이터가 t2의 해당 행의 데이터와 다른지 알려주는 간단한 SQL 쿼리를 알아 내려고합니다.

교차도 빼기도 사용할 수없는 것 같습니다. 내가 시도 할 때

SELECT * FROM robot intersect SELECT * FROM tbd_robot

오류 코드가 나타납니다.

[오류 코드 : 1064, SQL 상태 : 42000] SQL 구문에 오류가 있습니다. MySQL 서버 버전에 해당하는 설명서에서 1 행의 'SELECT * FROM tbd_robot'근처에서 사용할 올바른 구문을 확인하십시오.

구문 적으로 잘못된 일을하고 있습니까? 그렇지 않은 경우 사용할 수있는 다른 쿼리가 있습니까?

편집 : 또한 무료 버전 DbVisualizer를 통해 쿼리하고 있습니다. 그게 요인인지 확실하지 않습니다.

---

INTERSECT에뮬레이션해야합니다 MySQL.

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

편집 : 단어 주위에`추가 : 설정

---

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)

고유 한 값이없는 경우에도 위의 코드를 확장하여 ID 대신 모든 필드를 확인하고 AND를 사용하여 연결할 수 있습니다 (예 : ID NOT IN (...) AND OTHER_FIELD NOT IN (. ..) 등)

---

이 링크 에서 다른 해결책을 찾았습니다.

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

---

 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을 반환하면 테이블을 비교하고 일치하는 모든 쌍을 찾습니다.

---

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.

<?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!";

        }

    }

?>

---

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....

Took me a couple of hours to figure out. Here's a fully tested simply query for comparing "tbl_a" and "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 ID IN (select ID from tbl_a) AND ID IN (select ID from tbl_b)

---

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

참고URL : https://stackoverflow.com/questions/950414/compare-differences-between-two-tables-in-mysql