Март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 по тематике данной статьи.
Статья просмотренна 236505 раз, зашло посетителей 71056
