Автоувеличение в SQLite

Автоувеличение в SQLite

Выводы

1. Ключевое слово AUTOINCREMENT вызывает дополнительный расход ресурсов CPU, памяти, дискового пространства и операций ввода вывода а мы этого должны избегать по возможности. Использовать этот параметр обычно не обязательно.
2. В SQLite, колонка с типом INTEGER PRIMARY KEY это синонимом для ROWID (за исключением таблиц типа WITHOUT ROWID) которые всегда являются целыми 64-bit числами со знаком.
   
3. В предложении INSERT, если ROWID или колонка INTEGER PRIMARY KEY явно не задано значение, тогда это поле будет автоматически заполнено целым числом до этого неиспользованным, обычно это число большее, чем любое использованное до этого. Это действует вне зависимости от того используете вы AUTOINCREMENT или нет.
4. Если ключевое слово AUTOINCREMENT появляется после INTEGER PRIMARY KEY, то это меняет алгоритм автоматического выбора значения ROWID в части повторного использования ROWID за весь период существования базы данных. Другими словами, цель использования AUTOINCREMENT состоит в том чтобы предотвратить повторное использования ROWIDs из уже удаленных строк.

Описание

В SQLite, строки таблицы обычно имеют целое с знаком длиной 64-bit с именем ROWID , которое уникально среди всех строк в этой таблице. (WITHOUT ROWID таблицы это исключение.) 
 

Вы можете получить доступ к ROWID в таблице SQLite используя один из следующих специальных имен колонок ROWID, _ROWID_ или OID. Если вы присвоили эти имена своим колонкам, то доступ к внутренним ROWID теряется.

Если таблица содержит колонки типа INTEGER PRIMARY KEY, тогда эта колонка становится синонимом (alias) для ROWID. Вы можете теперь получить доступ к ROWID используя любое из четырех различных имен — три оригинальных имени описанных выше или имя, которое вы присвоили колонки типа INTEGER PRIMARY KEY. Все эти имена являются синонимами для друг друга и одинаково хорошо в любых контекстах.

Когда в таблицу SQLite вставляется новая строка, ROWID может быть задана как часть предложения INSERT или может быть назначена автоматически движком базы данных. Чтобы задать ROWID в ручную, нужно только включить его в список вставляемых значений. Например:

CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');

Если при вставке, ROWID не задан, или если заданный ROWID имеет значение NULL, тогда соответствующий ROWID создается автоматически. Обычный алгоритм состоит в том, чтобы ROWID для вновь созданной строки должен являться большим чем ROWID в строках таблицы, введенных до этого. Если таблица первоначально пуста, то в качестве ROWID используется число 1. Если наибольший ROWID равен наибольшему целому числу (9223372036854775807), тогда движок базы данных начинает перебор положительных кандидатов ROWID в случайном порядке до тех пор пока не найдет в таблице не использованное ранее значение. Если такого ROWID нет, то операция вставки заканчивается не удачей с ошибкой SQLITE_FULL. Если явно заданное отрицательное значение ROWID не может быть вставлено, тогда автоматически генерируется ROWID, которое всегда больше нуля.

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

Ключевое слово AUTOINCREMENT

Если колонка имеет тип INTEGER PRIMARY KEY AUTOINCREMENT, тогда для нее используется немного отличающийся алгоритм выбора ROWID. Значение ROWID, выбираемое для новой строки, должно быть хотя бы на единицу больше наибольшего значения ROWID уже записанного в таблице. Если в таблице не было данных, значение ROWID принимается равным 1. Если таблица уже содержала раньше запись с наиболее возможным ROWID, тогда вставить новую запись не удастся, произойдет ошибка с кодом SQLITE_FULL error. Рассматриваются только значения ROWID тех записей, для которых выполнена операция коммит. Значения ROWID записей, которые отменены (rolled back) могут снова использоваться.

SQLite отслеживает самый большой ROWID, который когда-либо проводила таблица, используя внутреннюю таблицу с именем «sqlite_sequence». Таблица sqlite_sequence создается и инициализируется автоматически, когда создается нормальная таблица, содержащая столбец AUTOINCREMENT. Содержание таблицы типа sqlite_sequence можно изменять, используя обычные UPDATE, INSERT или DELETE предложения. Но внесение изменений в такую таблицу, вероятно, нарушит алгоритм генерации ключа принятый для AUTOINCREMENT. Будьте уверены, в том что произойдет, перед выполнением таких изменений.

Поведение, выполняемое ключевым словом AUTOINCREMENT, существенно отличается от поведения по умолчанию. С AUTOINCREMENT в строках с автоматически выбранными ROWID гарантируется наличие ROWID, которые никогда не использовались ранее одной таблицей в той же базе данных. И автоматически генерируемые ROWID гарантированно будут монотонно увеличиваться. Это важные свойства в некоторых приложениях. Но если вашему приложению не нужны эти свойства, вы, вероятно, должны оставаться с поведением по умолчанию, так как использование AUTOINCREMENT требует дополнительной работы, которая должна выполняться по мере добавления каждой строки и, следовательно, заставляет INSERT работать немного медленнее.

Обратите внимание, что «монотонное увеличение» не означает, что ROWID всегда увеличивается ровно на единицу. Единица - обычное приращение. Однако, если вставка завершилась неудачей из-за (например) ограничения уникальности, ROWID неудачной попытки вставки не может быть повторно использован для последующих вставок, что приведет к пробелам в последовательности ROWID. AUTOINCREMENT гарантирует, что автоматически выбранные ROWID будут увеличиваться, но не обязательно, что они будут последовательными.

Так как ключевое слово AUTOINCREMENT изменяет поведение алгоритм поиска ROWID, AUTOINCREMENT не разрешается в таблицах типа WITHOUT ROWID или на табличных колонках с типом отличным от INTEGER PRIMARY KEY. Любая попытка использовать AUTOINCREMENT на таблице типа WITHOUT ROWID или на колонках отличных от INTEGER PRIMARY KEY приведет к возникновению ошибки.