Синхронизация БД с помощью триггеров (trigger MySQL)

Декабрь3

На одном из проектов, над которым я работаю по-ночам )), стала необходимость в перестройке БД (она была денормализована), приведение её к нормальным формам, а также переписыванию скриптов. Останавливать работающий проект низя — он коммерческий.

Как же быть? Точнее — необходимо было держать новую БД актуальной, чтобы когда, все будет готово и оттестировано просто сделать переключение на новую БД и радоваться своей работе.

Я принял такое решение, создать копию БД продакшена, поработать с ней, привести к нормализованному виду и потом синхронизировать. Так я и сделал.

Теперь пришло время подумать как же я буду синхронизировать БД старую и новую? Ведь хочется, что бы при добавлении/редактировании/удалении контента в старой БД, такие же действия происходили с ним в новой БД.

На помощь пришли триггеры MySQL, краткий экскурс что это такое я дал на странице своего блог Работа trigger (триггерами) в MySQL.

И так, есть таблица новостей, в ней есть поля:

mysql> DESCRIBE lib_news;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int(11)      | NO   | PRI | NULL    | auto_increment |
| dt            | int(11)      | NO   |     | 0       |                |
| title         | varchar(250) | NO   |     |         |                |
| descr         | text         | NO   |     | NULL    |                |
| text          | text         | NO   |     | NULL    |                |
| source        | varchar(250) | NO   |     |         |                |
| source_url    | varchar(250) | NO   |     |         |                |
| keywords      | text         | NO   |     | NULL    |                |
| img1          | varchar(20)  | NO   |     |         |                |
| img2          | varchar(20)  | NO   |     |         |                |
| img3          | varchar(20)  | NO   |     |         |                |
| img4          | varchar(20)  | NO   |     |         |                |
| img5          | varchar(20)  | NO   |     |         |                |
| first         | tinyint(1)   | NO   |     | 0       |                |
| fl            | tinyint(1)   | NO   | MUL | 0       |                |
| kol           | int(11)      | NO   |     | 0       |                |
| f_yandex      | tinyint(1)   | NO   |     | 0       |                |
| f_lenta       | tinyint(1)   | YES  |     | 0       |                |
| f_lenta_email | tinyint(1)   | YES  |     | 0       |                |
| comp_tp       | int(11)      | YES  |     | 0       |                |
+---------------+--------------+------+-----+---------+----------------+
20 rows in set (0.02 sec)

А вот поля из аналогичной таблицы в новой БД, разница в 2-х полях.

поле дата `dt` в старой это INT(11), в новой TIMESTAMP;
поле ИД компании comp_tp INT(11), в новой id_comp_tp SMALLINT(4);

mysql> DESCRIBE urbanus_lib_news;
+---------------+--------------+------+-----+-------------------+----------------+
| Field         | Type         | Null | Key | Default           | Extra          |
+---------------+--------------+------+-----+-------------------+----------------+
| id            | int(11)      | NO   | PRI | NULL              | auto_increment |
| id_comp_tp    | smallint(4)  | NO   |     | 0                 |                |
| dt            | timestamp    | NO   |     | CURRENT_TIMESTAMP |                |
| title         | varchar(250) | YES  |     | NULL              |                |
| descr         | text         | YES  |     | NULL              |                |
| text          | text         | YES  |     | NULL              |                |
| source        | varchar(250) | YES  |     | NULL              |                |
| source_url    | varchar(250) | YES  |     | NULL              |                |
| keywords      | text         | YES  |     | NULL              |                |
| img1          | varchar(20)  | YES  |     | NULL              |                |
| img2          | varchar(20)  | YES  |     | NULL              |                |
| img3          | varchar(20)  | YES  |     | NULL              |                |
| img4          | varchar(20)  | YES  |     | NULL              |                |
| img5          | varchar(20)  | YES  |     | NULL              |                |
| first         | tinyint(1)   | NO   |     | 0                 |                |
| fl            | tinyint(1)   | NO   | MUL | 0                 |                |
| kol           | int(11)      | NO   |     | 0                 |                |
| f_yandex      | tinyint(1)   | NO   |     | 0                 |                |
| f_lenta       | tinyint(1)   | NO   |     | 0                 |                |
| f_lenta_email | tinyint(1)   | NO   |     | 0                 |                |
+---------------+--------------+------+-----+-------------------+----------------+
20 rows in set (0.02 sec)

Получается, что при синхронизации таблиц, необходимо еще и приводить поля к нужному виду.

Ниже привожу триггеры которые и делают синхронизацию.

DROP TRIGGER IF EXISTS `lib_news_ai`;;
CREATE TRIGGER `lib_news_ai` AFTER INSERT ON `lib_news`
FOR EACH ROW
BEGIN
	INSERT INTO `newdb`.`new_lib_news` SET
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`
	ON DUPLICATE KEY UPDATE
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`;
END;;

DROP TRIGGER IF EXISTS `lib_news_au`;;
CREATE TRIGGER `lib_news_au` AFTER UPDATE ON `lib_news`
FOR EACH ROW
BEGIN
	INSERT INTO `newbd`.`new_lib_news` SET
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`
	ON DUPLICATE KEY UPDATE
		`id` = NEW.id, `dt` = FROM_UNIXTIME(NEW.`dt`), `id_comp_tp` = NEW.`comp_tp`, `title` = NEW.`title`,
		`descr` = NEW.`descr`, `text` = NEW.`text`, `source` = NEW.`source`, `source_url` = NEW.`source_url`,
		`keywords` = NEW.`keywords`, `img1` = NEW.`img1`, `img2` = NEW.`img2`, `img3` = NEW.`img3`,
		`img4` = NEW.`img4`, `img5` = NEW.`img5`, `first` = NEW.`first`, `fl` = NEW.`fl`, `kol` = NEW.`kol`,
		`f_yandex` = NEW.`f_yandex`, `f_lenta` = NEW.`f_lenta`, `f_lenta_email` = NEW.`f_lenta_email`;
END;;

DROP TRIGGER IF EXISTS `lib_news_ad`;;
CREATE TRIGGER `lib_news_ad` AFTER DELETE ON `lib_news`
FOR EACH ROW
BEGIN
	DELETE FROM `newbd`.`new_lib_news` WHERE `id` = OLD.id;
END;;

Как видно из св-в триггеров, любая новая запись в старой БД таблицы новостей будет записана в новую БД с изменениями которые необходимы. При редактировании также происходит обновление записей в 2-х БД. При удалении — удаление, соответственно.

Может возникнуть вопрос, почему я не использую простые запросы INSERT INTO или UPDATE. Ответ прост, вдруг по каким-то загадочным причинам в новой БД будет существовать запись с таким же ИД, который будет добавляться со старой БД, возникнет ошибка и ничего не будет, а используя конструкцию INSERT INTO ….. ON DUPLICATE KEY UPDATE (можно об этом почитать тут) я избегаю ошибки, даже если запись с таким же ИД есть, она будет обновлена. Или наоборот записи в новой БД нет, а в старой сделали исправление записи, следовательно эта запись добавится в новую БД.

Еще может возникнуть вопрос, а как же те записи, которые были добавлены в старую БД в момент реконструкции новой? Ну этот вопрос я не буду рассматривать в данной статье, об этом я еще не думал, но скорее всего этим займутся или EVENT или PROCEDURE.

Всем удачи.