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

Март11

Всем привет.

Причина по которому я написал эту статью — это неожиданное поведение ф-ции LAST_INSERT_ID() при выполнении запроса в котором я в поле с атрибутом AUTO_INCREMENT передавал значение.

LAST_INSERT_ID()

И так имеем таблички

01 Таблица авторов:
02
03 CREATE TABLE `author` (
04    `id` INT(10) NOT NULL AUTO_INCREMENT,
05    `name` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
06    `fam` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
07    `birthday` DATE NULL DEFAULT NULL,
08    PRIMARY KEY (`id`)
09 )

10 COLLATE='utf8_unicode_ci'
11 ENGINE=InnoDB
12 ROW_FORMAT=DEFAULT
13 AUTO_INCREMENT=1;
14
15 Таблица книг авторов:
16
17 CREATE TABLE `books` (
18    `id` INT(10) NOT NULL AUTO_INCREMENT,
19    `id_author` INT(10) NULL DEFAULT NULL,
20    `book` VARCHAR(500) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
21    PRIMARY KEY (`id`),
22    INDEX `id_author` (`id_author`)
23 )
24 COLLATE='utf8_unicode_ci'
25 ENGINE=InnoDB
26 ROW_FORMAT=DEFAULT
27 AUTO_INCREMENT=1;

Добавляем данные, обратите внимание на запросы тут :

1 INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Николай Николаевич', 'Носов', '2008-11-23');
2 SET @lastID := LAST_INSERT_ID();
3 INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Незнайка учится');
4 INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Незнайка-путешественник');

и тут :

1 INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (5, 'Артур Конан', 'Дойль', '1859-05-22');
2 SET @lastID := LAST_INSERT_ID();
3 INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир');
4 INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Шерлок Холмс');

Ну разница не только в значениях полей, а тут главная фишка, что при добавлении автора в первой тройке запросов, автоинкремент делал сама БД, а во второй тройке я принудительно указал номер в поле автоикремент, вот что получилось:

01 mysql> SELECT * FROM author;
02 +----+-------------------------------------+------------+------------+
03 | id | name                                | fam        | birthday   |
04 +----+-------------------------------------+------------+------------+
05 |  1 | Николай Николаевич               | Носов     | 2008-11-23 |
06 |  5 | Артур Конан                           | Дойль     | 1859-05-22 |
07 +----+-------------------------------------+------------+------------+
08 2 rows in set (0.00 sec)
09
10 mysql> SELECT * FROM books;
11 +----+-----------+-----------------------------------------------+
12 | id | id_author | book                                          |
13 +----+-----------+-----------------------------------------------+
14 |  1 |         1 | Незнайка учится                 |
15 |  2 |         1 | Незнайка-путешественник |
16 |  3 |         2 | Затерянный мир                   |
17 |  4 |         2 | Шерлок Холмс                       |
18 +----+-----------+-----------------------------------------------+
19 4 rows in set (0.00 sec)

Как вы заметили, с таблицей авторов все окей, 1 и 5 запись добавились с теми номерами ключей которые указала БД и я соот.
Во второй таблице — книги, все хуже. У книг с ИД 1 и 2 ИД_автора все ок, привязаны к автору с ИД1 в таблице авторов, а вот книги с ИД 3 и 4 привязаны к автору ИД которого в таблице авторов просто нет?!

Почему произошла такая ситуация. Разбираем.

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

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

Вот пример

1 TRUNCATE `author`;
2 TRUNCATE `books`;
3 INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Артур Конан', 'Дойль', '1859-05-22');
4 SET @lastID := LAST_INSERT_ID();
5 INSERT INTO `books` (`id`, `id_author`, `book`)
6 VALUES (NULL, @lastID, 'Затерянный мир'),
7 (NULL, @lastID, 'Шерлок Холмс');

Результат

1 mysql> SELECT LAST_INSERT_ID();
2 +------------------+
3 | LAST_INSERT_ID() |
4 +------------------+
5 |                1 |
6 +------------------+
7 1 row in set (0.00 sec)

А если сделать так

1 TRUNCATE `author`;
2 TRUNCATE `books`;
3 INSERT INTO `author` (`id`, `name`, `fam`, `birthday`) VALUES (NULL, 'Артур Конан', 'Дойль', '1859-05-22');
4 SET @lastID := LAST_INSERT_ID();
5 INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Затерянный мир');
6 INSERT INTO `books` (`id`, `id_author`, `book`) VALUES (NULL, @lastID, 'Шерлок Холмс');
7 SELECT LAST_INSERT_ID();

Результат

