Chris Laplante Chris Laplante -4 years ago 107
MySQL Question

How can I find identical tables in MySQL and PHP?

Is there an easy way to find identical tables, i.e. same structure and data, using MySQL and PHP? I doubt there is a MySQL command for it, but how would you recommend doing this?

Answer Source

Without more information about the structure or size of your data, you could do this:

SELECT IF(COUNT(*) = (SELECT COUNT(*) FROM table1) AND COUNT(*) = (SELECT COUNT(*) FROM table2),1,0) AS result FROM
(
    SELECT * FROM table1
    UNION
    SELECT * FROM table2
) tables_joined

But be warned, it's not an ideal solution. For large datasets, it could take a while

This is untested, but the general premise of the answer is:

  • Join the two tables. If the structure is consistent (at a basic level - this does not include data types, indexes etc) then you won't get any errors
  • After the join, compare the number of rows to the number of rows in one of the tables
  • If the number of rows are equal, then the data is identical (because otherwise the join would have returned two rows).
  • Output 1 if identical or 0 if not.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download