MySQL NA DUPLICATE KEY - identyfikator ostatniego wstawienia?

135

Mam następujące zapytanie:

INSERT INTO table (a) VALUES (0)
  ON DUPLICATE KEY UPDATE a=1

Chcę mieć identyfikator wkładki lub aktualizacji. Zwykle wykonuję drugie zapytanie, aby to uzyskać, ponieważ uważam, że insert_id () zwraca tylko 'wstawiony' identyfikator, a nie zaktualizowany identyfikator.

Czy istnieje sposób na WSTAWIENIE / AKTUALIZACJĘ i pobranie identyfikatora wiersza bez uruchamiania dwóch zapytań?

mysql insert-id

— thekevinscott
źródło

3

Zamiast przypuszczać, dlaczego sam tego nie przetestujesz? SQL w powyższej edycji działa, a przez moje testy jest szybsze niż wychwycenie niepowodzenia wstawiania, użycie INSERT IGNORE lub wybranie, aby najpierw sprawdzić, czy istnieje duplikat.

— Michael Fenwick

4

OSTRZEŻENIE: Proponowane rozwiązanie działa, ale wartość auto_increment nadal rośnie, nawet jeśli nie ma wstawki. Jeśli zduplikowany klucz zdarza się często, możesz chcieć wykonać alter table tablename AUTO_INCREMENT = 0;powyższe zapytanie, aby uniknąć dużych luk w wartościach identyfikatorów.

— Frank Forte

180

Sprawdź tę stronę: https://web.archive.org/web/20150329004325/https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
U dołu strony wyjaśniają, w jaki sposób można nadać LAST_INSERT_ID znaczący dla aktualizacji, przekazując wyrażenie do tej funkcji MySQL.

Z przykładu dokumentacji MySQL:

Jeśli tabela zawiera kolumnę AUTO_INCREMENT i INSERT ... UPDATE wstawia wiersz, funkcja LAST_INSERT_ID () zwraca wartość AUTO_INCREMENT. Jeśli zamiast tego instrukcja aktualizuje wiersz, LAST_INSERT_ID () nie ma znaczenia. Możesz jednak obejść ten problem, używając LAST_INSERT_ID (wyrażenie). Załóżmy, że ten identyfikator to kolumna AUTO_INCREMENT. Aby LAST_INSERT_ID () miało znaczenie dla aktualizacji, wstaw wiersze w następujący sposób:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;

— fredrik
źródło

2

Jakoś przegapiłem to, patrząc na tę stronę. Tak więc część aktualizacji wygląda następująco: UPDATE id = LAST_INSERT_ID (id) I to działa świetnie. Dzięki!

— thekevinscott

7

Mówi się, że funkcja php mysql_insert_id () zwraca poprawną wartość w obu przypadkach: php.net/manual/en/function.mysql-insert-id.php#59718 .

— jayarjo

2

@PetrPeller - cóż, bez patrzenia na wewnętrzne funkcje MySQL, prawdopodobnie oznacza to, że wygeneruje wartość, ale ta wartość nie jest związana z zapytaniem, które właśnie uruchomiłeś. Innymi słowy, problem, który jest trudny do debugowania.

— Jason,

13

Po 5.1.12 podobno nie jest to już konieczne, jednak dzisiaj znalazłem wyjątek od tej reguły. Jeśli masz pakiet autoinkrementacji i unikalny klucz na przykład adres e-mail oraz wyzwalacze „przy zduplikowanej aktualizacji” oparte na adresie e-mail, pamiętaj, że last_insert_id NIE będzie wartością automatycznego zwiększania zaktualizowanego wiersza. Wydaje się, że jest to ostatnio wstawiona wartość autoinkrementacji. To robi ogromną różnicę. Obejście jest takie samo, jak tutaj, a mianowicie użycie id = LAST_INSERT_ID (id) w zapytaniu aktualizującym.

— sckd

1

W 5.5 @ sckd komentarz nadal jest prawdziwy.

— e18r,

37

Dokładniej, jeśli jest to pierwotne zapytanie:

INSERT INTO table (a) VALUES (0)
 ON DUPLICATE KEY UPDATE a=1

