This post comes from the first version of this blog.
Please send me an email if anything needs an update. Thanks!

Witajcie jeszcze raz. ;] Jak mogliście zauważyć, ostatnio znalazło się na moim blogu kilka wpisów na temat pewnych problemów z językiem SQL, a konkretnie kilku sytuacji brzegowych, w których uzyskanie interesujących nas danych okazało się nietrywialnym zadaniem. Nie inaczej będzie też dzisiaj, jako, że czytając różne materiały w serwisach związanych z programowaniem trafiłem na problem pobierania identyfikatora nowego rekordu bez wykonywania zbędnego zapytania INSERT. Nie namyślając się długo dopisałem ten problem do listy pomysłów, a dzisiaj doczekał się on realizacji. ;] Zapraszam do lektury.

Wstęp.

Żeby zobrazować problem, przyjmijmy przykładową strukturę bazy danych. Załóżmy, że mamy tabelę użytkowników, przykładowo px_users:

1
2
3
4
5
6
7

CREATE TABLE IF NOT EXISTS `px_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`login` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`nicename` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

Załóżmy też, że w rzeczonej tabeli znajduje się kilka rekordów:

Mając tak nakreśloną sytuację, możemy przejść do opisu celu, jaki będziemy chcieli osiągnąć.

Problem.

Problem polega na tym, że takie dane, jak wartość pola AUTO_INCREMENT nie jest aż tak jawna i prosta do pobrania, jakby mogło się wydawać. Nie jest to też AŻ takie trudne, aczkolwiek moim zdaniem powinno to zostać trochę lepiej rozwiązane.

Należy zauważyć, że problemu nie da się idealnie rozwiązać za pomocą zapytań SQL pobierających najwyższą wartość klucza głównego tabeli i innych sposobów bazujących na analizie aktualnej zawartości jej zbiorów.

Wystarczy prosty przykład: dodajemy tysiąc rekordów, także wartość następnego identyfikatora zostanie ustawiona na 1006 - i taki zostanie wpisany. Usuńmy jednak np. 10 ostatnich rekordów, a następnie zażądajmy wyników poniższego zapytania:

1
2

SELECT MAX(id)
FROM px_users;

Krótka analiza tego zapytania w głowie zapewne zasugeruje odpowiedź po krótkiej chwili - wynikiem będzie 995, a więc nasz INSERT zostanie wpisany z nieodpowiednią wartością (995 + 1). Oczywiście w tym przypadku nie zostanie wskazany żaden błąd, ponieważ wspomniane zapytanie tylko “wypełni lukę” w kolejności klucza głównego, jednak nie jest to rozwiązanie bezpieczne.

Dodając do tego problem z sytuacją, w której dwa osobne procesy dostają ten sam wynik (dwa zbliżone w czasie zapytania) otrzymujemy istotny konflikt, który zwyczajnie musi zostać rozwiązany w inny sposób.

Rozwiązania.

Rozwiązań jest tradycyjnie kilka, stąd i odpowiedni tytuł tego "rozdziału". Celem tego wpisu jest pokazanie, w jaki sposób można uzyskać poprawną wartość kolejnego identyfikatora bez dodatkowego wstawiania rekordów, stąd pomijam trywialne rozwiązanie z wykonaniem zapytania INSERT i użyciem jednej z równoważnych funkcji / metod typu lastInsertId().

Zobaczmy więc, jakie są nasze możliwości:

A oto ich definicje:

Zapytanie SELECT do tabeli information_schema.TABLES.

Aby uzyskać potrzebne dane korzystając z tej metody, należy posłużyć się bazą danych information_schema. Zawiera ona wszelkie dane na temat samego serwera SQL, jego struktur, ustawień, baz danych, tabel i wielu, wielu innych rzeczy. Polecam samodzielne przejrzenie jej struktury i zbadania zawartych w tabelach danych, jako, że jest to często źródło wielu inspiracji na temat tego, co można w MySQL monitorować.

Nas interesuje tabela TABLES, która, jak sama nazwa wskazuje, zawiera dane na temat tabel. Jedną z kolumn tej tabeli jest właśnie AUTO_INCREMENT, a my postaramy się tą wartość pobrać. Gotowe zapytanie:

1
2
3

SELECT `AUTO_INCREMENT`
FROM `TABLES`
WHERE `TABLE_SCHEMA` = "[nazwa bazy danych]" AND `TABLE_NAME` = "[nazwa tabeli]"

W wyniku otrzymamy oczekiwaną liczbę.

Zapytanie SHOW TABLE STATUS.

W tym przypadku jest trochę trudniej, ponieważ nie otrzymujemy gotowej liczby, musimy ją sobie jeszcze “wyłuskać” z wyników zapytania. Zapytanie, którego potrzebujemy:

1

SHOW TABLE STATUS LIKE "[nazwa tabeli]"

Następnie fragment kodu PHP [dla wygody posłużę się przykładem z PDO]:

1
2
3
4

$sth = $dbh->prepare($query); // zapytanie powyżej
$sth->execute();
$data = $sth->fetch(PDO::FETCH_ASSOC);
$insertId = $data['Auto_increment'];

I wszystko kończy się szczęśliwie jak w pierwszym sposobie - w zmiennej $insertId mamy oczekiwaną przez nas wartość.

Wnioski.

Żebym nie został posądzony o propagowanie niepoprawnych technik programowania muszę wspomnieć o kilku istotnych rzeczach.

Nie jest dobrą praktyką pobieranie identyfikatora kolejnego rekordu tylko po to, żeby zaraz potem wykonać zapytanie umieszczające rekord pod tą pozycją. Problemy związane z potencjalnym odczytem tej samej wartości AUTO_INCREMENT przez dwie instancje skryptu w tym samym momencie mogą doprowadzić do błędów integralności bazy danych i innych problemów które będą się mściły aż do momentu zastosowania poprawnej konwencji.

Jeśli zależy nam na późniejszym wykonywaniu operacji na rekordzie, zalecaną praktyką jest wstawienie go do bazy danych i operowanie na “pewnych” danych, zamiast tych “potencjalnych”. Przy dostatecznie dużym obciążeniu może się okazać, że dane, które pobraliśmy pół sekundy temu i obrobiliśmy w skrypcie są “lata świetlne” do tyłu, także miejmy to na uwadze podczas programowania.

Inną kwestią jest także pobieranie aktualnej wartości pola nowego identyfikatora dla tworzenia relacji w tabelach. Po raz kolejny - jest to zły pomysł ze względu na te same problemy. Możemy stworzyć rekordy i przypisać je do identyfikatorów które nie istnieją, a mają dużą szansę zostać nadpisane w trakcie wykonywania zapytań, lub przez działający równolegle skrypt. Nie róbcie tego. ;]

Podsumowanie.

To by było na tyle w kwestii treści wpisu - z tego co zauważyłem, wpisy o problemach z SQLem przyciągają Waszą uwagę i "prowokują" do ciekawych dyskusji, także mam nadzieję, że i tym razem mnie nie zawiedziecie. Czekam na Wasze opinie i gwiazdki. ;]