Очистка LAST_INSERT_ID() перед вставкой, чтобы узнать, что получено из моей вставки

Question

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

Предположим, я использую соединение из пула, который мог вставить строку до того, как я получил соединение, и я выполняю условную вставку:

insert into mytable (colA)
select 'foo' from bar
where <some condition>;
select LAST_INSERT_ID();

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

Один из способов, о котором я подумал:

@previousId := LAST_INSERT_ID();
insert into mytable (colA)
select 'foo' from bar
where <some condition>;
select if(LAST_INSERT_ID() != @previousId, LAST_INSERT_ID(), null);

Есть ли способ "очистить" LAST_INSERT_ID() значение, так что я знаю, что это новое значение, вызванное моим SQL, если возвращается ненулевое значение?

Answer 1

Использование ROW_COUNT() чтобы определить, была ли ваша попытка условной вставки успешной, а затем вернуться LAST_INSERT_ID() или значение по умолчанию на основе этого:

select IF(ROW_COUNT() > 0, LAST_INSERT_ID(), 0);

Answer 2

Я согласен с ответом @Digital Chris, что вы не должны определять, была ли вставка успешной или неудачной, проверяя значение, возвращаемое LAST_INSERT_ID(): есть более прямые маршруты, такие как количество затронутых строк. Тем не менее может существовать некоторое требование для получения "чистого" значения из LAST_INSERT_ID(),

Конечно, одна проблема с вашим предлагаемым решением (сравнения со значением перед вставкой) заключается в том, что может случиться так, что вставка прошла успешно, и что назначенное ей автоматически увеличиваемое значение совпадает с предыдущей вставкой (предположительно на другой стол). Таким образом, сравнение может привести к предположению, что вставка не удалась, тогда как на самом деле это удалось.

Я рекомендую, если это возможно, избегать использования LAST_INSERT_ID() Функция SQL в предпочтении для mysql_insert_id() Вызов API (через ваш драйвер). Как объяснено в документации для последнего:

mysql_insert_id() возвращается 0 если предыдущее утверждение не использует AUTO_INCREMENT значение. Если вам нужно сохранить значение на потом, обязательно позвоните mysql_insert_id() сразу после утверждения, которое генерирует значение.

 [ делеция ] 

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

В любом случае, как указано в LAST_INSERT_ID(expr):

Если expr дается в качестве аргумента LAST_INSERT_ID() значение аргумента возвращается функцией и запоминается как следующее значение, возвращаемое LAST_INSERT_ID(),

Поэтому перед выполнением вашего INSERT Вы можете сбросить с помощью:

SELECT LAST_INSERT_ID(NULL);

Это также должно сбросить значение, возвращаемое mysql_insert_id(), хотя документация предполагает призыв к LAST_INSERT_ID(expr) должно происходить в течение INSERT или же UPDATE утверждение - может потребоваться проверка для проверки. В любом случае, создание такого неоперативного оператора должно быть довольно тривиальным, если это необходимо:

INSERT INTO my_table (my_column) SELECT NULL WHERE LAST_INSERT_ID(NULL);

Стоит отметить, что можно также установить identity а также last_insert_id системные переменные (однако они влияют только на значение, возвращаемое LAST_INSERT_ID() и не mysql_insert_id()):

SET @@last_insert_id := NULL;

Answer 3

Вы предполагаете, что вы можете получить предыдущий LAST_INSERT_ID()Но практически я не понимаю, где это могло произойти. Если вы делаете условную вставку, вы должны проверить, была ли она успешной, прежде чем предпринимать следующие шаги. Например, вы всегда будете использовать ROW_COUNT() проверить вставленные / обновленные записи. Псевдо-код:

insert into mytable (colA)
select 'foo' from bar
where <some condition>;

IF ROW_COUNT() > 0
    select LAST_INSERT_ID();
    -- ...use selected last_insert_id for other updates/inserts...
END IF;

Answer 4

Поскольку LAST_INSERT_ID() полон ошибок, я использую AFTER INSERT триггер для записи идентификатора в таблицу журнала в соответствии с проблемой, которую я пытаюсь решить.

В вашем сценарии, поскольку вставка является условной, используйте идентификатор unqiue, чтобы "пометить" вставку, а затем проверьте наличие этого тега в журнале вставки. Если идентификационный тег присутствует, произошла ваша вставка, и у вас есть вставленный идентификатор. Если идентификационный тег отсутствует, вставка не произошла.

Реализация ссылок

DELIMITER $$
CREATE TRIGGER MyTable_AI AFTER INSERT ON MyTable FOR EACH ROW
BEGIN
    INSERT INTO MyTable_InsertLog (myTableId, ident) VALUES (NEW.myTableId, COALESCE(@tag, CONNECTION_ID()));
END $$
DELIMITER ;

CREATE TABLE MyTable_InsertLog (
    myTableId BIGINT UNSIGNED PRIMARY KEY NOT NULL REFERENCES MyTable (myTableId),
    tag CHAR(32) NOT NULL
);

Пример использования

SET @tag=MD5('A');
INSERT INTO MyTable SELECT NULL,colA FROM Foo WHERE colA='whatever';
SELECT myTableId FROM MyTable_InsertLog WHERE tag=@tag;
DELETE FROM MyTable_InsertLog WHERE tag=@tag;

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

Answer 5

LAST_INSERT_ID() Функция имеет довольно узкую сферу применения: так как MySQL не поддерживает SQL SEQUENCEs, это используется для создания транзакции, которая INSERTДанные последовательно помещаются в несколько таблиц, если одна ссылается на суррогатный ключ из другой таблицы:

CREATE TABLE foo (id INTEGER PRIMARY KEY AUTO_INCREMENT, value INTEGER NOT NULL);
CREATE TABLE bar (id INTEGER PRIMARY KEY AUTO_INCREMENT, fooref INTEGER NOT NULL);
INSERT INTO foo(value) VALUES ('1');
INSERT INTO bar(fooref) VALUES (LAST_INSERT_ID());

Если вы действительно хотите использовать это в условной вставке, вам нужно сделать второй INSERT условно, а также путем объединения вставленных значений против SELECT от первой INSERT и впоследствии выбрасывая лишние столбцы (так что второй INSERT также имеет ноль или один ряд).

Я бы посоветовал против этого, хотя. Условная вставка таким образом уже несколько хрупкая, и ее построение не добавит стабильности вашему приложению.