MySQL AUTO_INCREMENT с примерами
Маркус Аллен
⚡ Умное резюме
MySQL Атрибут AUTO_INCREMENT автоматически генерирует последовательные номера для числового столбца при каждой вставке строки. Этот атрибут избавляет от необходимости вручную вычислять уникальные идентификаторы, что делает его стандартным способом заполнения первичного ключа.
Что такое автоинкремент?
Автоинкремент — это функция, которая работает с числовыми типами данных. Он автоматически генерирует последовательные числовые значения каждый раз, когда запись вставляется в таблицу для поля, определенного как автоматическое приращение.
Этот атрибут работает с любым целочисленным типом, от TINYINT до BIGINT. Столбец также должен быть проиндексирован, что происходит автоматически при его объявлении в качестве первичного ключа.
Когда использовать автоматическое увеличение?
В уроке по нормализация базы данныхМы рассмотрели, как можно хранить данные с минимальной избыточностью, размещая их во множестве небольших таблиц, связанных друг с другом первичными и внешними ключами.
Первичный ключ должен быть уникальным, поскольку он однозначно идентифицирует строку в базе данных. Но как гарантировать, что первичный ключ всегда будет уникальным?
Одним из возможных решений может быть использование формулы для генерации первичного ключа, которая проверяет наличие ключа в таблице перед добавлением данных. Это может сработать, но такой подход сложен и не является абсолютно надежным. Две сессии, выполняющие вставку данных в один и тот же момент, все равно могут считать одно и то же максимальное значение и вызвать конфликт.
Чтобы избежать такой сложности и гарантировать уникальность первичного ключа, мы можем использовать MySQL Функция автоинкремента используется для генерации первичных ключей. Автоинкремент применяется к типу данных INT. Тип данных INT поддерживает как знаковые, так и беззнаковые значения. Беззнаковые типы данных могут содержать только положительные числа. В качестве рекомендации рекомендуется установить ограничение на беззнаковые значения для автоинкрементного первичного ключа.
Синтаксис автоматического увеличения
Разобравшись с причинами, взгляните на скрипт, использованный для создания таблицы категорий фильмов.
CREATE TABLE `categories` ( `category_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `category_name` varchar(150) DEFAULT NULL, `remarks` varchar(500) DEFAULT NULL, PRIMARY KEY (`category_id`) );
Обратите внимание на параметр «AUTO_INCREMENT» в поле category_id. Это приводит к автоматической генерации идентификатора категории каждый раз при добавлении новой строки в таблицу. При вставке данных в таблицу он не указывается. MySQL генерирует его.
Примечание: Ключевое слово UNSIGNED удваивает положительный диапазон столбца, а ширина отображения, ранее записанная как int(11), устарела с MySQL Начиная с 8.0.17. Простой Int это текущая форма.
По умолчанию начальное значение параметра AUTO_INCREMENT равно 1, и оно будет увеличиваться на 1 для каждой новой записи.
Давайте рассмотрим текущее содержимое таблицы категорий.
SELECT * FROM `categories`;
Выполнение приведенного выше сценария в MySQL При использовании Workbench для работы с базой данных myflixdb мы получаем следующие результаты.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
Существует восемь строк, поэтому следующий сгенерированный идентификатор должен быть равен 9. Теперь давайте вставим новую категорию в таблицу категорий, указав только название.
INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');
Выполнение приведенного выше сценария для myflixdb в MySQL верстак дает нам следующие результаты, показанные ниже.
| category_id | category_name | remarks |
|---|---|---|
| 1 | Comedy | Movies with humour |
| 2 | Romantic | Love stories |
| 3 | Epic | Story acient movies |
| 4 | Horror | NULL |
| 5 | Science Fiction | NULL |
| 6 | Thriller | NULL |
| 7 | Action | NULL |
| 8 | Romantic Comedy | NULL |
| 9 | Cartoons | NULL |
Обратите внимание, что мы не указали идентификатор категории. MySQL Сгенерировано автоматически, поскольку идентификатор категории определен как автоматически увеличивающийся.
Если вы хотите получить последний идентификатор вставки, сгенерированный MySQL, для этого вы можете использовать функцию LAST_INSERT_ID. Показанный ниже скрипт получает последний сгенерированный идентификатор.
SELECT LAST_INSERT_ID();
Выполнение приведенного выше скрипта позволяет получить последний автоматически увеличивающийся номер, сгенерированный запросом INSERT. Результаты показаны ниже.
Наконечник: Функция LAST_INSERT_ID() ограничена областью действия вашего собственного соединения, поэтому значение, сгенерированное при вставке другим пользователем, никогда не будет возвращено вам по ошибке.
Как установить или сбросить начальное значение AUTO_INCREMENT
Последовательность по умолчанию начинается с 1, но это не всегда соответствует потребностям проекта. Номера счетов-фактур, возможно, придется сохранить из устаревшей системы, а тестовую таблицу часто приходится сбрасывать. MySQL Это позволяет напрямую использовать счетчик, поэтому оба случая обрабатываются одним условием. Выполните следующие шаги, чтобы управлять начальным числом.
- Установите значение при создании. Добавьте предложение AUTO_INCREMENT к оператору CREATE TABLE. В этом случае первая вставляемая строка получит этот номер вместо 1.
- Изменить значение в существующей таблице. Используйте ALTER TABLE с тем же пунктом. MySQL Принимает новый номер только в том случае, если он больше, чем наибольший идентификатор, хранящийся в данный момент.
- Сбросьте настройки очищенной таблицы. Операция TRUNCATE TABLE удаляет все строки и возвращает счетчик к 1 за одну операцию, чего не делает операция DELETE.
- Подтвердите изменение. Вставьте строку и считайте идентификатор с помощью функции LAST_INSERT_ID(), прежде чем полагаться на новую последовательность.
-- Start a brand-new table at 1000 CREATE TABLE `invoices` ( `invoice_id` int UNSIGNED NOT NULL AUTO_INCREMENT, `amount` decimal(10,2), PRIMARY KEY (`invoice_id`) ) AUTO_INCREMENT = 1000; -- Move the counter on an existing table ALTER TABLE `categories` AUTO_INCREMENT = 100; -- Empty the table and reset the counter to 1 TRUNCATE TABLE `categories`;
Шаг изменения также можно изменить с помощью системной переменной auto_increment_increment, но он применяется ко всему серверу, а не к одной таблице. Он используется в основном в репликации, где два сервера не должны генерировать одинаковый идентификатор.
Почему в последовательности AUTO_INCREMENT появляются пробелы?
Рано или поздно в таблице появляются идентификаторы типа 1, 2, 5, 6. Ничего не сломано. Счетчик предназначен для обеспечения уникальности, а не для обеспечения непрерывной последовательности чисел, и он никогда не выдает одно и то же значение дважды.
Пробелы возникают по следующим причинам.
- Удаленные строки: При удалении строки из таблицы ее автоматически увеличивающийся идентификатор не используется повторно. MySQL продолжает последовательно генерировать новые числа.
- Отменённые транзакции: Номер считается занятым в момент выполнения операции вставки. Если транзакция откатывается, строка исчезает, но номер уже использован.
- Неудачные вставки: Оператор, отклоненный из-за ограничения UNIQUE, все еще может использовать идентификатор до того, как произойдет ошибка.
- Вставки для оптовой продажи: InnoDB может зарезервировать блок чисел для вставки нескольких строк и отбросить те, которые не используются.
Попытка устранить эти пробелы — ошибка. Перенумерация строк нарушает работу всех внешних ключей, указывающих на них, а само значение не имеет никакого бизнес-смысла. Если отчету нужен непрерывный список, генерируйте номер строки в запросе, а не перезаписывайте сохраненные данные.
