All geek questions in one place

Sync two mysql db tables

So, I have two mysql db tables: "table_original" and "table_copy"

Here is my current setup:

Every night it is table_originalupdated from another server with information. It either removes the old db line, or adds a new line with a unique post_id (there will not be two identical post_id, even if the line is deleted).

Then, certain information along with post_id is copied to table_copy. So the two are connected using post_id.

So far it table_originalhas ~ 20,000 lines, and table_copy~ 40,000.

I just noticed that it was table_copynot updated from the first table, but only added rows instead of deleting everything that was deleted from the first table.

To synchronize them, my initial approach was to check each line from table_originalto table_copy, and then if post_id exists, then do nothing, if not, delete the line from table_copy.

My concern is that it table_originalwill be updated every night: either the old post_id line will be deleted, or a new line will be added. Unfortunately, I have no way of knowing what is being done before this is done. Sense, I need to wait until db is updated to see what has been removed and added.

Then, every time the first table is updated, I have to check each row on the second table to update them. The table will only be larger, and I am concerned that this approach may not be the best.

What are you guys asking me to do?

Thank!

+4
1

table_copy, table_original:

DELETE t1.* FROM table_copy AS t1 
LEFT OUTER JOIN table_original AS t2 USING (post_id)
WHERE t2.post_id IS NULL;

, , .

http://dev.mysql.com/doc/refman/5.7/en/delete.html DELETE .

, :

CREATE TRIGGER copy_on_ins AFTER INSERT ON table_original
FOR EACH ROW
  INSERT INTO table_copy SET post_id = NEW.post_id, other_columns = NEW.other_column;

CREATE TRIGGER copy_on_upd AFTER UPDATE ON table_original
FOR EACH ROW
  UPDATE table_copy SET other_column = NEW.other_column
  WHERE post_id = NEW.post_id;

CREATE TRIGGER copy_on_del AFTER DELETE ON table_original
FOR EACH ROW
  DELETE FROM table_copy WHERE post_id = OLD.post_id;

. http://dev.mysql.com/doc/refman/5.7/en/create-trigger.html

:

, post_ids , . , :

+3

Source: https://habr.com/ru/post/1656992/

More articles:


All Articles