W ramach zabawy z domowym projektem w Pythonie stawiam sobie dla niego bazkę na MySQL-u. W sumie to nie miałem okazji dłubać przy RDBMS-ach od dłuższego czasu i chyba nie było za czym tęsknić.

Mam sobie tabelkę (uproszczoną) do trzymania tagów:

CREATE TABLE Tags (
    TagID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    Name VARCHAR(32),
    PRIMARY KEY (TagID),
    UNIQUE(Name)
);

I teraz chcę sobie zaimplementować mechanizm "wstaw jeśli nie istnieje, zwróć aplikacji jego id". Można by to rozdzielić na dwa osobne zapytania, ale to biedne strasznie, więc sprawdźmy najpierw gołe INSERT IGNORE.

cursor.execute(
    """INSERT IGNORE INTO Tags(Name) VALUES(%s)""",
    ("Test",)
)
conn.commit()
print(cursor.lastrowid)

No i niestety źle, bo lastrowid jest uzupełniany tylko jeśli coś rzeczywiście wstawimy, jeżeli natomiast dany tag już istnieje to otrzymana wartość będzie 0. Nie oto chodzi.

Internet podsuwa inne rozwiązanie. Używamy ON DUPLICATE KEY i ustawiamy sobie ID następująco:

cursor.execute(
    """INSERT INTO Tags(Name) VALUES(%s) ON DUPLICATE KEY UPDATE TagID=LAST_INSERT_ID(TagID)""",
    ("Test",)
)
conn.commit()
print(cursor.lastrowid)

I teraz już działa - jak wstawiamy do tabeli to dostajemy ostatni id, a jak mamy duplikat, to również dostajemy jak trzeba. Pieknie.

No ale pojawia się kolejny problem, ponieważ AUTO_INCREMENT odpala się za każdym razem jak to wywołujemy, czy wstawimy czy nie. Parokrotna próba wstawienia duplikatu nabija licznik, a że TINYINT UNSIGNED to nędzny zakres 0-255 (ustawiłem mały bo ja nie mam dużo tagów), więc się wyczerpał w mgnieniu oka. Co teraz?

Jedna z opcji mówi, że po prostu należy dziurami między idkami powiększając zakres zmiennej. Najprościej. Bajty są tanie. Tym niemniej, mam garść tagów, wstawianie nowych będzie esktremalnie rzadkie, usuwane najprawdopodobniej nie będą nigdy, za to będę miał miliony testów na duplikację do przeprowadzenia. Te wszystkie ID-ki mi mogą mocno spuchnąć. I pewnego dnia może się okazać, że wylazłem poza zakres. Jakoś tak wolałbym mieć gęstsze upakowanie tych wartości.

Można by przenieść zarządzanie idk-ami piętro wyżej, do samej aplikacji. Do zrobienia, ale... serio...? Po to mam bazę bym się nie musiał babrać z takimi rzeczami.

Internet mówi, że można zmodyfikować zmienną innodb_autoinc_lock_mode w configu serwera, która zmienia to zachowanie kosztem spadku wydajności. Trzeba to wrzucić do /etc/mysql/conf.d/mysql.cnf.

[mysqld]
innodb_autoinc_lock_mode = 0

Można sprawdzić wartość takiej opcji tak:

mysql> SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          0 |
+----------------------------+

W rezultacie dziury w id-ach się już nie tworzą - niestety, wyłącznie w ramach stosowania INSERT IGNORE. Podejście z ON DUPLICATE KEY UPDATE już dziury robi. Jako że potrzebuję by zwracać ostatni id, więc podejście to też się nie sprawdza.

Grzebiąc dalej po necie natykam się na wypowiedz na Stacku, gdzie kolo mówi po prostu, że nie należy tak ufać AUTO_INCREMENT. Ma ono gwarantować aby kolejne idki rosły i nic poza tym, jeżeli chce się je "zagęścić", to trzeba podejść do tego inaczej. Wygląda na to, że inni ludzie również się wyburaczają o ten sam mechanizm.

To se pomyślałem "a może trigger...?". No to sprawdźmy. Najpierw zdjąłem ze schemy AUTO_INCREMENT po czym wstawiłem trigger:

CREATE TRIGGER UpdateID BEFORE INSERT ON Tags
FOR EACH ROW SET NEW.TagID = (SELECT COALESCE(MAX(TagID), 0) + 1 FROM Tags);

I to cudo faktycznie ręcznie uzupełnia ID bez żadnej dziury. Trzeba tylko wywołać INSERT INTO z wartością 0 dla TagID (bo i tak trigger ją zamieni) i niezależnie czy operacja się udała (bo nie było jeszcze takiego tagu) czy nie (bo duplikat) nie ma żadnego puchnięcia. Jak dodaję unikalną wartość to zawsze będzie miała ID-ek o jeden większy.

Tyle że jak to teraz wywołam z Pythonie:

cursor.execute(
    """INSERT INTO Tags(TagID, Name) VALUES(%s, %s) ON DUPLICATE KEY UPDATE TagID=LAST_INSERT_ID(TagID)""",
    (0, "Drive",)
)
conn.commit()
print(cursor.lastrowid)

To efekt jest dokładnie na odwrót jak na samym początku, jak sprawdzałem gołe INSERT IGNORE. Wtedy jak coś wstawiłem to dostawałem nowy idek, a jak miałem duplikat to dostawałem zero. Teraz jeśli wstawiłem element to zwraca mi 0 (bo taka wartość została użyta, a o triggerze tu nie wiadomo), a jeśli mam duplikat to dostaję poprawny id.

Aaaale. Co się okazuje. Jak użyłem takiego samego triggera ale zostawiłem w schemie AUTO_INCREMENT to nagle działa tak jak chciałem. Kod Pythona, już bez jawnego wstawiania TagID:

cursor.execute(
    """INSERT INTO Tags(Name) VALUES(%s) ON DUPLICATE KEY UPDATE TagID=LAST_INSERT_ID(TagID)""",
    ("Test",)
)
conn.commit()
print(cursor.lastrowid)

Zwraca nowy id dla nowych elementów i poprawny id dla duplikatów. Hę...? Może czegoś nie widzę, ale na oko babka umarła wszystko wydaje się zasuwać jak trzeba. No dobra, niech i tak zostanie. Aplikowana stomatologia hipologiczna.

A tak w ogóle to wszystko przy założeniu, że nie ma wstawiania grupowego które to zagadnienie dodatkowo komplikuje całe zagadnienie.

Important
If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

Tak że dla moich tagów to nie problem, ale jak widać moje zabawy nie wyczerpują tematu. Aczkolwiek chyba wystarczy mi tłuczenia się z bazą na jeden dzień.