Как повлиять на last_insert_id() из триггера (MySQL/MariaDB)?

Question

[Sinot]

Существует такая таблица (InnoDB обязателен):

CREATE TABLE `tbl` (
  `id1` int(11) NOT NULL AUTO_INCREMENT,
  `id2` int(11) NOT NULL,
  PRIMARY KEY (`id1`,`id2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Хочу добиться собственной нумерации для каждой группы id2, как тут для MyISAM.

Для этого добавил триггер:

CREATE DEFINER=`root`@`localhost` TRIGGER `db`.`tbl_BEFORE_INSERT` BEFORE INSERT ON `tbl` FOR EACH ROW BEGIN
    SET NEW.id1 = (SELECT COALESCE(MAX(id1) + 1, 1) FROM `tbl` WHERE id2 = NEW.id2);
END

Все отлично работает, но last_insert_id() не возвращает NEW.id1, так как инкремент не сработал.

Собственно как заставить last_insert_id() выдать значение, которое я хочу?

Пробовал в триггере не назначать NEW.id1, а изменить INCREMENT таблицы:

SET @id = NULL;
SELECT COALESCE(MAX(id1) + 1, 1) INTO @id FROM `tbl` WHERE id2 = NEW.id2;
ALTER TABLE `tbl` AUTO_INCREMENT = @id;

И это не сработало, так как ALTER TABLE завершает транзакцию, что запрещено в триггерах.

Вместо ALTER TABLE пробовал SET @@auto_increment_offset=@id, но он (я так понял) оказывает влияние при создании таблицы.

Comments

[Sinot]

Как выяснилось, у функции LAST_INSERT_ID() есть параметр, который задает значение, выдаваемое текущим и следующим вызовом функции.

Но после завершения работы триггера значение не сохраняется, что не подходит для решения проблемы.

[Sinot]

Хотя вместе с AUTO_INCREMENT можно убрать и NOT NULL, тогда и DEFAULT не понадобиться.

Answer 1

[Леонид Сысолетин]

Начнём с того, что AUTO_INCREMENT отрабатывает после вставки, так что менять его надо AFTER INSERT. Попутно замечу, что в AFTER UPDATE менять значение поля нельзя.
Далее, апдейт в AFTER INSERT тоже запрещён.
Итого, практически единственным решением в указанном случае будет хранимочка, которая будет менять нужные колонки, заворачиваясь в транзакцию, для "атомарности" операции.

Но главный вопрос в том, нахрена ставить AUTO_INCREMENT, если ты сам лучше сервера знаешь, какое значение должно быть у вновь вставляемой записи?

Comments

[Sinot]

В данном случае AUTO_INCREMENT вообще не отрабатывается ни до, ни после, так как я явно задаю значение поля в триггере. Собственно по этому таблица слегка видоизменилась: для первого поля AUTO_INCREMENT - убран, но так как он спасал от ошибки при передачи NULL пришлось добавить значение DEFAULT.

Жаль что придется писать запрос вместо LAST_INSERT_ID() или процедуру для вставки.

Спасибо.

[Sinot]

Хотя вместе с AUTO_INCREMENT можно убрать и NOT NULL, тогда DEFAULT не нужен.