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

问题描述：

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?

我有一个名为clients code>的表，在该表中有两列重要性; id code>和 client code>。我在同一个数据库中有一个名为 calendar code>的辅助表。我真的希望 calendar code>表中的两列 id code>和 client code>与 client code>表中的列同步。 p>

现在我正在使用这个PHP在MySQL中执行它： p>


  INSERT IGNORE INTO calendar（id，client）SELECT id  ，客户端FROM客户端; 
  code>  pre> 
 
 有没有更好的方法来完成此任务？ 也许MySQL中的一个内置函数我忽略了或类似的东西？ p> 
  div>

答

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

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

相关推荐