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 в 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 може да резервира блок от числа за вмъкване на няколко реда и да изхвърли тези, които не използва.
Опитът да се затворят тези празнини е грешка. Преномерирането на редове нарушава всеки външен ключ, който сочи към тях, и самата стойност не носи бизнес смисъл. Ако даден отчет се нуждае от непрекъснат списък, генерирайте номера на реда в заявката, вместо да пренаписвате съхранените данни.
