SQLite Автоинкремент

1. Краткое описание

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

2. В SQLite столбец с типом INTEGER PRIMARY KEY является псевдонимом для ROWID (за исключением таблиц WITHOUT ROWID), который всегда является 64-битным знаковым целым числом.

3. При INSERT, если столбцу ROWID или INTEGER PRIMARY KEY не задано явное значение, то он будет заполнен автоматически неиспользуемым целым числом, обычно на единицу большим, чем наибольший используемый ROWID. Это верно независимо от того, используется ли ключевое слово AUTOINCREMENT.

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

2. Общие сведения

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

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

Если таблица содержит столбец типа INTEGER PRIMARY KEY, этот столбец становится псевдонимом для 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 не указан или указан NULL, соответствующий ROWID создаётся автоматически. Обычно алгоритм заключается в присвоении новой строке ROWID, который на единицу больше, чем наибольший ROWID в таблице до вставки. Если таблица изначально пуста, используется ROWID 1. Если наибольший ROWID равен наибольшему возможному целому числу (9223372036854775807), движок базы данных начинает случайным образом выбирать положительные кандидаты ROWID до тех пор, пока не найдёт неиспользуемый. Если после разумного числа попыток не удаётся найти неиспользуемый ROWID, операция вставки завершается с ошибкой SQLITE_FULL. Если явно не вставляются отрицательные значения ROWID, то автоматически сгенерированные значения ROWID всегда будут больше нуля.

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

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

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

SQLite отслеживает наибольший ROWID, используя внутреннюю таблицу с именем "sqlite_sequence". Таблица sqlite_sequence создаётся автоматически, если она ещё не существует, всякий раз, когда создаётся обычная таблица, содержащая столбец AUTOINCREMENT. Строка в таблице sqlite_sequence, соответствующая таблице со столбцом AUTOINCREMENT, создаётся в первый раз, когда таблица AUTOINCREMENT записывается и обновляется при любых последующих записях, увеличивающих максимальный rowid. Содержимое таблицы sqlite_sequence можно изменять с помощью обычных инструкций UPDATE, INSERT и DELETE. Однако внесение изменений в эту таблицу, вероятно, повлияет на алгоритм генерации ключей AUTOINCREMENT. Убедитесь, что вы знаете, что делаете, прежде чем вносить такие изменения. Таблица sqlite_sequence не отслеживает изменения ROWID, связанные с оператором UPDATE, а только оператором INSERT.

Поведение, реализованное ключевым словом 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, приводит к ошибке.