Что такое автоинкремент

Снова про AUTO_INCREMENT

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

Действительно, будем все хранить в оперативной памяти. Хотя, нет, ее маловато будет. Да и при неожиданном выключении все данные пропадут. Поставим ИБП. Нет, лучше 2. А еще лучше представим, что память у нас бесконечная и данные при выключении не теряет.

Теперь можно писать что-то типа такого:

Order order = new Order(); User user = new User(); order.creator = user;

И насоздавать хоть 10 таких заказов, хоть 20, хоть 100… Хм, что-то много я насоздавал, надо бы коллекцию какую-нибудь. А к ней поиск по полям. И язык запросов, чтобы все универсально было. Плюс индексы по этим полям для ускорения поиска.

Что у нас получилось? Получилась практически обычная база данных. За одним исключением – у объектов нет первичного ключа. Все объекты идентифицируются по адресу в адресном пространстве.

Теперь можно вспомнить, что у оперативной памяти есть свои ограничения, программы закрываются, компьютеры выключаются, данные могут быть нужны нескольким программам сразу. Значит, нужно сделать некоторую абстракцию над оперативной памятью, чтобы адреса у всех были всегда одни и те же. Примерно как виртуальная адресация в защищенном режиме процессора. И хранить это в файлах на диске. И сделать систему управления.

Это подводит к мысли, почему целочисленные ключи удобны в использовании. Причины не только в реализации систем управления БД. База данных – это адресное пространство для размещения объектов. А целочисленный ключ (ID) – это ссылка на объект.

Из этого следует, что auto_increment в пределах одной таблицы — это не совсем правильно. Каждая запись в БД должна иметь уникальный адрес. Получается как бы двухмерное адресное пространство – адреса растут в одном направлении, сами объекты в другом; размер одного объекта не влияет на адреса соседних объектов. При этом неважно, распределенная БД или нет, сколько в ней серверов, баз и таблиц. Это одно адресное пространство, и адресация должна быть однозначной.

Получается, теоретически запись можно найти по ключу без указания таблицы; или наоборот, можно найти саму таблицу, в которой находится запись с таким ключом. Можно даже разбить адресное пространство на диапазоны: например, до 1000000 системные таблицы, настройки, справочники; после 1000000 реальные данные. При переполнении можно добавлять к диапазону заранее определенную достаточно большую константу, или заранее настроить карту диапазонов.

Однако, ID не нужно относить к атрибутам самого объекта как элемента модели данных. Например:

int x = 2;

У переменной x есть значение 2 и адрес 0x123456. Но нельзя сказать, что адрес – это атрибут целочисленных значений. Также нельзя сказать, что ID – это атрибут объектов типа User и Order. Он служит просто для связи абстрактной модели и технической реализации.

Все ограничения естественного ключа нужно делать дополнительными техническими средствами, на уровне самой БД или приложения. На самом деле, я даже не могу представить ситуацию, в которой естественный первичный ключ является «естественным». Все варианты типа номер паспорта, телефона, ИНН – это искусственно введенная нумерация объектов, именно потому что нельзя выделить у них или их владельцев уникальный набор признаков. И использовать ее можно только для идентификации именно этих объектов – выданных паспортов, узлов телефонной сети, экономического субъекта, платящего налоги. В реальности всегда есть возможность существования дубликатов с одинаковыми свойствами, и набора разных свойств в разное время у одной сущности.

P.S.: Мои рассуждения носят теоретический характер, я не работал с базами с подобной структурой. Технически это сделать несложно – одна sequence на все таблицы. Если у кого-то есть такой опыт, просьба поделиться информацией в комментариях – плюсы, минусы, подводные камни.

SQL
Проектирование и рефакторинг

Первичный ключ – GUID или автоинкремент?

Зачастую, когда разработчики сталкиваются с созданием модели данных, тип первичного ключа выбирается «по привычке», и чаще всего это автоинкрементное целочисленное поле. Но в реальности это не всегда является оптимальным решением, так как для некоторых ситуаций более предпочтительным может оказаться GUID. На практике возможны и другие, более редкие, типы ключа, но в данной статье мы их рассматривать не будем.

Ниже приведены преимущества каждого из вариантов.

Занимает меньший объем
Теоретически, более быстрая генерация нового значения
Более быстрая десериализация
Проще оперировать при отладке, поддержке, так как число гораздо легче запомнить

