Всё, что вы хотели знать об автоинкременте, но стеснялись спросить
Автоинкремент — это функция в базах данных SQL, которая автоматически генерирует уникальный номер для каждой новой строки в таблице.
Автоинкремент — это функция в базах данных, которая автоматически генерирует уникальный номер для каждой новой строки, добавленной в таблицу.
Как правило этот номер используется в качестве первичного ключа для уникальной идентификации каждой строки в таблице.
При добавлении новой строки в таблицу, где одна из колонок сконфигурирована как автоинкремент, база данных автоматически присваивает каждому новому ряду в этой колонке следующий доступный номер, избавляя пользователя от необходимости вручную управлять значениями первичного ключа. Это упрощает процесс добавления новых строк и гарантирует, что каждая строка имеет уникальный идентификатор.
Если вы хотите попрактиковаться в создании таблиц с "автоинкрементальными" колонками и выполнении операторов SQL, вы можете попробовать использовать онлайн-редактор SQL, такой как SQLize.online, который позволяет создавать таблицы, вставлять данные и выполнять SQL-запросы в действующей базе данных.
Создание таблицы
Ниже показано, как создать таблицу с колонкой - автоинкемкнтом в различных РСУБД.
SQLite:
CREATE TABLE example_table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(50), age INT
);В этом примере столбец id определен как целочисленный тип данных с ключевым словом AUTOINCREMENT. Он также задан как первичный ключ таблицы (PRIMARY KEY).
MySQL и MariaDB:
CREATE TABLE example_table ( id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT );
В этом примере столбец id определен как столбец с автоматическим инкрементом с помощью ключевого слова AUTO_INCREMENT. Он также установлен в качестве первичного ключа таблицы.
SQL Server:
CREATE TABLE example_table ( id INTEGER PRIMARY KEY IDENTITY(1, 1), name VARCHAR(50), age INT );
В отличии от двух первых примеров, в SQL Server не используется термин AUTO INCREMENT, Вместо этого столбец id определен как столбец идентичности с помощью ключевого слова IDENTITY. Он также определён как первичный ключ таблицы. Параметры (1, 1) задают начальное значение и шаг автоинкремента. В приведённом примере поле id будет получать значения начиная с 1 и каждый раз увеличиваться на 1 для каждой новой строки. (1, 2, 3, ...) Максимальное значение ограничивается размерностью типа INTEGER. Изменив параметры на IDENTITY(100, 10) мы можем начать нумерацию с 100 и каждый раз прибавлять 10 (100, 110, 120, 130…). Больше того, в качестве начала и шага можно использовать отрицательные числа. Попробуйте сами здесь.
Oracle:
В этой базе данных всё немного сложнее. Здесь нет простого механизма автонумерации и приходится использовать более сложный механизм - последовательности.
CREATE SEQUENCE example_table_seq; CREATE TABLE example_table ( id INTEGER DEFAULT example_table_seq.nextval PRIMARY KEY, name VARCHAR2(50), age INT );
В этом примере сначала создается последовательность (SEQUENCE) с помощью оператора CREATE SEQUENCE. Затем столбец id определяется как числовой тип данных INTEGER со значением по умолчанию, равным следующему значению последовательности. Он также задается в качестве первичного ключа таблицы. В приведенном примере (по умолчанию) будет создана последовательность с начальным значением 1 и шагом 1. Если нам нужен другой вариант последовательности мы можем указать параметры INCREMENT BY и START WITH при её создании:
CREATE SEQUENCE example_table_seq
INCREMENT BY 10
START WITH 10;PostgreSQL:
Данная СУБД имеет несколько вариантов определения автоинкрементного столбца. Наиболее простым и популярным является ключевое слово SERIAL - которое является псевдонимом для колонки типа INTEGER с автоувеличением. Также в PostgreSQL допустимы варианты SMALLSERIAL и BIGSERIAL для типов SMALLINT и BIGINT соответственно
CREATE TABLE example_table ( id SERIAL PRIMARY KEY, name VARCHAR(50), age INT );
В этом примере столбец id определен как последовательный тип данных, что эквивалентно столбцу с автоматическим инкрементом в других СУБД. Он также задан как первичный ключ таблицы
Кроме того, для создания автоинкрементного столбца в PostgreSQL можно использовать как синтаксис 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` был автоматически заполнен последовательными числами:
+====+=============+=====+ | 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 по умолчанию не допускает такого волюнтаризма и вернёт нам ошибку если мы попытаемся это сделать.
INSERT INTO example_table (id, name, age) VALUES (11, 'John Smith', 30);
[SQL Server]Cannot insert explicit value for identity column in table 'example_table' when IDENTITY_INSERT is set to OFF.
Поэтому, если нам нужно вставить значение в столбец 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
);
--вставляем строку с id = 3
INSERT 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 можно сбросить значение автоматического увеличения для таблицы с помощью таблицы 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, а следующая вставленная строка получит id = 101;
Мы можем удалить автоинкремент следующим запросом:
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:
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:
В PostgreSQL не имеет значения, как создается столбец с автоматическим увеличением (SERIAL, GENERATED ALWAYS AS IDENTITY или с помощью CREATE SEQUENCE), поскольку для изменения последовательности можно использовать инструкцию ALTER SEQUENCE. Однако данная СУБД не гарантирует отсутствие конфликтов после модификации последовательности, поэтому важно проявлять осторожность.
ALTER SEQUENCE example_table_id_seq RESTART WITH 100;
В качестве заключения
Автоинкремент — это функция баз данных, которая генерирует уникальный номер для каждой новой строки, добавляемой в таблицу, избавляя пользователя от необходимости вручную управлять значениями первичного ключа.
Эта функция поддерживается большинством СУБД и может быть легко реализована с использованием разного синтаксиса в каждой базе данных. SQLite, MySQL и MariaDB поддерживают столбцы с автоинкрементом, а также PostgreSQL и Oracle, которые также допускают более продвинутые параметры, такие как последовательности и последовательные типы данных.
Хотя каждая база данных имеет свой собственный синтаксис для реализации и управления автоинкрементом.
В целом функция автоинкремента значительно упрощает процесс добавления новых строк и гарантирует, что каждая строка будет иметь уникальный идентификатор, что необходимо для многих операций с базой данных.