同步MySQL数据库中不同表的两行

Question

I have a table named clients, in that table there's two columns of importance; id and client. I have a secondary table in the same database named calendar. I really want the two columns of id and client in the calendar table to sync with the ones in client table.

Right now I am using this PHP to execute this in MySQL:

INSERT IGNORE INTO calendar (id, client) SELECT id, client FROM clients;

Is there a better way of accomplish this task? Maybe a built in function in MySQL that I have overlooked or something like that?

Answer 1

Use Triggers : The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table.

The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE and it can be invoked before or after the event.

You can make trigger when you insert or update a row in main table and make the changes in another table

Example:

DELIMITER $$

CREATE TRIGGER my_sync_trigger 
AFTER INSERT ON `clients` for each row
begin
INSERT INTO calender (id,client)
Values (new.id, new.client);
END$$

DELIMITER ;

"new" stands for the new value inserted into clients table. The same value will be inserted into id and client column in calender.

Note: single quotes are removed from table name because quotes effectively make it a string literal instead of a proper identifier. DELIMITER command will change the ending of each statement from ";" to "$$" so that MySQL is not confused with ";" inside and outside the trigger

Make similar triggers for update and delete also

Simple guide for examples and syntax: http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx