Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve multi-database data synchronization in MySQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First, establish a test environment

CREATE table mother (id int (6) primary key auto_increment,value text)

CREATE table son LIKE mother

We have established a mother table and an identical son table. We are going to build triggers on the mother table. Adding, deleting and modifying the mother table should be synchronized to the son table.

II. Trigger prototype

The data set before and after the change of MySQL is called OLD and NEW,INSERT, respectively. There is no OLD, only NEW,DELETE, only OLD and no NEW,UPDATE.

Synchronous insert trigger

DELIMITER |

CREATE TRIGGER tg_sync_insert

AFTER INSERT

ON mother

FOR EACH ROW

BEGIN

INSERT INTO son SELECT * FROM mother WHERE id > LAST_INSERT_ID ()

END

| |

DELIMITER

LAST_INSERT_ID () gets the id value after the last insert table, so the mother table must have an auto_increment field. If multiple records are inserted at the same time, it needs to be greater than LAST_INSERT_ID ().

Synchronous delete trigger

DELIMITER |

CREATE TRIGGER tg_sync_delete

AFTER DELETE

ON mother

FOR EACH ROW

BEGIN

DELETE FROM son WHERE son.id=OLD.id

END

| |

DELIMITER

Synchronous update trigger

DELIMITER |

CREATE TRIGGER tg_sync_update

AFTER UPDATE

ON mother

FOR EACH ROW

BEGIN

DELETE FROM son WHERE son.id=OLD.id

INSERT INTO son SELECT * FROM mother WHERE id = OLD.id

END

| |

DELIMITER

III. Testing

insert

Mysql > insert into mother (value) values ('first')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from son

+-+

| | id | value |

+-+

| | 6 | first |

+-+

1 row in set (0.00 sec)

Mysql > insert into mother (value) values ('second'), (' third')

Query OK, 2 rows affected (0.01sec)

Records: 2 Duplicates: 0 Warnings: 0

Mysql > select * from mother

+-+

| | id | value |

+-+

| | 7 | second |

| | 6 | first |

| | 8 | third |

+-+

3 rows in set (0.00 sec)

Mysql > select * from son

+-+

| | id | value |

+-+

| | 7 | second |

| | 6 | first |

| | 8 | third |

+-+

3 rows in set (0.00 sec)

Mysql >

Update

Mysql > update mother set value='updated' where id='8'

Query OK, 1 row affected (0.01sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from mother

+-+

| | id | value |

+-+

| | 7 | second |

| | 6 | first |

| | 8 | updated |

+-+

3 rows in set (0.00 sec)

Mysql > select * from son

+-+

| | id | value |

+-+

| | 7 | second |

| | 6 | first |

| | 8 | updated |

+-+

3 rows in set (0.00 sec)

Mysql >

Delete

Mysql > delete from mother where id='8'

Query OK, 1 row affected (0.00 sec)

Mysql > select * from son

+-+

| | id | value |

+-+

| | 7 | second |

| | 6 | first |

+-+

2 rows in set (0.00 sec)

Mysql > delete from mother where id in (7 and 6)

Query OK, 2 rows affected (0.01sec)

Mysql > select * from son

Empty set (0.01sec)

Mysql >

The above is how to achieve multi-database data synchronization in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.