Pobierz wartość, a jeśli nie istnieje dodaj

Witajcie,

Jest tabela:

CREATE TABLE table (
  id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  name varchar(32) DEFAULT NULL,
  PRIMARY KEY (id)
)

Kolumna name posiada unikatowe wartości:

ALTER TABLE table
ADD UNIQUE INDEX name (name);

Czy istnieje możliwość stworzenia kwerendy, która:

• jeśli wartość name istnieje, zwróci id,
• jeśli wartość name nie istnieje, doda nowy rekord i zwróci nową wartość AUTO_INCREMENT dla id.

Pseudokod:

idValue = (SELECT id FROM table WHERE name = '12345');

if ( idValue is not empty )
  return idValue;

INSERT INTO table (name) VALUES ('12345');
return AUTO_INCREMENT(id);

Dziękuję i pozdrawiam

ja nie znam, ale wyszukiwanie po zaindeksowanym polu nie jest kosztowne

Twój kod można zapisać inaczej:

1. Wstaw name do tabeli, jeśli go nie ma.
2. Zwróć id dla danego name

@Marcin.Miga:

Marcin.Miga napisał(a):

Twój kod można zapisać inaczej:

1. Wstaw name do tabeli, jeśli go nie ma.
2. Zwróć id dla danego name

Zwróć uwagę na to, co zapisałem w pseudokodzie.
Przeanalizujmy Twoje podejście Marcinie:

Marcin.Miga napisał(a):

1. Wstaw name do tabeli, jeśli go nie ma.

Ten krok wymaga wcześniejszego wyszukania czy name istnieje czy nie:

SELECT id FROM table WHERE name = ?;

lub wstawienia rekordu, ignorując duplikat:

INSERT IGNORE INTO table (name)  VALUES (?);
-- lub
INSERT INTO table (name) VALUES (?) ON DUPLICATE KEY UPDATE id=id;

Marcin.Miga napisał(a):

2. Zwróć id dla danego name

... jednakże z powyższej kwerendy ignorującej, można zrezygnować, jeśli SELECT, zwróci id.

Kwerendy ignorujące mają pewną wadę, jeśli zostanie znaleziony duplikat, LAST_INSERT_ID() zwróci 0, co więcej dla tego przypadku, INSERT IGNORE zwiększy wartość AUTO_INCREMENT; być może widzisz do czego zmierzam - w pesymistycznym wariancie, dojdzie do co najmniej dwóch niepotrzebnych wyszukań, jeśli name nie ma w tabel.

Było by fajnie, gdyby np. INSERT w przypadku znalezienia duplikatu, ustawiło LAST_INSERT_ID() na prawidłową wartość, ale tego nie robi.
Oczywiście można użyć procedury/funkcji, jednakże, zastanawiałem się czy istnieje możliwość wykonania tej operacji jedną konstrukcją kwerendy.

Pozdrawiam

@matuzalem: Nie robisz tego, co napisałem.
Napisałem wyraźnie: Wstaw name do tabeli, jeśli go nie ma.
Czyli

INSERT INTO ...
SELECT ...
WHERE NOT EXISTS(SELECT name FROM ...)

Wydaje mi się, że będzie to szybsze, niż sprawdzanie lub INSERT z ON CONFLICT

Marcin.Miga napisał(a):

@matuzalem: Nie robisz tego, co napisałem.

Napisałem wyraźnie: Wstaw name do tabeli, jeśli go nie ma.
Czyli

INSERT INTO ...
SELECT ...
WHERE NOT EXISTS(SELECT name FROM ...)

Wydaje mi się, że będzie to szybsze, niż sprawdzanie lub INSERT z ON CONFLICT

Dziękuję Marcinie za odpowiedź - Twoje podejście również rozważałem zanim napisałem na forum, jednak ono nie zadziała, chyba, że ja robię coś źle.

INSERT INTO table (name)
SELECT '?'
WHERE NOT EXISTS
(
    SELECT id FROM table WHERE name = '?'
);

SELECT LAST_INSERT_ID();

Jeśli name nie ma w table - zostanie dodane i LAST_INSERT_ID() zwróci oczekiwaną wartość AUTO_INCREMENT, jednakże, jeśli name już istnieje, wtedy LAST_INSERT_ID() zwraca wartość ostatniego rekordu, a nie tą, która jest przypisana do istniejącego name - czyli i tak musiałbym wykonać SELECT.

Pozdrawiam

Podaj wersje MySQL

