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

Статьи на тему программирования под веб, используя 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 по тематике данной статьи.

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

рубрика: MySQL

15 комментариев в “Особенности работы LAST_INSERT_ID() и AUTO_INCREMENT в MySQL”

  1. Avatar
    Михаил пишет:

    Спасибо за блог! Глубоко копаете, интересно почитать. 🙂


  2. Avatar
    maxnag пишет:

    Спасибо и Вам!
    Оформите подписку, чтобы быть в курсе.


  3. Avatar
    Руслан пишет:

    Спасибо большое информация помогла


  4. Avatar
    maxnag пишет:

    Рад помочь!


  5. Avatar
    Иван пишет:

    Не вводите людей в заблуждение. LAST_INSERT_ID отображает ПОСЛЕДНИЙ успешно вставленный ID и работает для полей, имеющих атрибут AUTO_INCREMENT (для определения последнего успешно вставленного идентификатора). Поведение LAST_INSERT_ID абсолютно закономерно, а изобретение велосипеда в приведенных опытах — недостаточное ознакомление с документацией по SQL

    Цитата:
    "LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement."


  6. Avatar
    maxnag пишет:

    Как-то вы плохо читали мою статью, вы нажимали на ссылку с пруфлинком? Она как раз ведет на ту цитату которую вы приводите. А сказать я хотел, что если я вставляю запись с указанным ИД, то тут эта ф-ция не работает и на этом можно споткнуться.
    Перед тем как писать статью я знакомлюсь с доками и делаю опыты.


  7. Avatar
    Антон пишет:

    По-моему, Вас запутали. Вы что хотели сказать, то и сказали: "LAST_INSERT_ID отображает ПОСЛЕДНИЙ успешно вставленный ID" здесь товарищч Иван не прав, а Вы в статье правы. Ибо смысл в фразе "returns a BIGINT (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement" такой: функция вернёт ПЕРВЫЙ сгенерированный АВТОМАТОМ номер для ПОСЛЕДНЕГО успешного запроса. Если в insert передаются данные для одной строки — вопросов нет. Если вставляются несколько строк, то, думаю, ответ теперь на 100% очевиден.)


  8. Avatar
    maxnag пишет:

    Совершенно с Вами согласен!


  9. Avatar
    Олег пишет:

    Ваня правильно сказал. Вот ваша цитата: "Какие еще приколы скрывает в себе эта функция, оказывается если вы делаете многократное добавление в рамках одного запроса, то ф-ция LAST_INSERT_ID() выдаст ИД только первой успешно добавленной записи!!! Пруфлинк.", и она ошибочна. Вдумайтесь в его комментарий и в вашу цитату, прежде чем спорить и соглашаться с подхалимами


  10. Avatar
    Юля пишет:

    А как выбрать для таблицы конкретной?
    Я делаю SELECT LAST_INSERT_ID() FROM `table1`; и мне возвращается выборка из нулей и их столько, сколько записей в таблице.


  11. Avatar
    maxnag пишет:

    Что выбрать???? Последний вставленный ИД?
    LAST_INSERT_ID() выводит ИД последней успешно вставленной записи!!! и все, дальше мускуль не запоминает что было, это некая ячейка в памяти в которой всегда последний успешно вставленный ИД, а если Вам надо выбрать в любой момент последний ИД в таблице, ну тут масса способов.

    Вот например, который пришел первый в голову — посмотреть БД information_schema таблицу TABLES там есть поле AUTO_INCREMENT который и показывает следующий ИД, который будет, но с ним тоже не все гладко может быть, т.к. если по каким-то причинам ИД не вставилось, а инкремент работает, получится, что данные будут не верны. В общем это проблема. На хабре была статья по этому поводу поищите еще там.


  12. Avatar
    Александр пишет:

    Вопрос в ту же "тему": а что делать по переполнению значения ID? Допустим, id с автоинкрементом составляет диапозон от 1 до 127. Когда в таблице уже есть запись с id=127, следующую запись БД уже не принимает. Понимаю. Можно ставить ID Integer, и даже bigInteger — но теоретически все-равно остается такая возможность — переполнения ID. Что делать в этом случае? Писать процедуру проверки на переполнение и дропать ID?
    Кто как решал это 🙂 Поделитесь плиз 🙂


  13. Avatar
    maxnag пишет:

    Ну ваш вопрос мне аналогичен, а что делать если вокруг Земли летают метеоры и астероиды и они могут когда-то на нас упасть…
    Наверное если у вас таблица для логов, то она наполняется быстро и туда надо ставить bigint, а если у вас таблица для FAQ (ЧАВО), то там и TINYINT может с головой хватить…

    Некоторые берут делают главные ключи не в 10-й системе исчисления, а в 36-ричной СИ (0-9 a-z) тогда меньше получается надо тратить бит на длину столца, но и возникают другие проблемы… В общем — не заморачивайтесь)


  14. Avatar
    не работает alter auto_increment » Программирование в удовольствие пишет:

    […] Особенности работы LAST_INSERT_ID() и AUTO_INCREMENT в MySQL Март11 […]


  15. Avatar
    koloboc пишет:

    Может я что-то не допонимаю, разъясните что не так в примере с LAST_INSERT_ID() ?
    В первой группе запросов имеем изначально пустые таблицы и вставляем сначала в одну потом в другую таблицу по одной записи, потом спрашиваем LAST_INSERT_ID(). Выдается =1.
    Это последний ID со второго запроса.

    Во втором примере также таблицы пусты и мы вставляем в первую таблицу запись и две записи во вторую… Спрашиваем LAST_INSERT_ID()…
    Выдает = 2.
    А что не так? разве не ожидаемо?
    Ни как не могу понять в чем особенность, которую описывает автор? чем он удивлен?

    «Какие еще приколы скрывает в себе эта функция, оказывается если вы делаете многократное добавление в рамках одного запроса, то ф-ция LAST_INSERT_ID() выдаст ИД только первой успешно добавленной записи!!! Пруфлинк.»
    По-моему своими примерами автор доказал, что эта фраза не верна!


не публикуется

пример

Оставить комментарий или два:

  

Облако тегов

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

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

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

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

Календарь

Март 2011
Пн Вт Ср Чт Пт Сб Вс
« Фев   Май »
 123456
78910111213
14151617181920
21222324252627
28293031  

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