При репликации между несколькими экземплярами базы, где добавление новых записей происходит более, чем в одну реплику, GUID гарантирует отсутствие коллизий
Позволяет генерировать идентификатор записи на клиенте, до сохранения ее в базу
Обобщение первого пункта — обеспечивает уникальность идентификаторов не только в пределах одной таблицы, что для некоторых решений может быть важно
Делает практически невозможным «угадывание» ключа в случаях, когда запись можно получить, передав ее идентификатор в какой-нибудь публичный API

GUID можно генерировать как на клиенте, так и самой базой данных — уже два варианта. К тому же, в MS SQL есть две функции для получения уникального идентификатора — NEWID и NEWSEQUENTIALID. Давайте разберемся, в чем их отличие и может ли оно быть существенным на практике.

Привычная генерация уникальных идентификаторов в том же .NET через Guid.NewGuid() дает множество значений, не связанных друг с другом никакой закономерностью. Если ряд GUID-ов, полученных из этой функции, держать в отсортированном списке, то каждое новое добавляемое значение может «попадать» в любую его часть. Функция NEWID() в MS SQL работает аналогично — ряд ее значений весьма хаотичен. В свою очередь, NEWSEQUENTIALID() дает те же уникальные идентификаторы, только каждое новое значение этой функции больше предыдущего, при этом идентификатор остается «глобально уникальным».

Если использовать Entity Framework Code First, и объявить первичный ключ вот таким образом

[Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] public Guid Id

в базе данных будет создана таблица с первичным кластерным ключом, который имеет значение по умолчанию NEWSEQUENTIALID(). Сделано это из соображений производительности. Опять же, в теории, вставлять новое значение в середину списка более накладно, чем добавление в конец. База данных, конечно же, не массив в памяти, и вставка новой записи в середину списка строк не приведет к физическому сдвигу всех последующих. Тем не менее, дополнительные накладные расходы будут — разделение страниц (page split). По итогу также будет сильная фрагментация индексов, которая может отразиться на производительности выборки данных. Неплохое объяснение того, как происходит вставка данных в кластеризованую таблицу, можно найти в ответах форума по этой ссылке.

Таким образом, для GUID мы имеем 4 варианта, которые стоит проанализировать в плане производительности: последовательный и непоследовательный GUID с генерацией на клиенте, и та же пара вариантов, но с генерацией на стороне базы. Остается вопрос, как получать последовательные GUID на клиенте? К сожалению, стандартной функции в .NET для этих целей нет, но ее можно сделать, воспользовавшись P/Invoke:

internal static class SequentialGuidUtils < public static Guid CreateGuid() < Guid guid; int result = NativeMethods.UuidCreateSequential(out guid); if (result == 0) < var bytes = guid.ToByteArray(); var indexes = new int[] < 3, 2, 1, 0, 5, 4, 7, 6, 8, 9, 10, 11, 12, 13, 14, 15 >; return new Guid(indexes.Select(i => bytes[i]).ToArray()); > else throw new Exception("Error generating sequential GUID"); > > internal static class NativeMethods

Обратите внимание на то, что без специальной перестановки байт, GUID нельзя отдавать. Идентификаторы получатся корректные, но с точки зрения SQL сервера — непоследовательные, поэтому никакого выигрыша по сравнению с «обычным» GUID даже теоретически не получится. К сожалению, ошибочный код приведен во многих источниках.

К списку остается добавить пятый вариант — автоинкрементный первичный ключ. Других вариантов у него нет, так как на клиенте его генерировать нормально не получится.

С вариантами определились, но есть еще один параметр, который следует учесть при написании теста — физический размер строк таблицы. Размер страницы данных в MS SQL — 8 килобайт. Записи близкого или даже большего размера могут показать более сильный разброс производительности для каждого из вариантов ключа, чем на порядок меньшие записи. Чтобы обеспечить возможность варьировать размер записи, достаточно добавить в каждую из тестовых таблиц NVARCHAR поле, которое затем заполнять нужным количеством символов (один символ в NVARCHAR поле занимает 2 байта).

Тестирование

По этой ссылке находится проект с программой, которая была разработана с учетом указанных выше соображений.

Всего три серии тестов с длиной текстового поля в записи 80, 800 и 8000 байт соответственно (количество символов в тестовой программе будет в два раза меньше в каждом из случаев, так как один символ в NVARCHAR занимает два байта).
В каждой из серий — по 5 запусков, каждый из которых добавляет по 10000 записей в каждую из таблиц. По результатам каждого из запусков можно будет проследить зависимость времени вставки от количества строк, уже находящихся в таблице.
Перед началом каждой из серий таблицы полностью очищаются.

