Pola typu autoincrement, serial czy sekwencje w bazach danych bardzo dobrze spełniają swoją rolę.
Pomagają i ułatwiają życie programiście. Często zdarza się jednak tak, że potrzebujemy id właśnie wstawionego rekordu. Np. jako klucza obcego przy wstawianiu rekordu do innej tabeli.
Opiszę metody na pobranie id w bazach MySQL, Oracle oraz PostrgeSQL, przykłady stworzę w PHP.
Na potrzeby artykułu stworzę specyficzną dla każdej z baz tabelę studenci.
MySQL
Skrypt tworzący tabelę:
CREATE TABLE STUDENCI ( ID INT AUTO_INCREMENT, IMIE VARCHAR(20) , NAZWISKO VARCHAR(30) , PRIMARY KEY (ID) );
Aby uzyskać w kolumnie ID efekt autonumerowania, używamy słowa kluczowego AUTO_INCREMENT przy definiowaniu tabeli.
Skrypt PHP realizujący zadanie dla MySQL:
// połączenie z bazą $link = mysql_connect("localhost", "test", "test"); // sprawdzenie połączenia if ($link) echo "Polaczony."; else die("Blad bazy danych."); // wybór schematu mysql_select_db("test", $link); // przygotwanie zapytania $sql = "INSERT INTO STUDENCI(IMIE,NAZWISKO) VALUES('BRUCE','LEE')"; // wykonanie zapytania mysql_query($sql); // pobranie id wstawionego rekordu // metoda pierwsza // przygotowanie zapytania $sql = "SELECT LAST_INSERT_ID()"; // wykonanie zapytania $wynik = mysql_query($sql); // odczytanie wyniku $id = mysql_fetch_assoc($wynik); // przepisanie id z tablicy do zmiennej $id = $id['LAST_INSERT_ID()']; // metoda druga $id = mysql_insert_id(); // wyświetlenie wyników echo "<br>Id dodanego rekordu: ".$id; // zamkniecie polaczenia mysql_close($link);
W przykładzie przedstawiłem dwie metody pobrania id.
Pierwsza niezależna od języka PHP: wykonanie zapytania SELECT LAST_INSERT_ID(). Jest to wbudowana w MySQL funkcja zwracająca ostatnio wygenerowaną wartość pola z własnością AUTO_INCREMENT.
Druga metoda to użycie funkcji języka PHP mysql_insert_id().
Właściwie obie metody te są tożsame. W pierwszym przypadku to ja, ‚ręcznie’ wykonałem zapytanie SELECT LAST_INSERT_ID(), w drugim PHP zrobiło to za mnie.
Oracle
Skrypt tworzący tabelę:
CREATE TABLE STUDENCI ( ID NUMBER , IMIE VARCHAR(20), NAZWISKO VARCHAR(30), PRIMARY KEY (ID) );
Aby w Oracle uzyskać autonumerowanie musimy stworzyć sekwencję.
Skrypt tworzący sekwencję:
CREATE SEQUENCE STUDENCI_ID_SEQ MINVALUE 1 MAXVALUE 9999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE;
Skrypt PHP realizujący zadanie dla Oracle:
// połączenie z bazą $link = oci_connect('test', 'test','TEST.WORLD'); // sprawdzenie połączenia if ($link) echo 'Polaczony.'; else die('Blad bazy danych.'); // przygotwanie zapytania $sql = "SELECT STUDENCI_ID_SEQ.NEXTVAL FROM DUAL"; // wykonanie zapytania $stmt = oci_parse($link, $sql); $result = oci_execute($stmt); // odczyt danych do tablicy $id = oci_fetch_array($stmt); // przepisanie id z tablicy do zmiennej $id = $id[0]; // przygotwanie zapytania $sql = "INSERT INTO STUDENCI VALUES (". $id .",'BRUCE','LEE')"; // wykonanie zapytania $stmt = oci_parse($link, $sql); oci_execute($stmt); // wyświetlenie wyników echo "<br>Id dodanego rekordu: ".$id; // zamkniecie polaczenia oci_free_statement($stmt); oci_close($link);
W przypadku Oracle sprawa jest nieco inna. Pierwszą rzeczą jest odczytanie za pomocą zapytania
SELECT STUDENCI_ID_SEQ.NEXTVAL FROM DUAL
kolejnego numeru z sekwencji. Dopiero po tym dodamy rekord do bazy. Gdybyśmy nie potrzebowali id, wstawienie rekordu wyglądałoby tak:
INSERT INTO STUDENCI VALUES (STUDENCI_ID_SEQ.NEXTVAL,'BRUCE','LEE')
Wywoływanie sekwencji ręcznie nie byłoby potrzebne.
PostgreSQL
Skrypt tworzący tabelę:
CREATE TABLE STUDENCI ( ID SERIAL , IMIE VARCHAR(20), NAZWISKO VARCHAR(30), PRIMARY KEY (ID) )
W PostgreSQL autonumerowaną kolumnę uzyskujemy wykorzystując typ danych SERIAL. PostgreSQL podobnie jak Oracle wykorzystuje sekwencje. Rożnica polega na automatycznym tworzeniu sekwencji w momencie tworzenia tabeli.
Skrypt PHP realizujący zadanie dla PostgreSQL:
// połączenie z bazą $link = pg_connect("host=localhost port=5432 dbname=test user=test password=test"); // sprawdzenie połączenia if ($link) echo "Polaczony."; else die("Blad bazy danych."); // przygotwanie zapytania $sql = "INSERT INTO STUDENCI(IMIE,NAZWISKO) VALUES('BRUCE','LEE') RETURNING ID"; // wykonanie zapytania $result = pg_query($link, $sql); // odczyt danych do tablicy $id = pg_fetch_array($result); // przepisanie id z tablicy do zmiennej $id = $id[0]; // wyświetlenie wyników echo "<br>Id dodanego rekordu: ".$id; // zamkniecie polaczenia pg_close($link);
W PostgreSQL zadanie można wykonać w najbardziej elegancji według mnie sposób. Do zapytania wstawiającego rekord dodajemy klauzulę returning oraz nazwę kolumny, którą chcemy zwrócić.
INSERT INTO STUDENCI(IMIE,NAZWISKO) VALUES('BRUCE','LEE') RETURNING ID
Wykonanie tego zapytania spowoduje dodanie rekordu bo tabeli oraz zwrócenie id dodanego rekordu. Zwykle zapytania typu insert nie zwracają danych, zwracają status wykonania wstawienia. To rozwiązanie to jakby połączenie zapytania insert i select.
Bonus. Oracle z użyciem wyzwalaczy i procedur składowanych
Skrypty tworzące tabelę oraz sekwencją pozostają niezmienione. Dodatkowo tworzę trigger:
CREATE OR REPLACE TRIGGER STUDENCI_ID_TRIGGER BEFORE INSERT ON STUDENCI REFERENCING NEW AS NEW FOR EACH ROW BEGIN SELECT STUDENCI_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL; END;
Skrypt PHP realizujący zadanie dla Oracle z wykorzystaniem procedury składowanej:
// połączenie z bazą $link = oci_connect('test', 'test','TEST.WORLD'); // sprawdzenie połączenia if ($link) echo 'Polaczony.'; else die('Blad bazy danych.'); // przygotwanie zapytania $sql = "BEGIN INSERT INTO STUDENCI(IMIE, NAZWISKO) VALUES ( 'BRUCE', 'LEE' ) RETURNING ID INTO :NEW_ID; END;"; $stmt = oci_parse($link, $sql); // zbindowanie zmiennej PL/SQL ze zeminna PHP oci_bind_by_name($stmt, ":NEW_ID", $id, 32); // wykonanie zapytania oci_execute($stmt); // wyświetlenie wyników echo "<br>Id dodanego rekordu: ".$id; // zamkniecie polaczenia oci_free_statement($stmt); oci_close($link);
Rozwiązanie to przypomina PostgeSQL, ponieważ używamy klauzuli RETURNING INTO. Oracle pozwala na korzystanie z tej klauzuli tylko w przypadku PL/SQL, czyli języka w którym tworzymy procedury wbudowane. W tym przykładzie posłużyłem się takim rozwiązaniem. Dodatkowo dzięki triggerowi pozbyłem się jawnego odczytywania kolejnej wartości z sekwencji. Ale po kolei.
Trigger w momencie dodawania rekordu wstawia kolejny numer z sekwencji, dzięki temu w zapytaniu insert już tego nie robię. Najważniejszą częścią tego przykładu, jest wykonanie zapytania jako procedury wbudowanej. Pozwoliło to skorzystać z mechanizmu RETURNING INTO. Ponieważ jednak jest to PL/SQL, trzeba zbindować zmienną po stronie serwera NEW_ID ze zmienną PHP $id. Reszta skryptu nie różni się od poprzedniego przykładu z wykorzystaniem Oracle.
W paczce z przykładami znajduje się dodatkowo takie rozwiązanie z użyciem ADOdb.
Przykłady do pobrania: http://antczak.org/source/returning/source.zip

