MySQL DDL Trigger, схема таблицы различий для переименования столбцов

Я создаю сценарий PHP для сравнения схемы двух баз данных.

Мне удалось проверить изменения схемы в отношении удаленных / добавленных таблиц, столбцов, индексов, ссылок, но когда дело доходит до переименованных столбцов, я немного застрял.

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

Предпосылки:

  • Я не знаю об изменениях, которые произошли с момента последнего различий.
  • Данные в базах данных не будут совпадать, но схема должна после различий.

Возьмем, к примеру, следующую схему в целевой базе данных.

Field   Type    Null    Key Default Extra
field1  int(11) NO      NULL     
field2  int(11) NO      NULL     
field3  int(11) NO      NULL

А затем предположим следующую схему в исходной базе данных.

Field   Type    Null    Key Default Extra
field1  int(11) NO      NULL     
field4  int(11) NO      NULL     
field3  int(11) NO      NULL

Не зная точно, что произошло, я не могу определить, действительно ли field2 изменился на field4 в порядке DROP, ADD AFTER или же CHANGE COLUMN, Следующие два запроса достигают одного и того же результата с точки зрения структуры таблицы, но данные теряются при использовании первого.

(1)    ALTER TABLE `demo` DROP `field2`
       ALTER TABLE `demo` ADD `field4` INT( 11 ) NOT NULL AFTER `field1` 

(2)    ALTER TABLE `demo` CHANGE `field2` `field4` INT( 11 ) NOT NULL

Я, очевидно, могу удалить старое имя столбца и создать новое, но тогда все исходные данные будут потеряны. Мне нужно использовать ALTER TABLE table CHANGE COLUMN field new_name structure; запрос, а не DROP column FROM table с последующим ALTER TABLE table ADD column definition;

Я надеялся, что смогу использовать триггер DDL для отслеживания изменений в схеме и вставки записи таких изменений в таблицу в исходной базе данных. Позже я мог бы запросить эту таблицу, чтобы определить, как появился определенный столбец. Однако, насколько я могу судить, невозможно запускать триггеры для запросов DDL в MySQL, что исключает возможность регистрации этих изменений. Я читал этот рабочий журнал ( WL # 2418: триггеры DDL) в MySQL Forge (сейчас он находится в зоне разработки MySQL), но, к сожалению, его реализация еще не завершена.

Есть ли способ, которым я могу обновить таблицы, чтобы соответствовать схеме в отношении переименованных столбцов без потери данных?

Я смотрел на такие вещи, как MySQL Diff, но он должен быть встроен в существующий фрагмент кода, поэтому мне придется создавать его самому.

Идеи, которые я рассмотрел

  • Добавьте комментарий к каждому столбцу, который является уникальным числом или строкой (для аргумента назовите его хешем). Запросите таблицу information_schema, чтобы получить это значение и сравнить его в каждом столбце. Если он уникален, то это новый столбец, или если он соответствует хешу, но не имени или структуре, он переименовывается / реконфигурируется.

  • Сравните схему, если есть новый столбец - проверьте его положение относительно соседних столбцов. Если имя нового столбца находится в той же позиции, что и отсутствующее, сравните структуру этого столбца. Если оно совпадает, считайте его переименованным. Если нет, считайте, что он удален, а затем добавлен.

Источник

5 ответов

Нет способа обнаружить это, но если вам нужно это для вашей локальной машины, то есть способ сделать это.

  1. Включить общую запись в Mysql table
  2. Теперь все запросы будут сохранены в mysql.general_log Таблица.
  3. Теперь вы можете выполнить запрос как SELECT * FROM mysql.general_log WHERE argument LIKE 'ALTER%' чтобы дать вам все операторы SQL, которые относятся к модификации базы данных, включая переименования столбцов.

Надеюсь, это поможет..

Источник

Я не думаю, что вы должны синхронизировать схему таким образом. Вы должны проверять сценарии миграции в исходном коде каждый раз, когда вы что-либо меняете, а затем иметь таблицу в своей базе данных, в которой регистрируется, какая миграция была применена. Таким образом, вы можете убедиться, что все миграции были применены ко всем базам данных. например

CREATE TABLE `ddl_version` (
version` varchar(32)  NOT NULL,
`applied_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY `version` (`version`)

)`
Источник

MySQL не поддерживает триггеры DDL. Я вижу в их рабочем журнале, что они запланировали это для сервера 7.1.

Идентификатор задачи - WL#2418.

Источник 
ALTER TABLE table
 CHANGE COLUMN old_col_name
 new_col_name column_definition;

Это изменяет имя столбца без потери данных в столбце (если только вы не изменили тип данных, а новый тип данных меньше старого типа данных, например, изменив BIGINT на SMALLINT).

К сожалению, эта команда требует, чтобы вы повторили текущее определение столбца, например, тип данных, NOT NULL и другие параметры. В MySQL нет команды просто переименовать столбец.

Предложение CHANGE COLUMN является расширением MySQL для стандартного SQL, в котором нет синтаксиса для переименования столбца на месте.

Другое решение, если вы хотите придерживаться стандартного SQL, - это добавить новый столбец, скопировать данные из старого столбца в новый столбец и затем удалить старый столбец:

ALTER TABLE table ADD COLUMN new_col_name column_definition;

UPDATE table SET new_col_name = old_col_name;

ALTER TABLE table DROP COLUMN old_col_name;
Источник

Самая большая проблема здесь заключается в том, что если для одной и той же таблицы было изменено более одного имени столбца (одного типа), НЕТ способа определить, какой из столбцов был изменен на какой. (Тем не менее, существует возможность фактически сравнить данные и надеяться, что некоторые данные одинаковы в обеих таблицах, и таким образом можно будет определить, какой столбец изменился на что, но это будет совершенно ненадежно)

Кроме того, есть проблема изменения 1 (или более) поля и добавления 1 (или более) поля, и вы снова столкнетесь с той же проблемой, что и выше.

MySQLDiff, упомянутый выше, сможет сказать вам, какие поля являются новыми, а какие старыми (и больше похожи на новые таблицы и т. Д.), Но он не сможет сказать вам, какое имя столбца было изменено на какое, и даже не пытается к. (Ни одно программное обеспечение не может надежно сделать это, особенно если ваши данные не совпадают между базами данных). Вы можете увидеть, что он делает в: mysql-1.5.0/library/database.lib.php и mysql-1.5.0/library/generator.lib.php

Однако я бы настоятельно рекомендовал представить очень простой процесс в вашей команде разработчиков. - Каждый раз, когда вносятся изменения в структуру БД (или любые записи, которые необходимы для работы системы), должны быть записаны, например, в. Файл db_changes.sql, который находится под контролем версий. Для каждой новой ревизии, если такие изменения в db необходимы, тогда файл db_changes.sql стирается, изменения записываются и сохраняются как новая ревизия. Таким образом, изменения в структуре базы данных корректируются должным образом, и применение их - это всего лишь вопрос различий между двумя точками пересмотра. (MysqlWorkbench - один из клиентов, который, если вы измените таблицы, покажет вам запрос, который он будет выполнять внутри, если вашим разработчикам вообще понадобится клиент)

Источник
Другие вопросы по тегам