Работа со столбцами AUTO_INCREMENT в MySQL
Автоинкремент позволяет автоматически генерировать уникальный номер каждый раз, когда в таблицу добавляется новая запись. Эта функция особенно полезна для поля первичного ключа, чтобы ключ автоматически устанавливался при каждом добавлении новой записи. Хотя автоматическое инкрементирование можно просто установить и забыть об этом, бывают случаи, когда вы захотите управлять столбцом AUTO_INCREMENT, чтобы установить начальный номер или, возможно, пропустить определенные значения. В этой статье вы узнаете, как использовать столбцы AUTO_INCREMENT в MySQL, а также рассмотрите несколько необычных случаев использования.
Объявление колонки типа AUTO_INCREMENT
Чтобы сделать столбец с автоинкрементом, просто присвойте ему атрибут AUTO_INCREMENT. Обычно поле AUTO_INCREMENT имеет тип INTEGER и объявляется первичным ключом таблицы:
CREATE TABLE widgets ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, description VARCHAR(255) NULL, PRIMARY KEY (id) );
Для столбца AUTO_INCREMENT следует использовать наименьший целочисленный тип данных, который достаточно велик, чтобы вместить максимальное значение последовательности, которое вы хотите уместить. Если столбец достигнет верхнего предела для выбранного типа данных, следующая попытка сгенерировать номер последовательности будет неудачной. Вы всегда можете включить атрибут UNSIGNED, чтобы обеспечить больший диапазон. Например, если вы используете TINYINT, максимально допустимый порядковый номер равен всего 127. Для TINYINT UNSIGNED максимальное значение возрастает до 255. Целочисленные типы включают INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT и BIGINT.
Столбец AUTO_INCREMENT будет увеличиваться на единицу каждый раз при вставке новой строки, поэтому его не нужно включать в операторы INSERT:
INSERT INTO widgets (name, description) VALUES
('widget 1', 'The first widget'),
('widget 2', 'The second widget'),
('widget 3', 'The third widget');
Выбор содержимого таблицы widgets вернет три строки со значениями id 1, 2 и 3 соответственно:
< SELECT * FROM widgets; +----+----------+-------------------+ | id | name | description | +----+----------+-------------------+ | 1 | widget 1 | The first widget | | 2 | widget 2 | The second widget | | 3 | widget 3 | The third widget | +----+----------+-------------------+
Указание значения столбца AUTO_INCREMENT
По умолчанию столбец AUTO_INCREMENT начинается с 1. Вы также можете явно присвоить 0 столбцу, чтобы сгенерировать следующий номер последовательности, если не включен режим SQL NO_AUTO_VALUE_ON_ZERO. Присвоение столбцу значения NULL также приведет к генерации следующего порядкового номера, если столбец был объявлен как NOT NULL. Следовательно, все следующие выражения равноценны:
INSERT INTO widgets (id, name, description) VALUES('another widget', NULL);
INSERT INTO widgets (id, name, description) VALUES(0,'another widget', NULL);
INSERT INTO widgets (id, name, description) VALUES(NULL,'another widget', NULL);
Примечание: начинать с 0 не рекомендуется, так как это может привести к проблемам. Например, если вы сбросите таблицу с помощью mysqldump, а затем перезагрузите ее, MySQL обычно генерирует новые порядковые номера, когда встречает значения 0, в результате чего таблица будет иметь идентификаторы, отличные от тех, которые были сброшены.
Когда вы вставляете любое другое значение в столбец AUTO_INCREMENT, столбец устанавливается в это значение, и последовательность сбрасывается таким образом, что следующее автоматически сгенерированное значение следует последовательно за последним (наибольшим) значением столбца, то есть:
INSERT INTO widgets (id, name, description) VALUES(50, 'another widget', 'another descriptionription');
-- следующая запись увеличивается до 51...
INSERT INTO widgets (id, name, description) VALUES('another widget', 'another descriptionription');
Сброс значения AUTO_INCREMENT
Существует несколько способов сбросить значение AUTO_INCREMENT:
- Вы можете сбросить его с помощью оператора ALTER TABLE. Синтаксис оператора ALTER TABLE для сброса значения автоинкремента выглядит следующим образом:
ALTER TABLE table_name AUTO_INCREMENT = value;
Вы указываете имя таблицы после предложения ALTER TABLE и значение, на которое вы хотите сбросить значение в выражении AUTO_INCREMENT=value. Обратите внимание, что значение должно быть больше или равно текущему максимальному значению столбца автоинкремента:
ALTER TABLE widgets AUTO_INCREMENT = 5;
Если вы вставите новую строку, MySQL присвоит значение 6 столбцу id новой строки.
2. Для сброса значения автоинкремента на ноль можно использовать оператор TRUNCATE TABLE:
TRUNCATE TABLE widgets;
Имейте в виду, что этот оператор удаляет все данные из таблицы навсегда, возвращая вашу таблицу в пустое состояние!
Определение последнего значения столбца AUTO_INCREMENT
Вы можете получить последнее автоматически сгенерированное значение AUTO_INCREMENT с помощью SQL-функции LAST_INSERT_ID() или API-функции mysql_insert_id(). Эти функции специфичны для конкретного сеанса, поэтому на возвращаемые ими значения не влияет другое подключение, которое также выполняет вставку.
При множественной вставке LAST_INSERT_ID() и mysql_insert_id() возвращают ключ AUTO_INCREMENT из первой вставленной строки. Это необходимо для того, чтобы множественные вставки правильно воспроизводились на других серверах при репликации.
Таблицы MyISAM и мультистолбцовые индексы
Для таблиц MyISAM можно указать AUTO_INCREMENT для вторичного столбца в многостолбцовом индексе. В этом случае генерируемое значение для столбца AUTO_INCREMENT рассчитывается по формуле: MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. Это пригодится, если вы хотите поместить данные в упорядоченные группы:
CREATE TABLE widgets (
grp ENUM('Type 1','Type 2','Type 3') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
description VARCHAR(255) NULL,
PRIMARY KEY (grp,id)
) ENGINE=MyISAM;
INSERT INTO widgets (grp, name) VALUES
('Type 1','widget 1'),
('Type 1','widget 2'),
('Type 2','widget 3'),
('Type 2','widget 4'),
('Type 1','widget 5'),
('Type 3','widget 6');
Вот что будет в таблице widgets:
SELECT * FROM widgets ORDER BY grp, id; +--------+----+----------+ | grp | id | name | +--------+----+----------+ | Type 1 | 1 | widget 1 | | Type 1 | 1 | widget 2 | | Type 2 | 2 | widget 3 | | Type 3 | 3 | widget 6 | | Type 1 | 1 | widget 5 | | Type 2 | 2 | widget 4 | +--------+----+----------+
Заключение
Столбцы AUTO_INCREMENT в MySQL обеспечивают простой способ добавления числового первичного ключа в таблицу, который можно установить и забыть. Более того, если вы используете механизм MyISAM, вы можете даже использовать их как часть многостолбцового индекса.
Оригинал статьи: https://www.databasejournal.com/mysql/working-with-auto-increment-columns-in-mysql/