Автоинкремент в SQLite, MySQL и PostgreSQL

Справочник · SQL · 2 минуты чтения

СУБД	Столбец с автоматическим номером
SQLite	`id INTEGER PRIMARY KEY`
MySQL, MariaDB	`id INT AUTO_INCREMENT PRIMARY KEY`
PostgreSQL 10 и новее	`id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY`
PostgreSQL, старая запись	`id serial PRIMARY KEY`
Microsoft SQL Server	`id INT IDENTITY(1,1) PRIMARY KEY`

При вставке столбец id не указывают — номер ставит СУБД:

SQL

INSERT INTO notes (text) VALUES ('первая'), ('вторая');

Для больших таблиц вместо INT/integer берут BIGINT/bigint: обычное целое кончается на 2 147 483 647.

SQLite: INTEGER PRIMARY KEY

SQL

CREATE TABLE notes (
    id   INTEGER PRIMARY KEY,
    text TEXT
);

Такой столбец — псевдоним скрытого номера строки rowid, и SQLite заполняет его сама: наибольший номер плюс один. Тип должен быть написан именно INTEGER. С INT PRIMARY KEY столбец становится обычным, и без явного значения в нём окажется NULL:

SQL

CREATE TABLE bad (id INT PRIMARY KEY, text TEXT);
INSERT INTO bad (text) VALUES ('а'), ('б');
SELECT * FROM bad;

id	text
NULL	а
NULL	б

Нужно ли слово AUTOINCREMENT

id INTEGER PRIMARY KEY AUTOINCREMENT отличается одним: номера никогда не используются повторно. Без AUTOINCREMENT, если удалить строку с наибольшим номером, следующая получит тот же номер:

Таблица	Строки 1, 2, 3; удалили 3; добавили новую
`INTEGER PRIMARY KEY`	новая строка получит номер 3
`INTEGER PRIMARY KEY AUTOINCREMENT`	новая строка получит номер 4

AUTOINCREMENT нужен, когда старый номер может где-то храниться (в ссылках, во внешней системе) и повтор опасен. Документация SQLite советует без нужды его не ставить: он немного замедляет вставку. Слово допустимо только с INTEGER PRIMARY KEY, иначе ошибка AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY.

Номер только что добавленной строки

СУБД	Как узнать
SQLite	`SELECT last_insert_rowid();` или `INSERT … RETURNING id` (3.35 и новее)
MySQL, MariaDB	`SELECT LAST_INSERT_ID();`
PostgreSQL	`INSERT … RETURNING id`

SQL

INSERT INTO notes (text) VALUES ('третья') RETURNING id;

Эти функции возвращают номер, выданный в этом же подключении, поэтому чужие вставки в то же время им не мешают. Не вычисляйте номер как SELECT MAX(id): если две программы вставляют строки одновременно, обе получат один и тот же ответ.

Сбросить счётчик

Сброс счётчика при живых строках в таблице приведёт к ошибкам: новые номера столкнутся со старыми (UNIQUE constraint failed). Сбрасывайте его только в пустой таблице.

SQLite без AUTOINCREMENT — счётчика нет: в пустой таблице нумерация сама начнётся с 1. С AUTOINCREMENT счётчик хранится в служебной таблице: DELETE FROM sqlite_sequence WHERE name = 'notes';.
MySQL — ALTER TABLE notes AUTO_INCREMENT = 1; или TRUNCATE TABLE notes; (удалит и все строки).
PostgreSQL — ALTER TABLE notes ALTER COLUMN id RESTART WITH 1; для identity, TRUNCATE notes RESTART IDENTITY; — очистить и сбросить.

Дыры в нумерации — это нормально

После удаления строк, отменённой транзакции или неудачной вставки в номерах появляются пропуски: 1, 2, 5, 6. MySQL и PostgreSQL не возвращают номер, даже если вставка откатилась. Номер нужен, чтобы отличать строки, а не чтобы их считать. Сколько строк в таблице, говорит COUNT(*), а порядковый номер в ответе даёт ROW_NUMBER() OVER (ORDER BY id).