Решение ERROR 1062 и 1046 MySQL: AUTO_INCREMENT и выбор БД

Быстрый ответ

Для того чтобы в MySQL задать полю свойство AUTO_INCREMENT, это поле должно быть целочисленным и быть определённым как PRIMARY KEY. Вот пример создания новой таблицы со столбцом, значение которого автоматически увеличивается:

CREATE TABLE AllAboutIncrement (
  IncrementID INT AUTO_INCREMENT PRIMARY KEY
);

Если вам необходимо применить это свойство к уже существующей таблице, используйте следующую команду:

ALTER TABLE AllAboutIncrement MODIFY ID INT AUTO_INCREMENT PRIMARY KEY;

Такая команда заставит поле ID автоматически инкрементироваться с каждым новым внесённым в таблицу записью.

Подготовка к применению AUTO_INCREMENT

Прежде чем задать полю свойство AUTO_INCREMENT, необходимо выполнить следующие действия:

Удаление аномалий в идентификаторах

Значениями идентификаторов должны быть только положительные целые числа. Отбросьте все отрицательные или нулевые значения:

-- Сначала устраним все несоответствия – нули сделаем положительными, а отрицательные числа – абсолютными с помощью функции ABS и увеличим отрицательные числа на единицу
UPDATE AllAboutIncrement SET ID = ABS(ID) + 1 WHERE ID <= 0;

Подробнее об этом расскажет наш спикер на видео

Устранение дубликатов

Удалите все дубликаты, отрицательные или нулевые значения идентификаторов:

-- Пришло время избавиться от дубликатов
DELETE FROM AllAboutIncrement WHERE ID < 0;

-- Маленький лайфхак – игнорируем дубликаты при установке уникальности
ALTER IGNORE TABLE AllAboutIncrement ADD UNIQUE (ID);

Задание начального значения AUTO_INCREMENT

Задайте начальное значение для свойства AUTO_INCREMENT, которое должно быть больше максимального текущего значения ID:

-- Давайте попробуем установить начальное значение AUTO_INCREMENT равным 1000000
-- Но подобное число уместно использовать, если ваш максимальный ID действительно приближается к этому порогу, иначе добавление новых записей может занять много времени!
ALTER TABLE AllAboutIncrement AUTO_INCREMENT = 1000000;

Визуализация

Воспринимайте AUTO_INCREMENT как номера клиентов в кафе 👩‍🍳💨✨. Каждый новый клиент, подходящий к прилавку, получает свой уникальный номер – это и есть идентификатор его заказа:

До: [ 1️⃣, 2️⃣, 3️⃣, ... ]
В очереди: 🚶‍♂️➡️ Заказ №1️⃣ ➡️ 🍰 🚶‍♀️➡️ Заказ №2️⃣ ➡️ 🍞 🏃➡️ Заказ №3️⃣ ➡️ 🥖
После появления нового клиента: [ 1️⃣, 2️⃣, 3️⃣, ..., 4️⃣ ]

С использованием AUTO_INCREMENT, каждая новая запись (или заказ 🍰) в вашей таблице (или в базе данных 💻) получает свой следующий уникальный номер.

Особенности AUTO_INCREMENT

Работа с настройкой 'NO_AUTO_VALUE_ON_ZERO'

При использовании команды ALTER TABLE для изменения таблицы с установкой AUTO_INCREMENT, воспользуйтесь настройкой 'NO_AUTO_VALUE_ON_ZERO', чтобы нулевое значение не сбрасывало счётчик:

-- Ноль тоже имеет своё значение!
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
ALTER TABLE AllAboutIncrement ...
SET @@session.sql_mode = DEFAULT;

Настройка SQL_MODE

В некоторых случаях вам может потребоваться временная настройка SQL_MODE – не забывайте устанавливать стандартный режим по завершении:

Профессиональные рекомендации для AUTO_INCREMENT

Избегайте использования зарезервированных слов

Не используйте зарезервированные слова SQL в качестве названий столбцов. Это поможет избегать недоразумений и ошибок в синтаксисе:

-- Давайте переименуем столбец во что-то более оригинальное
ALTER TABLE AllAboutIncrement MODIFY TheFieldOfIncrementingDoom INT AUTO_INCREMENT;

Проверка целостности данных

Проверьте целостность данных перед внесением любых изменений:

-- Быстрая проверка состояния нашей схемы
SELECT * FROM information_schema.tables WHERE table_schema = 'NameOfYourSchema';

Тестирование изменений

Не забывайте тестировать все внесённые изменения. Убедитесь, что все работает правильно, добавив тестовые данные:

-- Давайте проведем небольшой эксперимент
INSERT INTO AllAboutIncrement (YourColumnName) VALUES (NULL);

Извлекайте урок из ошибок

Не бойтесь сообщений об ошибках. Воспринимайте их как своего рода помощники в решении SQL-проблем.

Полезные материалы

1. MySQL :: Руководство по MySQL 8.0 :: 3.6.9 Использование AUTO_INCREMENT — официальная документация по AUTO_INCREMENT в MySQL.
2. Самые новые вопросы с тегом 'auto-increment' на Stack Overflow — обсуждение вопросов, связанных с AUTO_INCREMENT, на крупнейшем форуме разработчиков.
3. MySQL – Управление дублирующими записями — руководство по управлению дубликатами в MySQL.
4. SQL AUTO INCREMENT поля — подробное руководство W3Schools по применению AUTO_INCREMENT.
5. AUTO_INCREMENT – База знаний MariaDB — статьи о AUTO_INCREMENT в базе знаний MariaDB.