Sequence, как объект базы данных, отсутствует в MySQL, но тем не менее, его можно организовать на триггерах. Существуют разные способы организации уникального ключа для нескольких таблиц (на уровне приложения, процедура). В этой статье рассказывается, как организовать последовательность +1 с использованием “before insert” триггеров.
Что есть Sequence?
Напомним, что такое Sequence (в Oracle). Последовательность CREATE SEQUENCE – это объект базы данных, который генерирует целые числа в соответствии с правилами, установленными во время его создания.
Задача
Требуется обеспечить уникальным значениями первичного ключа несколько таблиц (или все таблицы) в базе данных MySQL).
Решение
Будем использовать вспомогательную таблицу Sequence, в которой будет генерироваться последовательность, поле Name опционально — для имени таблицы (без префикса):
Gdn::Structure() ->Table('Sequence') ->Engine('InnoDB') ->PrimaryKey('SequenceID', 'int') ->Column('Name', 'char(20)') ->Set($Explicit, $Drop);
Для создания триггера в MySQL у пользователя выполняющего запрос должны быть права TRIGGER, до версии 5.1.6 нужны привелегии SUPER. Триггеры будут создаваться динамически при событии типа schema-update, чтобы поддерживать код в актуальном состоянии.
Нам понадобятся названия таблиц и их первичные ключи (ключ: имя таблицы, значение: имя ключа, как правило это “имя таблицы” + “ID”):
$SequenceTables = Model::GetSequenceTables();
Для случая, когда мы обновляем таблицы (а не создаем их) понадобиться максимальное значение первичного ключа из покрываемых sequence-ом таблиц:
$NextAutoIncrement = MaxAutoIncrement(array_keys($SequenceTables)); $Construct->Query("alter table {$DatabasePrefix}Sequence auto_increment = $NextAutoIncrement");
Код функции MaxAutoIncrement() не приводится, остается в качестве упражнения, можно сделать несколькими способами.
Шаблон кода тела триггера, для каждой из таблиц, где:
%1$s — префикс таблиц
%2$s — имя таблицы
%3$s — имя первичного ключа таблицы
$TriggerCodeFormat = 'begin if new.%3$s then begin replace into %1$sSequence values(new.%3$s, "%2$s"); end; else begin insert into %1$sSequence values(null, "%2$s"); set new.%3$s = last_insert_id(); end; end if; end';
Триггер будет срабатывает до вставки строки в таблицу и проверять значение первичного ключа, если это null, то происходит запись в таблицу Sequence и получение значение вставленной строки (возвращается функцией last_insert_id()), и это значение заменяет null первичного ключа во вставляемой строке.
Далее запускается цикл с именами таблиц и создается триггер с имененем «Sequence» + «имя_таблицы» применив шаблон, предварительно получив все триггеры запросом show triggers и проверкой на, возможно, ранее уже созданный точно такой же триггер для этой таблицы:
$CreateTriggerFormat = 'create trigger Sequence%2$s before insert on %2$s for each row ' . $TriggerCodeFormat; foreach ($SequenceTables as $Table => $PrimaryKey) { $TriggerName = "Sequence{$Table}"; $Statement = GetValueR("$TriggerName.Statement", $DatabaseTriggers); $TriggerCode = sprintf($TriggerCodeFormat, $Px, $Table, $PrimaryKey); if ($TriggerCode != $Statement) { $CreateTriggerSql = sprintf($CreateTriggerFormat, $Px, $Table, $PrimaryKey); try { $Construct->Query("drop trigger if exists $TriggerName"); $Construct->Query($CreateTriggerSql); } catch (Exception $Exception) { if (Debug()) throw $Exception; elseif ($Exception->GetCode() != 256) throw $Exception; } } }
Ограничения
- На данный момент (MySQL 5.5) нельзя создать более одного триггера для одной таблицы, с теми же самыми действиями и событиями.
- Не реализовано удаление записей из таблицы Sequence.
- Update первичного ключа, при выполнении запроса вида:
update t1 set id = 1 where id = 2;
не сработает триггер и соответственно запись в таблице Sequence останется неверной.
- Однозначная идентификация по ID из Sequence может поломаться, если вставляется запись с заданным значением первичного ключа.
Добавить комментарий