1 mysql> SELECT LAST_INSERT_ID();
2 +------------------+
3 | LAST_INSERT_ID() |
4 +------------------+
5 |                2 |
6 +------------------+
7 1 row in set (0.00 sec)

Век живи — век учись (© Народная мудрость).

В заключении еще решил разобрать поведение атрибута AUTO_INCREMENT.

AUTO_INCREMENT

Это такой атрибут который генерирует порядковый номер к указанному полю. Полей должно быть только с типом integer или float (TINYINTSMALLINTMEDIUMINTINTBIGINTFLOAT) и имеющий индекс типа (UNIQUEPRIMARYKEY). Причем у каждого из типа поля есть свой предел автоинкремента, например для поля TINYINT это значение 127 или 255 для TINYINT UNSIGNED и тд смотрите документацию. Каждая последующая запись будет +1 (по-дефолту) к максимальному числу до добавления в этом поле, наверное не так лучше сказать.
В MySQL есть вспомогательная БД information_schema в которой хранится необходимая информация о всех таблицах всех БД сервера. И вот именно там храниться следующий номер для автоинкремента, т.е. перед добавление записи в таблицу БД не ищет максимальный номер записи, на это ведь будет тратиться время, которое дорого.
В таблице может быть только одно поле которое имеет автоинкремент, и причем это поле не должно иметь дефолтного значения.
Если мы хотим указать какой-то свой номер для поля с автоинкрементом, то мы должны просто передать этому полю значение. Если значение уже есть в таблице, то будет ошибка если тип индекса в поле UNIQUEили PRIMARY, но есть тип индекса KEY то запись спокойно добавится.
Если по каким-то причинам нам надо указывать поле с автоинкрементом в запросе, но мы хотим чтобы этому полю было присвоено значение автоинкремента автоматом, то в это поле надо передать 0 (ноль)или NULL:

1 INSERT INTO`tablename`VALUE (0);
2 или
3 INSERT INTO`tablename`VALUE (NULL);
4 или
5 INSERT INTO`tablename`VALUE (NULL), (0); // два запроса на добавления

Если по каким-то причинам вы хотите добавить значение 0 в поля автоинкремента, для этого необходимо прописать в файле-настроке (my.ini / my.cnf) следующую строчку

1 sql-mode = NO_AUTO_VALUE_ON_ZERO

но делать это не рекомендуется, т.к. если сделать дамп БД и потом развернуть его на другом сервере, где нет такой настройки, то данные будут искажены.

Если по каким-то причинам вам необходимо сделать чтобы следующая запись начиналась с какого-нибудь друго числа, просто напишите команду:

1 ALTER TABLE `tablename` AUTO_INCREMENT = тут_пишем_нужное_число;

не забывайте об используемом типе поля, чтобы ваше число не вышло за рамки.

А например возникла задача делать автоинкремент не ++1 а через 5, т.е. 1,6,11 т.д. как быть в таком?
Для этого имеются две переменные auto_increment_increment и auto_increment_offset.auto_increment_increment — указывает шаг приращения, а auto_increment_offset указывает стартовую точку.
Для просмотра что у вас используется команда, которая показывает как у вас настроен автоинкремент:

1 mysql> SHOW VARIABLES LIKE 'auto_inc%';
2 +--------------------------+-------+
3 | Variable_name            | Value |
4 +--------------------------+-------+
5 | auto_increment_increment | 1     |
6 | auto_increment_offset    | 1     |
7 +--------------------------+-------+
8 2 rows in set (0.00 sec)

Поменять можно 2 путями — только на время работы сервера БД (до перезагрузки) и навсегда — смена конфигурационного файла

1 1 путь :
2 SET @@auto_increment_increment=5; SET @@auto_increment_offset=1;
3
4 2 путь в файле my.ini / my.cnf пишем строки :
5 auto_increment_increment=5;
6 auto_increment_offset=1;

Более детальней об изменении AUTO_INCREMENT читать тут.

http://blog.nagaychenko.com/2011/03/11/%D0%BE%D1%81%D0%BE%D0%B1%D0%B5%D0%BD%D0%BD%D0%BE%D1%81%D1%82%D0%B8-%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D1%8B-last_insert_id-%D0%B8-auto_increment-%D0%B2-mysql/

  One Response to “не работает alter auto_increment”

  1. Статья интересная! Но что то у меня не создается табличка, когда в запросе написано «`id` INT(10) NOT NULL AUTO_INCREMENT». Если эту строку убираю, то все работает как надо…

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

   
© 2012 Программирование в удовольствие Яндекс.Метрика Suffusion theme by Sayontan Sinha