проблемы, возникающие при объявлении параметра AUTOJNCREMENT

Необходимо помнить особенности поведения столбцов с параметром AUT0 INCREMENT. Это поможет избежать следующих неожиданностей.

auto INCREMENT - это не тип данных столбца, а атрибут типа данных. Более того, это атрибут, предназначенный для работы с целочисленными типами данных. Версии СУБД MySQL менее 3.23 позволяют назначать этот атрибут даже для данных типа char. Однако только целые типы работают с этим атрибутом корректно.

Главным предназначением механизма AUTO INCREMENT является создание последовательности целых положительных чисел. По этой причине все столбцы, имеющие атрибут AUTO lNCREMENT, должны объявляться как unsigned. Это вдвое увеличит диапазон значений, принимаемых столбцом.

При определенных условиях можно генерировать последовательность значений и в отрицательной области чисел. Но автор этого делать не рекомендует. Если пользователь все же вознамерился это сделать, при переносе базы данных в другую версию СУБД MySQL необходимо провести все возможные проверки. Опыт автора свидетельствует о том, что здесь можно ожидать возникновения проблем.

Не надо думать, что добавление атрибута AUTO INCREMENT волщебным образом предоставит вам возможность генерации неофаничен-ной последовательности чисел. Диапазон значений столбца с афибутом AUTOINCREMENT подчиняется законам диапазона значений для типа, с которым объявлен данный столбец. Например, столбец типа TINYINT UNSIGNED будет иметь максимальное значение 255. По достижению этого значения счетчиком, MySQL будет выдавать сообщение об ошибке повторение ключа ( duplicate епог ).

Основными нововедениями в поведение афибута auto lNCREMENT является невозможность повторного использования однажды использованного значения и возможность задания начального значения в операторе CREATE table. Эти свойства можно отменить, задав оператор DELETE.

DELETE FROM tbl name

При этом номер последовательности устанавливается в 1. Это произойдет, даже если начальное значение задано в операторе CREATE TABLE. Это происходит потому, что в СУБД MySQL оператор DELETE оптимизирован таким образом, что полностью очищает таблицу. При этом происходит не пошаговое удаление записи за записью, а сначала полностью удаляются, затем вновь создаются файл данных и индексный файл. Поэтому вся информация о последовательных счетчиках будет утеряна. Для того чтобы сохранить инфор-

мацию о последовательных номерах, необходимо обойти механизм оптимизации и выполнить пошаговое удаление оператором:

delete from tbl name where 1 > О

Как вести строго возрастающую последовательность номеров, если версия вашей СУБД MySQL больше 3 23? Одним из выходов будет ведение отдельной таблицы только для генерирования и хранения значений с атрибутом auto lNCREMENT. Записи из этой таблицы удаляться никогда не будут. Таким образом, значения из этой таблицы никогда не будут использоваться повторно. При добавлении новой записи в главную таблицу, сначала добавляется пустая запись в таблицу с последовательными числами, затем в главную таблицу добавляется запись со значением, возвращаемым функцией LAST INSERT ID() .

INSERT INTO ai tbl SET ai col = NULL

INSERT INTO main tbl SET id = LAST INSERT ID () ..

Предположим, что перед нами стоит задача написать приложение, генерирующее значения auto increment. Начальное значение счетчика должно быть равно 100. Необходимо, чтобы эта задача была переносимой на любую версию СУБД MySQL. Как решить эту задачу?

Когда целью является переносимость базы данных, нельзя полагаться на возможность задавать начальное число последовательности из оператора create table, как ЭТО делается в СУБД MySQL версии 3.23. Вместо этого перед добавлением новой записи сначала с помощью оператора

SELECT COUNT(*) FROM tbl name

проверьте, что записи в таблице отсутствуют. Это дополнительный шаг, но он не вызовет дополнительных накладных расходов, так как оператор select count (*) без фразы where работает достаточно быстро. Если таблица пустая, добавьте строку с явно определенным значением в столбце с последовательными номерами, равным 100. Если таблица заполнена, добавьте строку с пустым значением в столбце с последовательными номерами, что позволит генерировать значение автоматически.

Такой метод позволит вам добавлять записи с номерами 100, 101 и т.д. и работает вне зависимости от того, позволяет или не позволяет СУБД MySQL задавать начальное значение. Такой метод работает не точно, если требуется гарантия строгой последовательности, даже если имеет место удаление записей. В этом случае можно совместить этот метод с методом, в котором используется вспомогательная таблица, единственным предназначением которой является хранение текущего значения последовательного номера для их дальнейшего использования в главной таблице.

Зачем могут потребоваться последовательности с начальными номерами, больше 1? Одной из причин этого может быть необходимость хранения чисел с одинаковым количеством цифр в них. Например, в процессе обработки данных о потребителях можно ожидать, что их число никогда не превысит один миллион. В этом случае начальное значение счетчика можно установить равным 1000000.

Еще один способ установить длину чисел - применить атрибут ZEROFILL. В зависимости ОТ контекста, в котором используются данные, это может породить определенные проблемы. Так, например, необходимо быть предельно осторожными, используя последовательности с ведущими нулями в сценариях Perl или РНР и работать с ними только как со строками, так как после их преобразования в числа, ведущие нули будут потеряны. Вот короткий сценарий Perl, демонстрирующий работу с такими данными.

#! /usr/bin/perl

$s = 00010 ; # создать число с ведущими нулями; print $s\n ;

$s++; # инкрементировать это число

print $s\n ;

$s += 1; # использование $s в цифровом контексте

print $s\n ;

Этот сценарий вернет:

00010 Okay

00011 Okay

12 Oops!

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

Могут существовать и другие причины для присвоения начального значения, отличного от 1. Например, при присвоении членских номеров возникает необходимость всячески избегать присвоения номера 1 во избежание политических последствий присвоения кому-либо такого номера. И такие факты уже имели место.

Не параметризированная генерация последовательностей AUTOJNCREMENT

Есть и другой способ генерации последовательностей вообще без применения параметра AUTO INCREMENT. Для этого используется вариант функции LAST INSERT ID() С аргументом. (Эта модификация функции бьша введена в СУБД MySQL версии 3.22.9.) Параметр ехрг в выражении LAST INSERT ID (ехрг) можно рассматривать как счетчик, созданный механизмом AUTO INCREMENT, Т.е. после добавления или модификации столбца с помощью функции LAST INSERT ID (ехрг) последующее обращение к функции LAST INSERT ID О вернет значение ехрг. Такой механизм позволяет создавать счетчик и затем использовать его в последующих операторах внутри одного сеанса работы с базой данных.

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