Особенности работы LAST_INSERT_ID() и AUTO_INCREMENT в MySQL
Всем привет.
Причина по которому я написал эту статью — это неожиданное поведение ф-ции LAST_INSERT_ID() при выполнении запроса в котором я в поле с атрибутом AUTO_INCREMENT передавал значение.
LAST_INSERT_ID()
И так имеем таблички
Таблица авторов: CREATE TABLE `author` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `name` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `fam` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', `birthday` DATE NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=1; Таблица книг авторов: CREATE TABLE `books` ( `id` INT(10) NOT NULL AUTO_INCREMENT, `id_author` INT(10) NULL DEFAULT NULL, `book` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci', PRIMARY KEY (`id`), INDEX `id_author` (`id_author`) ) COLLATE='utf8_unicode_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=1;
Добавляем данные, обратите внимание на запросы тут :
INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Николай Николаевич', 'Носов', '2008-11-23'); SET @lastID := LAST_INSERT_ID(); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Незнайка учится'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Незнайка-путешественник');
и тут :
INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (5, 'Артур Конан', 'Дойль', '1859-05-22'); SET @lastID := LAST_INSERT_ID(); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Шерлок Холмс');
Ну разница не только в значениях полей, а тут главная фишка, что при добавлении автора в первой тройке запросов, автоинкремент делал сама БД, а во второй тройке я принудительно указал номер в поле автоикремент, вот что получилось:
mysql> SELECT * FROM author; +----+-------------------------------------+------------+------------+ | id | name | fam | birthday | +----+-------------------------------------+------------+------------+ | 1 | Николай Николаевич | Носов | 2008-11-23 | | 5 | Артур Конан | Дойль | 1859-05-22 | +----+-------------------------------------+------------+------------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM books; +----+-----------+-----------------------------------------------+ | id | id_author | book | +----+-----------+-----------------------------------------------+ | 1 | 1 | Незнайка учится | | 2 | 1 | Незнайка-путешественник | | 3 | 2 | Затерянный мир | | 4 | 2 | Шерлок Холмс | +----+-----------+-----------------------------------------------+ 4 rows in set (0.00 sec)
Как вы заметили, с таблицей авторов все окей, 1 и 5 запись добавились с теми номерами ключей которые указала БД и я соот.
Во второй таблице — книги, все хуже. У книг с ИД 1 и 2 ИД_автора все ок, привязаны к автору с ИД1 в таблице авторов, а вот книги с ИД 3 и 4 привязаны к автору ИД которого в таблице авторов просто нет?!
Почему произошла такая ситуация. Разбираем.
Оказывается эта функция LAST_INSERT_ID() возвращает ИД поля с атрибутом автоинкремент если запись была вставлена успешно и автоинкремент работал!!!! Что у нас и было в первой тройке запросов, а вот во второй тройке запросов увы такого не было, т.к. я указал ИД, а значение 2 взялось, т.к. последний успешный запрос был при добавлении второй книги первого автора!
Какие еще приколы скрывает в себе эта функция, оказывается если вы делаете многократное добавление в рамках одного запроса, то ф-ция LAST_INSERT_ID() выдаст ИД только первой успешно добавленной записи!!! Пруфлинк.
Вот пример
TRUNCATE `author`; TRUNCATE `books`; INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Артур Конан', 'Дойль', '1859-05-22'); SET @lastID := LAST_INSERT_ID(); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир'), (NULL, @lastID, 'Шерлок Холмс');
Результат
mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec)
А если сделать так
TRUNCATE `author`; TRUNCATE `books`; INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Артур Конан', 'Дойль', '1859-05-22'); SET @lastID := LAST_INSERT_ID(); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир'); INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Шерлок Холмс'); SELECT LAST_INSERT_ID();
Результат
mysql> SELECT LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ | 2 | +------------------+ 1 row in set (0.00 sec)
Век живи — век учись (© Народная мудрость).
В заключении еще решил разобрать поведение атрибута AUTO_INCREMENT.
AUTO_INCREMENT
Это такой атрибут который генерирует порядковый номер к указанному полю. Полей должно быть только с типом integer или float (TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, FLOAT) и имеющий индекс типа (UNIQUE, PRIMARY, KEY). Причем у каждого из типа поля есть свой предел автоинкремента, например для поля TINYINT это значение 127 или 255 для TINYINT UNSIGNED и тд смотрите документацию. Каждая последующая запись будет +1 (по-дефолту) к максимальному числу до добавления в этом поле, наверное не так лучше сказать.
В MySQL есть вспомогательная БД information_schema в которой хранится необходимая информация о всех таблицах всех БД сервера. И вот именно там храниться следующий номер для автоинкремента, т.е. перед добавление записи в таблицу БД не ищет максимальный номер записи, на это ведь будет тратиться время, которое дорого.
В таблице может быть только одно поле которое имеет автоинкремент, и причем это поле не должно иметь дефолтного значения.
Если мы хотим указать какой-то свой номер для поля с автоинкрементом, то мы должны просто передать этому полю значение. Если значение уже есть в таблице, то будет ошибка если тип индекса в поле UNIQUE или PRIMARY, но есть тип индекса KEY то запись спокойно добавится.
Если по каким-то причинам нам надо указывать поле с автоинкрементом в запросе, но мы хотим чтобы этому полю было присвоено значение автоинкремента автоматом, то в это поле надо передать 0 (ноль) или NULL:
INSERT INTO`tablename`VALUE (0); или INSERT INTO`tablename`VALUE (NULL); или INSERT INTO`tablename`VALUE (NULL), (0); // два запроса на добавления
Если по каким-то причинам вы хотите добавить значение 0 в поля автоинкремента, для этого необходимо прописать в файле-настроке (my.ini / my.cnf) следующую строчку
sql-mode = NO_AUTO_VALUE_ON_ZERO
но делать это не рекомендуется, т.к. если сделать дамп БД и потом развернуть его на другом сервере, где нет такой настройки, то данные будут искажены.
Если по каким-то причинам вам необходимо сделать чтобы следующая запись начиналась с какого-нибудь друго числа, просто напишите команду:
ALTER TABLE `tablename` AUTO_INCREMENT = тут_пишем_нужное_число;
не забывайте об используемом типе поля, чтобы ваше число не вышло за рамки.
А например возникла задача делать автоинкремент не ++1 а через 5, т.е. 1,6,11 т.д. как быть в таком?
Для этого имеются две переменные auto_increment_increment и auto_increment_offset. auto_increment_increment — указывает шаг приращения, а auto_increment_offset указывает стартовую точку.
Для просмотра что у вас используется команда, которая показывает как у вас настроен автоинкремент:
mysql> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.00 sec)
Поменять можно 2 путями — только на время работы сервера БД (до перезагрузки) и навсегда — смена конфигурационного файла
1 путь : SET @@auto_increment_increment=5; SET @@auto_increment_offset=1; 2 путь в файле my.ini / my.cnf пишем строки : auto_increment_increment=5; auto_increment_offset=1;
Более детальней об изменении AUTO_INCREMENT читать тут.
Обнаружил в интернете интересный сайт с подкастами. Советую скачать podcast, подкасты на различные темы.Также советую послушать подкасты на сайте dev.mysql.com по тематике данной статьи.
Статья просмотренна 236193 раз, зашло посетителей 70941