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

Ответ 1

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

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

Ответ 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() сразу после инструкции, которая генерирует значение.

[ deletia ]

Причина различий между 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 вы могли бы reset с помощью:

SELECT LAST_INSERT_ID(NULL);

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

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;

Ответ 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;

Ответ 4

Так как LAST_INSERT_ID() полный gotchas, я использую 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 [email protected];
DELETE FROM MyTable_InsertLog WHERE [email protected];

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

Ответ 5

Функция LAST_INSERT_ID() имеет довольно узкую область применения: поскольку MySQL не поддерживает SQL SEQUENCE s, это используется для создания транзакции, в которой 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 также имеет нуль или одну строку).

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