@Marcin.Miga:

Marcin.Miga napisał(a):

Podaj wersje MySQL

Korzystam z PDO - poniżej kod Marcinie.

$sql = "INSERT INTO test_table (name) " .
       "SELECT ? " .
       "WHERE NOT EXISTS " .
       "(SELECT id FROM test_table WHERE name = ?)";

$stmt = $db->prepare($sql);

$stmt->bindValue(1, "value1");
$stmt->bindValue(2, "value1");

$stmt->execute();

// ...

echo "ID: " . $db->lastInsertId();

Dla pustej tabeli test_table w przypadku wprowadzenia nowej wartości (value1):

• execute() --> true,
• lastSQLErrCode --> 00000,
• LastDriverErrorCode --> 0,
• lastInsertId --> 1,
• AUTO_INCREMENT --> 2.

Następnie wprowadzam kilka unikatowych wartości (dla przykładu: będą to 4 kolejne różne wartości) dla name by wypełnić tabelę - lastInsertId zwracał przewidywane wartości dla pola AUTO_INCREMENT.

Aktualna zawartość tabeli:

| id | name |
|----+------|
|  1 |value1|
|  2 |value2|
|  3 |value3|
|  4 |value4|
|  5 |value5|

AUTO_INCREMENT = 6

Przy próbie wprowadzenia już istniejącej wartości (value2):

• execute() --> true,
• lastSQLErrCode --> 00000,
• LastDriverErrorCode --> 0,
• lastInsertId --> 0,
• AUTO_INCREMENT --> 6.

Jak widać, lastInsertId zwraca 0, a nie 2.

Pozdrawiam

Tyle treści, a nie podałeś tego, o co prosiłem... A i kod nie robi tego, co napisałem w pierwszym poście...

Twoja propozycja z poprzedniego posta:

Marcin.Miga napisał(a):

@matuzalem: Nie robisz tego, co napisałem.

Napisałem wyraźnie: Wstaw name do tabeli, jeśli go nie ma.
Czyli

INSERT INTO ...
SELECT ...
WHERE NOT EXISTS(SELECT name FROM ...)

Wydaje mi się, że będzie to szybsze, niż sprawdzanie lub INSERT z ON CONFLICT

Marcin.Miga napisał(a):

Podaj wersje MySQL

Co masz na myśli MySQL?

Prosiłbym Cię o pomoc, a nie zabawę w kotka i myszkę - jeśli znasz odpowiedź na zadane prze zemnie pytania, proszę po prostu podaj je, jeśli nie to nie zabieraj mi czasu w przekomarzanie się.

Pozdrawiam

Masz rację. Nie będę trącił czasu na rozwiązywanie cudzych problemów.

Myślę, że @cerrato oraz @Marcin.Miga wyczerpali możliwości - wątek uznaje za zamknięty.

MySQL ma wiele wersji od 5.X (starsze już raczej nie używane) po 8.X dodatkowo ma wiele klonów jak MariaDB czy Percona. Podaj dokładnie silnik i wersję. Póki korzystasz z prostego Select to nie ma różnicy, ale jak już chcesz wchodzi w detale to podaj te wersję. Jak masz PHPMyAdmin to w prawym górnym rogu są stosowne informacje.
A z SQL

SELECT VERSION();

jurek1980 napisał(a):

MySQL ma wiele wersji od 5.X (starsze już raczej nie używane) po 8.X dodatkowo ma wiele klonów jak MariaDB czy Percona. Podaj dokładnie silnik i wersję. Póki korzystasz z prostego Select to nie ma różnicy, ale jak już chcesz wchodzi w detale to podaj te wersję. Jak masz PHPMyAdmin to w prawym górnym rogu są stosowne informacje.

A z SQL

SELECT VERSION();

Dziękuję Jurku za zainteresowanie - niektórym pomyliło się formu ze szkołą - jak będę chciał wydać pieniądze to kupię dobrą książkę, a nie rozwiązanie, teraz potrzebuje pomocy z odpowiedzią, a nie dochodzenie.

Nie korzystam z PHPMyAdmin: MariaDB 10.1.40

Pozdrawiam

teraz potrzebuje pomocy z odpowiedzią, a nie dochodzenie.

To musisz zdać sobie sprawę, że wersja bazy ma znaczenie, aby uzyskać prawidłową odpowiedź. I to "dochodzenie" temu służyło. Ty jednak odebrałeś to opacznie...

No nic zostaje jak to określiłeś "dobra książka" .