За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

Синхронизация БД с помощью триггеров (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.

Всем удачи.

`newdb`.`new_lib_news`

Статья просмотренна 53747 раз, зашло посетителей 17248

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Декабрь 2009
Пн Вт Ср Чт Пт Сб Вс
« Ноя   Янв »
 123456
78910111213
14151617181920
21222324252627
28293031  

Сейчас на сайте