Автоинкремент — Основы реляционных баз данных

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

Автогенерация первичного ключа

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

Автогенерация работает по следующим принципам:

Внутри базы создается отдельный счетчик, который привязывается к каждой таблице
Счетчик увеличивается на единицу при вставке новой строки
Получившееся значение записывается в поле, которое помечается как автогенерируемое

Автогенерацию первичного ключа часто называют автоинкрементом (autoincrement). Что переводится как автоматическое увеличение и напоминает операцию инкремента из программирования ++.

До определенного момента механизм автоинкремента был реализован по-своему в каждой СУБД разными способами. Это создавало проблемы при переходе от одной СУБД к другой и усложняло реализацию программного слоя доступа к базе данных.

Эта функциональность добавлена в стандарт SQL:2003, то есть очень давно. И только в 2018 году PostgreSQL в версии 10 стал его поддерживать. Такой автоинкремент известен под именем GENERATED AS IDENTITY:

CREATE TABLE colors ( -- Одновременное использование и первичного ключа и автогенерации id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name varchar(255) ); INSERT INTO colors (name) VALUES ('Red'), ('Blue'); SELECT * FROM colors;

Этот запрос вернет:

id	name
1	Red
2	Blue

Если удалить запись с id равным двум и вставить еще одну запись, то значением поля id будет 3 . Автогенерация не связана с данными в таблице. Это отдельный счетчик, который всегда увеличивается. Так избегаются вероятные коллизии и ошибки, когда один и тот же идентификатор принадлежит сначала одной записи, а потом другой.

Вот его структура из документации:

 AS IDENTITY[ ( sequence_option ) ]

Тип данных может быть SMALLINT, INT или BIGINT
GENERATED ALWAYS — не позволит добавлять значение самостоятельно, используя UPDATE или INSERT
GENERATED BY DEFAULT — в отличие от предыдущего варианта, этот вариант позволяет добавлять значения самостоятельно

PostgreSQL позволяет иметь более одного автогенерируемого поля на таблицу.

Открыть доступ

Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно

130 курсов, 2000+ часов теории
1000 практических заданий в браузере
360 000 студентов

Наши выпускники работают в компаниях:

Всё, что вы хотели знать об автоинкременте, но стеснялись спросить

Автоинкремент — это функция в базах данных SQL, которая автоматически генерирует уникальный номер для каждой новой строки в таблице.

Автоинкремент — это функция в базах данных, которая автоматически генерирует уникальный номер для каждой новой строки, добавленной в таблицу.

Этот номер обычно используется в качестве первичного ключа для уникальной идентификации каждой строки в таблице.

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

Если вы хотите попрактиковаться в создании таблиц с автоинкрементными столбцами и выполнении операторов SQL, вы можете попробовать использовать онлайн-редактор SQL, такой как SQLize.online, который позволяет создавать таблицы, вставлять данные и выполнять SQL-запросы в действующей базе данных.

Ниже показано, как создать таблицу с автоинкрементным столбцом в различных РСУБД.

CREATE TABLE example_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50), age INT );

В этом примере столбец id определен как целочисленный тип данных с ключевым словом AUTOINCREMENT. Он также задан как первичный ключ таблицы.

MySQL и MariaDB:

CREATE TABLE example_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), age INT );

В этом примере столбец id определен как столбец с автоматическим инкрементом с помощью ключевого слова AUTO_INCREMENT. Он также установлен в качестве первичного ключа таблицы.

CREATE TABLE example_table ( id INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(50), age INT );

В этом примере столбец id определен как столбец идентичности с помощью ключевого слова IDENTITY. Он также задан как первичный ключ таблицы. Числа 1,1 задают начальное значение и значение инкремента автоинкрементного столбца. Например мы можем начать нумерацию с 100 и даждый раз прибавлять 10 (100, 110, 120, 130…) заменив IDENTITY(1,1) на IDENTITY(100, 10). Больше того, в качестве начала и шага можно использовать отрицательные числа. Попробуйте сами здесь.

CREATE SEQUENCE example_table_seq; CREATE TABLE example_table ( id NUMBER DEFAULT example_table_seq.nextval PRIMARY KEY, name VARCHAR2(50), age INT );

