Написать пост

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

Аватарка пользователя Slava Rozhnev
Slava Rozhnev
Отредактировано

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

22К открытий32К показов

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

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

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

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

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

SQLite:

			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. Он также установлен в качестве первичного ключа таблицы.

SQL Server:

			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). Больше того, в качестве начала и шага можно использовать отрицательные числа. Попробуйте сами здесь.

Oracle:

			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
);
--вставляем строку с 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_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 также позволяет изменять автоинкремент с помощью запроса 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, которые также допускают более продвинутые параметры, такие как последовательности и последовательные типы данных.

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

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

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

22К открытий32К показов