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 может поломаться, если вставляется запись с заданным значением первичного ключа.
Добавить комментарий