mysql - How to verify if two tables have exactly the same data? -

Basically we have a table ( original table ) and it is backed up in another table (<<>

backup table ); Thus there are two tables with the same schema.

In the beginning, both tables are in the same set of data ( original table and backup table ) for some reasons, to verify this sometime after Whether the dataset has changed in the original table

To do this I have to compare the dataset against the original table backup table .

Let's assume that the following schema in original table is:

  LemmasMapping to create tables (lemma1 int, lemma2 int, btree (lemma1) Using the index ix_lemma1, index by using btree (lemma2) ix_lemma2)  

How can I get a dataset comparison?

Update : The table does not have a primary key, it stores mapping only between two IDs

I will write three questions

1. Join in choosing one of the internal rows where the primary key is present in both tables, but one or more The value of the other columns is different. It will raise the rows changed in the original.

2. External participants left to take the rows in the original table, but not in the backup table (i.e. the root in the original in the line that does not exist in the backup). The lines inserted in the original will be returned.

3. Join the right external to pick up the rows in the backup, which is no longer present in the original. This will return the rows that have been removed from the original.

You can add three questions together to return the same result set. If you do this, then you have to add a column to let it know what type of line it is (updated, inserted, or deleted).

With some attempts, be careful with a full external insertion external john in a query in a query, because they behave differently in different SQL engines. Being involved can be predicted that instead of joining the pen, occasionally join an external as well as interconnect.