Sequence in MySQL

Четверг - 31 мая 2012 в 08:15 пп · Filed under Разработка, Структура ·Отмечено mysql, sequence, trigger

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;
        }
    }
}

Ограничения

1. На данный момент (MySQL 5.5) нельзя создать более одного триггера для одной таблицы, с  теми же самыми действиями и событиями.
2. Не реализовано удаление записей из таблицы Sequence.
3. Update первичного ключа, при выполнении запроса вида:

   update t1 set id = 1 where id = 2;

   не сработает триггер и соответственно запись в таблице Sequence останется неверной.

4. Однозначная идентификация по ID из Sequence может поломаться, если вставляется запись с заданным значением первичного ключа.

Ссылки

1. http://sql-language.ru/create-sequence.html
2. http://club.shelek.ru/viewart.php?id=317
3. http://www.rldp.ru/mysql/mysqlpro/triggers.htm
4. http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_trigger

Постоянная ссылка