compare differences between two tables in mysql

Question

Same as oracle diff  how to compare two tables  except in 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   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 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  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

User · Answer

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

User · Answer

You can construct the intersection manually using UNION  It s easy if you have some unique field in both tables  e g  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

User · Answer

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

User · Answer

Problem below  is to compare table before and after i do big update   If you use Linux  you can use commands as follow  In terminal  mysqldump -hlocalhost -uroot -p schema name here table name here  gt   home ubuntu database dumps dump table before running update sql  mysqldump -hlocalhost -uroot -p schema name here table name here  gt   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  gt   home ubuntu database dumps diff txt  You will need online tools for  Formatting SQL exported from the dumps   e g http   www dpriver com pp sqlformat htm  Not the best I ve seen   We have diff txt  you have to take manually the   - showing inside  which is 1 line of insert statements  that has the values   Do diff online for the 2 lines -  amp    in diff txt  past them in online diff tool   e g https   www diffchecker com  you can save and share it  and has no limit on file size   Note  be extra careful if its sensitive production data

User · Answer

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

User · Answer

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  lt   variables content     lt  php       User     lt DatabaseUser gt         Pass     lt DatabasePassword gt         SourceDB     lt SourceDatabase gt         TestDB     lt DatabaseToTest gt          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         lt table border  1  width  100   gt       lt tr gt           lt td gt Table lt  td gt           lt td gt Found in  SourceDB     count   SourceDB Tables       lt  td gt           lt td gt Found in  TestDB     count   TestDB Tables       lt  td gt           lt td gt Test result lt  td gt       lt tr gt               foreach   LookupTables as  table               FoundInSourceDB   in array   table   SourceDB Tables     1   0           FoundInTestDB   in array   table   TestDB Tables     1   0          echo         lt tr gt           lt td gt  table lt  td gt           lt td gt  lt input type  checkbox       FoundInSourceDB    1    checked           gt  lt  td gt            lt td gt  lt input type  checkbox       FoundInTestDB    1    checked           gt  lt  td gt              lt td gt    compareTables   SourceDB   TestDB   table      lt  td gt         lt  tr gt                            echo         lt  table gt       lt br gt  lt br gt      No of discrepancies found   NoOfDiscrepancies              function query   link   q               result   mysqli query   link   q              errors   mysqli error  link           if    errors  gt                      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    amp  amp  count   result    gt  0                   NoOfDiscrepancies                 data     lt table gt  lt tr gt  lt td gt column name lt  td gt  lt td gt ordinal position lt  td gt  lt td gt data type lt  td gt  lt td gt column type lt  td gt  lt  tr gt                 foreach   result as  item                       data      lt tr gt  lt td gt     item  column name      lt  td gt  lt td gt     item  ordinal position      lt  td gt  lt td gt     item  data type      lt  td gt  lt td gt     item  column type      lt  td gt  lt  tr gt                                 data      lt  table gt                 return  data                     else                return  Checked but no discrepancies found                         gt

User · Answer

INTERSECT needs to be emulated in 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

[sql] compare differences between two tables in mysql

Examples related to sql

Examples related to mysql