REPLACE

Školení, která pořádám

MySQL obsahuje nestandardní SQL příkaz REPLACE, který dovoluje vložit do tabulky data nebo je přepsat v případě, že koliduje primární klíč nebo jiný unikátní index. S využitím tohoto příkazu je možné velice snadno ošetřit obsluhu společného formuláře pro editaci a vložení záznamu:

<?php
// tradiční řešení
if (isset($_GET["select"])) {
    mysql_query("UPDATE tabulka SET ... WHERE id = " . intval($_GET["select"]));
} else {
    mysql_query("INSERT INTO tabulka (...) VALUES (...)");
}

// s využitím REPLACE
mysql_query("REPLACE tabulka SET id = " . intval($_GET["select"]) . ", ...");
?>

Příkaz REPLACE jsem dřív používal, pak jsem s tím ale kvůli jeho nestandardnosti přestal (byť např. SQLite ho také podporuje).

Příkaz má ještě jednu nepříjemnou vlastnost – v případě kolize smaže všechny kolidující řádky a vloží místo nich ten nový. Takže pokud např. v tabulce uzivatele (id int NOT NULL AUTO_INCREMENT, login varchar(30) NOT NULL, UNIQUE (login), PRIMARY KEY (id)) máme řádky (1, 'vasek'), (2, 'petr') a provedeme REPLACE uzivatele SET id = 2, login = 'vasek', zbude v tabulce jediný řádek (2, 'vasek'). Rozumnější by podle mě bylo, kdyby REPLACE detekoval kolize pouze u primárních klíčů.

Pokud mě paměť neklame, tak v minulosti bylo možné využívat hodnoty zaměňovaného řádku, takže např. aktualizace počítadla čtenosti článku v jednotlivých dnech byla jednoduchá (předpokládá se existence unikátního indexu nad sloupci clanek, datum):

<?php
// tradiční řešení
mysql_query("INSERT INTO ctenost (clanek, datum, pocet) VALUES (" . intval($_GET["id"]) . ", CURDATE(), 1)");
if (!mysql_affected_rows()) {
    mysql_query("UPDATE ctenost SET pocet = pocet + 1 WHERE clanek = " . intval($_GET["id"]) . " AND datum = CURDATE()");
}

// s využitím REPLACE ve starších verzích MySQL
mysql_query("REPLACE ctenost SET clanek = " . intval($_GET["id"]) . ", datum = CURDATE(), pocet = pocet + 1");
?>

Tato vlastnost ale byla označena za chybu a byla odstraněna. Přípomínám, že opačný postup u tradičního řešení (INSERT až po vyzkoušení UPDATE) by mohl vést k neuložení záznamu a testování pomocí SELECT by vedlo k nutnosti zamknout tabulku.

Podtrženo, sečteno – nejen kvůli nestandardnosti příkazu, ale hlavně kvůli problémům spojeným s unikátními indexy příkaz používat nedoporučuji. Málokdo asi stojí o to, aby kvůli přidanému unikátnímu indexu začala z tabulky mizet data.

V MySQL 4.1 byl představen modifikátor ON DUPLICATE KEY UPDATE příkazu INSERT, který je příkazu REPLACE podobný, ale nemá tak destruktivní účinky. Přesně tento příkaz se hodí pro aktualizaci čtenosti:

<?php
// s využitím ON DUPLICATE KEY UPDATE od MySQL 4.1
mysql_query("INSERT INTO ctenost (clanek, datum, pocet) VALUES (" . intval($_GET["id"]) . ", CURDATE(), 1) ON DUPLICATE KEY UPDATE pocet = pocet + 1");

// získání hodnoty sloupce id s příznakem AUTO_INCREMENT
$id = (mysql_affected_rows() == 1 ? mysql_insert_id() : mysql_result(mysql_query("SELECT id FROM ctenost WHERE clanek = " . intval($_GET["id"]) . " AND datum = CURDATE()"), 0));
?>

Pokud ke konfliktu unikátního klíče nedojde, vrátí mysql_affected_rows jedničku, jinak dvojku.

Přijďte si o tomto tématu popovídat na školení Návrh a používání MySQL databáze.

Jakub Vrána, Výuka, 23.9.2005, diskuse: 10 (nové: 0)

Diskuse

dgx:

Jakube, pokud nastavíš UNIQUE (login), tak očekáváš, že by v tabulce měly být dva řádky s login='vasek'?

23.9.2005 02:35:38

llook:

To asi ne, ale já bych očekával, že to vyhodí chybu. Pokud se někdo snaží uživateli id=2 dát obsazené jméno, nečekal bych, že to provede a původního nositele jména smaže.

Lépe to řeší metoda Replace() z ADOdb. Dostane název tabulky, hodnoty a název klíče. Pokud se jí nepodaří UPDATE, tak zkusí INSERT. Záleží jenom na mě, co určím jako klíč.

24.9.2005 05:28:00

Radim Smička:

Tady někdo nesnáší Operu. Na weblogu bych to opravdu nečekal :(.

Pokud se na stránku podívám Operou vyhodí to vždy
Connection closed by remote server

23.9.2005 08:15:46

Rado:

Mi Opera bez problémů funguje.

23.9.2005 08:49:52

endlife:

problém je asi na Vašem příjimači, chodím sem s Operou od zrodu této subdomény a bez problému..

i když si moc nedokážu představit, jak se dá tohoto stavu docílit..

(reakce na) 23.9.2005 08:51:43