How can I find identical tables in MySQL and PHP?

Question

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 1

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.

Answer 2

Identical tables MUST have identical ids, so you just need to check if there is any difference between ids in both tables, so the following code should be helpful:

Select count(*) from table1 where table1_id IN (select table2_id from table2); 

if result is 0, then there is difference.

The better way is seeing and checking the table structure, like (imagine we got 2 tables named orod1 and orod2):

select table_name,column_name,data_type 
from information_schema.columns where table_schema='orod_schema' and table_name ='orod%';