В этом примере сначала создается последовательность с помощью оператора CREATE SEQUENCE. Затем столбец id определяется как числовой тип данных со значением по умолчанию, равным следующему значению последовательности. Он также задается в качестве первичного ключа таблицы. В приведенном примере будет создана последовательность с начальным значением 1 и шагом 1. Если нам нужен другой вариант мы можем указать параметры INCREMENT BY и START WITH при создании

CREATE SEQUENCE example_table_seq INCREMENT BY 10 START WITH 10;

PostgreSQL имеет несколько вариантов определения автоинкрементного столбца. Наиболее популярным является ключевое слово `SERIAL`.

CREATE TABLE example_table ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INT );

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

Кроме того, для создания автоинкрементного столбца в PostgreSQL можно использовать как синтаксис `IDENTITY`, так и синтаксис `SEQUENCE`. Вот примеры создания автоинкрементного столбца с использованием синтаксиса IDENTITY и SEQUENCE в PostgreSQL:

CREATE TABLE example_table ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(50), age INT );

CREATE SEQUENCE example_table_seq; CREATE TABLE example_table ( id INT DEFAULT nextval('example_table_seq') PRIMARY KEY, name VARCHAR(50), age INT );

Как и в примере с Oracle DB, вариант с SEQUENCE предлагает возможности гибкой конфигурации автоинкремента в отличии от SERIAL

Чтобы вставить новую строку в таблицу с автоинкрементным столбцом, мы можем использовать оператор INSERT INTO, указав для всех столбцов, кроме автоинкрементного:

INSERT INTO example_table (name, age) VALUES ('John Smith', 30);

Большинство диалектов SQL позволяет вставлять несколько строк в один запрос.

INSERT INTO example_table (name, age) VALUES ("Джейн Доу", 25), ('Боб Джонсон', 40), ('Элис Браун', 35);

Если сейчас сделать выборку из таблицы Мы увидим что столбец `id` был автоматически заполнен последовательными числами:

SELECT * FROM example_table;

+====+=============+=====+ | id | name | age | +====+=============+=====+ | 1 | Джейн Доу | 25 | | 2 | Боб Джонсон | 40 | | 3 | Элис Браун | 35 | +----+-------------+-----+

SQLite, MySQL & MariaDB позволяет вставлять произвольные значения в столбец с автоматическим увеличением и обновлять последовательность.

INSERT INTO example_table (id, name, age) VALUES (11, 'John Smith', 30); INSERT INTO example_table (name, age) VALUES ('Jane Doe', 25), ('Bob Johnson', 40), ('Alice Brown', 35); SELECT * FROM example_table;

В приведенном выше примере мы вставляем ’John Smith’ с id = 11 и затем ещё три строки без указания значений `id`. Эти строки будут вставлены со следующими значениями последовательности 12, 13, 14 Проверьте это здесь.

+====+=============+=====+ | id | name | age | +====+=============+=====+ | 11 | John Smith | 30 | | 12 | Jane Doe | 25 | | 13 | Bob Johnson | 40 | | 14 | Alice Brown | 35 | +----+-------------+-----+

MS SQL Server по умолчанию не допускает такого волюнтаризма. Поэтому, если нам нужно вставить значение в столбец identity, мы должны установить IDENTITY_INSERT в нашей таблице:

SET IDENTITY_INSERT example_table ON INSERT INTO example_table (id, name, age) VALUES (11, 'John Smith', 30) SET IDENTITY_INSERT example_table OFF;

Приведенный выше запрос вставляет запись с id = 11 и перемещает вверх счетчик для вставки последующих записей.

А как насчет Oracle? Эта DB позволяет вставлять значения в столбец id, без влияния на последовательности, поэтому мы можем вставлять строку с произвольным id, но это может привести к конфликту, как в следующем примере:

CREATE SEQUENCE example_table_seq; CREATE TABLE example_table ( id NUMBER DEFAULT example_table_seq.nextval PRIMARY KEY, name VARCHAR2(50), age NUMBER ); --вставляем строку с INTO example_table (id, name, age) VALUES (3, 'John Smith', 30); -- следующие запросы вставят строки с id 1 и 2 INSERT INTO example_table (name, age) VALUES ('Jane Doe', 25); -- 1 INSERT INTO example_table (name, age) VALUES ('Bob Johns', 40); -- 2 -- здесь получаем ошибку OCIStmtExecute: -- ORA-00001: unique constraint (0c7690dacb6b.SYS_C009760) violated INSERT INTO example_table (name, age) VALUES ('Alice Brown', 35); SELECT * FROM example_table;