Dobra dobra, równie dobrze możemy sobie napisać w postgresql funkcję która nam zwróci takie id o w taki oto sposób:
CREATE OR REAPLCE FUNCTION „insertFunction”( VARCHAR( 255 ) ) RETRUNS INT AS &&
DECLARE
„_name” ALIAS FOR $1;
„_currval” INT;
BEGIN
INSERT INTO
„tabela”;
VALUES
( „_name” );
SELECT
currval( ‚”idtabela_seq”‚)
INTO
„_currval”;
RETURN „_currval”;
END;
$$ LANGUAGE ‚plpgsql’;
:>
Możemy, tylko po co, skoro to już jest gotowe?
Pokazałem tu jak w danej bazie zrobić to w najprostszy sposób.
to która według Ciebie jest wydajniejsza metoda w MySQL za pomocą zapytania czy funkcji? mam swoje zdanie na ten temat i chętnie się coś więcej dowiem.
Tak jak napisałem w tekście, w zasadzie metody te są równoznaczne. Myślę, że wydajność jest zbliżona.
ja prowadziłem testy kiedyś, i w 90% przypadkach zapytania które wykonuje SQL są o wiele szybsze od tych po stronie pasera PHP. przy jakiś znikomych rekordach naprawdę nie ma porównania, ale przy dość pokaźniej bazie, php po prostu zaczyna nie wyrabiać mówię tu o rzędach ok 3mln rekordów nie zadajemy paserowi potrzeby pobrania, policzenia i jeszcze wyciągnięcia ostatnio dodanego rekordu. natomiast zapytania sql’owe zwracają nam już gotowe dane i paser nie musi się martwić o ich odpowiednie obrobienie.
W manualu PHP jest wzmianka, że liczba ta jest konwertowana do inta, stąd może wynikać opóźnienie. W każdym razie dobrze wiedzieć i dziękuję za wartościowy komentarz.
A ja mam pytanie co do metody pobierania lastid metodą PHP w MySQL.
Jest możliwe że funkcja nam zwróci id z innej tabeli niż ta do której dodaliśmy rekord z danego pliku w którym pobieramy lastid, w sytuacji gdy w tym samym czasie doda się inny rekord z innego pliku?.