Compare differences between two tables in mysql

Question

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

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

What is the best way to distinguish between these two tables?

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

I can't seem to use intersection or minus. When i try

SELECT * FROM robot intersect SELECT * FROM tbd_robot 

I get the error code:

[Error code: 1064, SQL state: 42000] You have an error in your SQL syntax; check the manual that matches the version of your MySQL server. for the correct syntax to use next to 'SELECT * FROM tbd_robot' on line 1

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

Edit: In addition, I am viewing the free version of DbVisualizer. Not sure if this could be a factor.

Answer 1

INTERSECT must be emulated 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: Added `around words: set

Answer 2

You can build the intersection manually using UNION. This is easy if you have a unique field in both tables, for example. 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 do not have a unique value, you can still extend the above code to check all fields, not just the identifier, and use AND to connect them (for example, ID NOT IN (...) AND OTHER_FIELD NOT IN (.. .) etc.)

Answer 3

I found another solution in this

 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 

Answer 4

  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 it. This will compare your table and find all matching pairs if any mismatch returns NULL on the left.

Answer 5

Based on Haim's answer, I created PHP code to test and display all the differences between the two databases. It will also be displayed if the table is present in the source or test databases. You must change the contents of <> variables with your data.

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