MySQL AUTO_INCREMENT із прикладами
Редакційна колегія
⚡ Розумний підсумок
MySQL AUTO_INCREMENT автоматично генерує послідовні числа для числового стовпця щоразу, коли вставляється рядок. Атрибут усуває необхідність обчислювати унікальні ідентифікатори вручну, що робить його стандартним способом заповнення первинного ключа.
Що таке автоматичне збільшення?
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 in 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 може зарезервувати блок чисел для багаторядкової вставки та відкинути ті, що не використовуються.
Спроба заповнити ці прогалини є помилкою. Перенумерація рядків порушує кожен зовнішній ключ, який вказує на них, і саме значення не має жодного бізнес-сенсу. Якщо звіту потрібен безперервний список, згенеруйте номер рядка в запиті, замість того, щоб перезаписувати збережені дані.