PostgreSQL с синтаксисом SERIAL или SEQUENCE для столбца с автоматическим увеличением показывают нам такое же поведение, как и Oracle (строка вставлена, последовательность не обновлена, ошибка при дублировании). Но при использовании синтаксиса IDENTITY вставка строки с произвольным идентификатором не допускается. Это ограничение может быть обойдено переопределением системного значения, как в следующем примере, но опять же это не обновляет последовательность и может вызвать конфликт.

CREATE TABLE example_table ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(50), age INT ); INSERT INTO example_table (id, name, age) OVERRIDING SYSTEM VALUE VALUES (3, 'John Smith', 30); INSERT INTO example_table (name, age) VALUES ('Jane Doe', 25); INSERT INTO example_table (name, age) VALUES ('Bob Johnson', 40); INSERT INTO example_table (name, age) VALUES ('Alice Brown', 35); SELECT * FROM example_table;

Давайте двигаться дальше! Как изменить значение автоинкремента?

В SQLite можно сбросить значение автоматического увеличения для таблицы с помощью таблицы sqlite_sequence.

Таблица sqlite_sequence — это внутренняя таблица, используемая SQLite для отслеживания следующего значения автоинкремента для каждой таблицы в базе данных. Каждая строка в таблице sqlite_sequence представляет таблицу в базе данных, а в столбце seq хранится следующее значение автоматического увеличения для этой таблицы. Поэтому, когда нам нужно изменить его, просто попробуйте следующее:

UPDATE sqlite_sequence SET seq = 100 WHERE name = 'example_table'; INSERT INTO example_table (name, age) VALUES ('John Gold', 30);

Приведенный выше код изменит значение автоприращения на 100, а следующая вставленная строка получит >

Мы можем удалить автоинкремент следующим запросом:

DELETE FROM sqlite_sequence WHERE name = 'example_table';

или установить его меньше максимального значения в автоматически увеличиваемом столбце, но после вставки новой строки sqlite_sequence будет исправлен автоматически. Просто посмотрите следующий фрагмент кода:

CREATE TABLE example_table ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50), age INT ); INSERT INTO example_table (name, age) VALUES ('Jane Doe', 25), ('Bob Johnson', 40), ('Alice Brown', 35); SELECT * FROM sqlite_sequence WHERE name = 'example_table';

данный код вернет следующий результат:

+===============+=====+ | name | seq | +===============+=====+ | example_table | 3 | +---------------+-----+

Удалим его и убедимся что данные исчезли:

DELETE FROM sqlite_sequence WHERE name = 'example_table'; SELECT * FROM sqlite_sequence WHERE name = 'example_table';

но после следующей записи вставленное значение последовательности также будет восстановлено. Попробуйте здесь.

MySQL & MariaDB также позволяет изменять автоинкремент с помощью запроса ALTER TABLE, но оно не может быть меньше максимального значения в автоматически увеличенном столбце:

ALTER TABLE example_table AUTO_INCREMENT = 100;

В Oracle можно изменить последовательность с помощью инструкции ALTER SEQUENCE. Этот оператор позволяет изменять характеристики последовательности, такие как минимальное и максимальное значения, шаг и начальное значение. Если необходимо внести более существенные изменения в последовательность, например изменить ее тип данных или полностью удалить ее, может потребоваться удалить и пересоздать последовательность.

DROP SEQUENCE example_table_seq; CREATE SEQUENCE example_table_seq INCREMENT BY 1 START WITH 100;

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

В PostgreSQL не имеет значения, как создается столбец с автоматическим увеличением (SERIAL, GENERATED ALWAYS AS IDENTITY или с помощью CREATE SEQUENCE), поскольку для изменения последовательности можно использовать инструкцию ALTER SEQUENCE. Однако данная СУБД не гарантирует отсутствие конфликтов после модификации последовательности, поэтому важно проявлять осторожность.

ALTER SEQUENCE example_table_id_seq RESTART WITH 100;

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

Эта функция поддерживается большинством СУБД и может быть легко реализована с использованием разного синтаксиса в каждой базе данных. SQLite, MySQL и MariaDB поддерживают столбцы с автоинкрементом, а также PostgreSQL и Oracle, которые также допускают более продвинутые параметры, такие как последовательности и последовательные типы данных.

Хотя каждая база данных имеет свой собственный синтаксис для реализации и управления автоинкрементом.

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

Если Вам понравилась статья, Вы можете поддержать автора.