Vergleichen Sie Unterschiede zwischen zwei Tabellen in MySQL

INTERSECT muss emuliert werden in 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
        )

Edit: Hinzugefügt, um die Wörter: setzen

InformationsquelleAutor der Antwort Quassnoi

Fand ich eine andere Lösung in diesem 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

InformationsquelleAutor der Antwort Haim Evgi

Basierend auf Haim Antwort, die ich erstellt einen PHP-code zum testen und anzeigen der Unterschiede zwischen zwei Datenbanken.
Dies wird auch angezeigt, wenn eine Tabelle vorhanden ist, in Quell-oder test-Datenbanken.
Sie müssen ändern Sie es mit Ihren details die < > - Variablen Inhalt.

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

        }

    }

?>

InformationsquelleAutor der Antwort Bogdan Draganica