Summary of data synchronization methods for two tables in MySQL

There are two tables A and B. You must insert A record into table A and A record into Table B at the same time, insert A record to B and insert A record to. The two tables have different structures and need to correspond several fields. You can use the following trigger.

Trigger for table:

Begin

Set @ disable = 1;

If @ disable = 1 and not exists (SELECT 1 FROM tableB where ID = new. ID) then

Insert into tableB (ID, corresponding to field 1) values (new. ID, new. Corresponding to field 1 );

End if;

Set @ disable = 0;

End

Table B triggers:

Begin

Set @ disable = 1;

If @ disable = 1 and not exists (SELECT 1 FROM tableA where ID = new. ID) then

Insert into tableA (ID, corresponding to field 1) values (new. ID, new. Corresponding to field 1 );

End if;

Set @ disable = 0;

End

Mysql uses a trigger to synchronize two tables

Currently, the local test is successful.

Assume that two local databases a and B have table1 (id, val) and table2 (id, val) in Table)
Assume that you want to synchronize data updates in table 1 and Table 2.
Code:

DELIMITER $
CREATE
/* [DEFINER = {user | CURRENT_USER}] */
TRIGGER 'A'. 'Trigger name' BEFORE UPDATE
ON 'A'. 'Table1'
FOR EACH ROW BEGIN
IF new. id! = Old. id THEN
UPDATE 'B'. 'Table2' SET 'B'. 'Table2'. id = new. id WHERE 'B'. 'Table2'. val = old. val;
End if;
END $
DELIMITER;

I found a lot of code on the Internet, and it failed to run in phpadmin. It was always a syntax error and phpmyadmin could not create a trigger visually. So I found another mysql management tool, SQLyog. This tool is good. For java writing, you can create a trigger on the interface, and then copy the code to phpmyadmin to run it successfully!

Create add, delete, and update triggers respectively to synchronize data between two tables.

1: added data synchronization:
 
If there are two tables, table A and Table B, create A trigger to enable Table B to insert data synchronously after table A is inserted. The inserted data fields in Table B must correspond to those in Table.

Create trigger name
On a table
AFTER INSERT
AS BEGIN INSERT
Table B (Table B field 1, Table B field 2, Table B field 3)
SELECT table A field 1, table A field 2, table A field 3
FROM INSERTED
END

2. Delete data synchronization:

If there are two tables, table A and Table B, create A trigger so that Table B also deletes data after table A is deleted. Table B and table A should have corresponding primary keys.

Create trigger name
On a table
AFTER DELETE
Table AS BEGIN DELETE B
WHERE
Table B primary key IN (
SELECT table A primary key
From deleted)
END

3. Synchronous data update:
 
If there are two tables, table A and Table B, create A trigger so that after table A data is updated, Table B also updates data synchronously.

Create trigger name
On a table
AFTER UPDATE
AS
Update Table B
SET
B. B table Field 1 = A. A table Field 1
FROM
Table B as B, INSERTED AS
Where B. B Table primary key = A. A table primary key

Understand the two temporary tables in the trigger: Deleted and Inserted. Note: Deleted and Inserted indicate the "old record" and "new record" tables that trigger the event respectively"