a „id” jest kluczem podstawowym z automatyczną inkrementacją, niż byłoby to działające rozwiązanie:

INSERT INTO table (a) VALUES (0)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), a=1

Wszystko jest tutaj: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Jeśli tabela zawiera kolumnę AUTO_INCREMENT i INSERT ... UPDATE wstawia wiersz, funkcja LAST_INSERT_ID () zwraca wartość AUTO_INCREMENT. Jeśli zamiast tego instrukcja aktualizuje wiersz, LAST_INSERT_ID () nie ma znaczenia. Możesz jednak obejść ten problem, używając LAST_INSERT_ID (wyrażenie). Załóżmy, że ten identyfikator to kolumna AUTO_INCREMENT.

— Aleksandar Popovic
źródło

8

Tak, zobacz zaakceptowaną odpowiedź na to samo, co powiedziałeś. Nie ma potrzeby przywracania 3-letnich postów. Mimo wszystko dziękuję za twój wysiłek.

— fancyPants

1

@tombom jedynym powodem, dla którego opublikowałem tę odpowiedź, jest to, że zaakceptowana odpowiedź jest nieprawidłowa - nie zadziała, jeśli nie ma nic do zaktualizowania.

— Aleksandar Popovic

2

Możesz spojrzeć na REPLACE, które zasadniczo polega na usunięciu / wstawieniu, jeśli rekord istnieje. Ale to zmieniłoby pole automatycznego inkrementacji, jeśli jest obecne, co mogłoby spowodować zerwanie relacji z innymi danymi.

— Brent Baisley
źródło

1

Ach tak - Szukam czegoś, co nie będzie pozbyć poprzedniego identyfikatora użytkownika

— thekevinscott

Może to być niebezpieczne również dlatego, że może również spowodować usunięcie innych powiązanych danych (z powodu ograniczeń).

— Serge

2

Nie wiem, jaka jest twoja wersja MySQL, ale w przypadku InnoDB wystąpił błąd z autoinc

błąd w 5.1.20 i poprawiony w 5.1.23 http://bugs.mysql.com/bug.php?id=27405

błąd w 5.1.31 i poprawiony w 5.1.33 http://bugs.mysql.com/bug.php?id=42714

— Hugues Van Landeghem
źródło

1

Natknąłem się na problem, gdy NA DUPLICATE KEY UPDATE id = LAST_INSERT_ID (id) zwiększ klucz podstawowy o 1. Więc id następnego wejścia w sesji zostanie zwiększony o 2

— Oleksii Zymovets
źródło

0

Warto zauważyć, a to może być oczywiste (ale i tak powiem to dla jasności tutaj), że polecenie REPLACE spowoduje zdmuchnięcie istniejącego pasującego wiersza przed wstawieniem nowych danych. W PRZYPADKU DUPLICATE KEY UPDATE zaktualizuje tylko określone kolumny i zachowa wiersz.

Z instrukcji :

REPLACE działa dokładnie tak samo, jak INSERT, z wyjątkiem tego, że jeśli stary wiersz w tabeli ma taką samą wartość jak nowy wiersz dla indeksu PRIMARY KEY lub UNIQUE, stary wiersz jest usuwany przed wstawieniem nowego wiersza.

— Greg K.
źródło

0

Istniejące rozwiązania działają, jeśli używasz autoinkrementacji. Mam sytuację, w której użytkownik może zdefiniować prefiks i powinien ponownie uruchomić sekwencję na 3000. Z powodu tego zróżnicowanego prefiksu nie mogę użyć funkcji autoincrement, co powoduje, że last_insert_id jest pusty dla wstawień. Rozwiązałem to następująco:

INSERT INTO seq_table (prefix, id) VALUES ('$user_prefix', LAST_INSERT_ID(3000)) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id + 1);
SELECT LAST_INSERT_ID();

Jeśli prefiks istnieje, zwiększy go i zapełni last_insert_id. Jeśli przedrostek nie istnieje, wstawi przedrostek o wartości 3000 i wypełni last_insert_id wartością 3000.

— Aaron
źródło