Comparing two MySQL tables
From time to time, I need to compare MySQL database tables and see what data has been added to the one in the time since I mysqldump’d the first one.
For example: Let’s say I have table_a as my snapshot’d table, and table_b as my newer table which has one or more new rows in it.The query below will return all records that are in table_b, and not in table_a.
SELECT table_b.* FROM table_b
LEFT JOIN table_a ON table_b.id = table_a.id
WHERE table_a.item_id IS NULLThis idea can be extrapolated to comparing the tables of two different databases:
SELECT database_b.sometable.* FROM database_b.sometable
LEFT JOIN database_a.sometable ON database_b.sometable.id = database_a.sometable.id
WHERE database_a.sometable.item_id IS NULLThe you can take those results and use them to INSERT the missing records, should you want to do this.
