За чистый и ясный код!

Статьи на тему программирования под веб, используя PHP, MySQL, Jquery и многое другое

Особенности работы LAST_INSERT_ID() и AUTO_INCREMENT в MySQL

Март11

Всем привет.

Причина по которому я написал эту статью — это неожиданное поведение ф-ции 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 по тематике данной статьи.

Статья просмотренна 193339 раз, зашло посетителей 53501

  

Облако тегов

cli csv dump events form Kohana locale models MySQL mysqldump orm PHP tools trigger validate газ газовый счетчик итоги кеширование переменные

Облако тегов плагина WP Cumulus для WordPress требует для просмотра Flash Player 9 или выше.

Я на твиттере!

  • у твиттера тоже бывают перерывы...

Календарь

Ноябрь 2017
Пн Вт Ср Чт Пт Сб Вс
« Июл    
 12345
6789101112
13141516171819
20212223242526
27282930  

Сейчас на сайте