Doporučujeme: Služby pro Váš web | Doména do 10 minut | Levný PHP webhosting | 5 GB Email hosting | 60 GB FTP hosting | PHP hosting od 23 Kč | Psí Park
| Zavřít |
klikni pro zmenšení fotky

Můj blog – až 10 % špičkového kódu navíc!

Uložené procedury v MySQL

Datum: 6. 5. 2007 0.00 | Autor: Lukáš | 11154× | Kategorie: MySQL | Komentáře: 10

Minulý měsíc se na vsevjednom.cz konal velký přechod na MySQL 5 a PHP 5, a to společně s přechodem na nejnovější stable verzi Debianu — 4.0.

Dnes jsem se rozhodl konečně vyzkoušet některou z řady možností, které nabízí MySQL 5 oproti čtverce: procedury.

Uložené procedury slouží ke zjednodušení kódu. Provádíme-li tedy například ve více skriptech několik stejných SQL dotazů za sebou, můžeme si pro ně vytvořit proceduru. Potřebujeme-li pak něco změnit, měníme pouze proceduru, nemusíme tak procházet jednotlivé soubory a zdlouhavě vyhledávat a měnit části kódu. Procedury jsou vlastně podobné jako klasické funkce, avšak s tím rozdílem, že je nedefinujeme vždy na začátku při provádění scriptu, ale jsou trvale uložené a zkompilované na serveru. Což se také podepisuje na rychlosti provádění procedur oproti běžnému zápisu.

Dost plácání; a protože nemám rád nekonkrétní příklady, uvedu jako příklad zjednodušený kód, který jsem dnes kuchal a předělával do procedury — kód počítadla na vv1. Rozhodl jsem se pro něj proto, že je to služba generující jednu z největších zátěží, a tak jsem si od úpravy kódu sliboval její snížení.

U počítadla používám více tabulek a při každém načtení počítadla se v některých z nich na základě různých podmínek aktualizují informace — počet zhlédnutí vždy a počet denních návštěv společně s vložením návštěvy do tabulky detailů jen při novém přístupů.

Uvedu názorný příklad, abyste byli v obraze:

CREATE TABLE `pocitadlo` (
`poc` int(11) NOT NULL auto_increment,
`celkem` int(8) NOT NULL default '0',
PRIMARY KEY (`poc`)
) ENGINE=MyISAM;


CREATE TABLE `pocitadlo_data` (
`id` int(11) NOT NULL auto_increment,
`poc` int(11) NOT NULL default '0',
`time` int(11) NOT NULL default '0',
`ip` varchar(31) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=MyISAM;


CREATE TABLE `pocitadlo_dny` (
`poc` int(11) NOT NULL default '0',
`datum` date NOT NULL default '0000-00-00',
`navstevy` smallint(5) NOT NULL default '0',
`zhlednuti` smallint(5) NOT NULL default '0',
UNIQUE KEY `poc` (`poc`,`datum`)
) ENGINE=MyISAM;

První tabulka obsahuje id počítadla a celkovou hodnotu návštěv. Do druhé tabulky se ukládají jednotlivé návštěvy: čas a IP adresa. No a ve třetí tabulce je vždy ke každému počítadlu a dnu počet návštěv a zhlédnutí.

Původní kód tedy vypadal ku příkladu nějak takto:

 1 <?

 2 $existuje
=mysql_query("SELECT 1 FROM pocitadlo_data WHERE poc=".$poc." AND ip='".$_SERVER['REMOTE_ADDR']."' AND time>".(time()-3600));
 3
 4 if(
mysql_num_rows($existuje)==0){
 5
mysql_query("INSERT INTO pocitadlo_data VALUES('', ".$poc.", ".time().", '".$_SERVER['REMOTE_ADDR']."')");
 6
mysql_query("UPDATE pocitadlo SET celkem = celkem+1 WHERE poc = ".$poc);
 7
mysql_query("UPDATE pocitadlo_dny SET navstevy = navstevy+1, zhlednuti = zhlednuti+1 WHERE poc = ".$poc." AND datum='".date("Y-m-d")."'");
 8 }
 9
10 else{
11
mysql_query("UPDATE pocitadlo_dny SET zhlednuti = zhlednuti+1 WHERE poc = ".$poc." AND datum='".date("Y-m-d")."'");
12 }
13  
?>


To máme pár podmínek a v nejhorším případě 4 SQL dotazy (v originální verzi počítadla je to ještě trochu více propodmínkované, v závislosti na různých nastaveních počítadla, části kódu se SQL dotazy se tedy opakovaly i několikrát). Teď si ale vytvoříme proceduru, díky které nebudeme potřebovat ani žádnou podmínku v php:

CREATE PROCEDURE `pocitadlo_vloz` (`poc` int, `ip` varchar(15), OUT zapocitan boolean)
BEGIN

IF EXISTS(SELECT 1 FROM `pocitadlo_data` WHERE `poc`=poc AND `ip`=ip AND `time`<(UNIX_TIMESTAMP()-3600)) THEN

UPDATE `pocitadlo_dny` SET `zhlednuti` = `zhlednuti`+1 WHERE `poc` = poc AND `datum`=CURDATE();
SET zapocitan=0;

ELSE

INSERT INTO `pocitadlo_data` (`poc`, `time`, `ip`) VALUES(poc, UNIX_TIMESTAMP(), ip);
UPDATE `pocitadlo` SET `celkem` = `celkem`+1 WHERE `poc` = poc;
UPDATE `pocitadlo_dny` SET `navstevy` = `navstevy`+1, `zhlednuti` = `zhlednuti`+1 WHERE `poc` = poc AND `datum`=CURDATE();
SET zapocitan=1;

END IF;

END;

Prvně — dbejte na to, aby byly názvy sloupců a tabulek v uvozovkách `! Jinak to nemusí fungovat!

A teď už k popisu: příkaz CREATE PROCEDURE snad vysvětlovat nemusím — znamená prostě vytvoření procedury; následuje její název. V závorce jsou pak vstupní proměnné, které do procedury vkládáme. Ty mohou být jak vstupní (IN), tak i výstupní (OUT) nebo obojetné (INOUT). O jaký typ proměnné se jedná, uvedeme před jejím názvem. V případě IN proměnných není třeba psát nic, jsou tak standardně označeny všechny. Dále je třeba nezapomenout uvést datový typ všech proměnných, což je povinné.

Vlastní kód procedury pak začneme příkazem BEGIN. Pak už můžeme používat podmínky, cykly, definovat si další proměnné a podobně; zkrátka využívat všech funkcí MySQL. Já ve svém příkladu potřebuji zjistit, zda již v tabulce pocitadlo_data existuje hodnota IP uživatele, a to vložená v poslední hodině. Použil jsem tedy podmínku a funkci EXISTS, která vrací TRUE. V případě, že záznam existuje, přičtu jen počet zhlédnutí a do výstupní proměnné zapocitan si uložím hodnotu 0. V opačném případě je uživatel započítán, je akutalizována hodnota celkových a dnešních návštěch a zhlédnutí. Do proměnné zapocitan nyní ukládám hodnotu 1. Následuje END IF, tedy konec podmínky, a END pro ukončení kódu procedury.

Proceduru vložte například v konsoli (mysql -u uzivatel -p heslo) úplně stejně jako jiný SQL kód (nejdříve je třeba si zvolit databázi: use nazev_databaze) s tím, že je nejdříve třeba použít příkaz DELIMITER //, aby vám konsole nezalamovala řádky jako nové příkazy; phpMyAdmin zatím vkládat nebo prohlížet procedury neumí.

Nyní jsme hotovi a můžeme proceduru zavolat (CALL) pomocí mysql_query v php kódu. Já jsem si navíc ještě vytvořil výstupní proměnnou zapocitan — tu vyberu dalším dotazem.

1 <?

2 mysql_query
("CALL pocitadlo_vloz(".$poc.", '".$ip."', @zapocitan)");
3
$zapocitan=mysql_result(mysql_query("SELECT @zapocitan"), 0);
4 echo 
$zapocitan;
5
?>

Budete-li chtít proceduru odstranit, stačí spustit SQL kód DROP PROCEDURE `nazev_procedury`. Status procedury pak příkazem: SHOW PROCEDURE STATUS LIKE 'nazev_procedury'. Pro zobrazení kódu by mělo fungovat SHOW PROCEDURE CODE `pocitadlo_vloz`, avšak to mi nefungovalo ani v konsoli, ani v PMA (dle manuálu funguje od verze 5.0.17, já mám 5.0.32). Pokud si však budete chtít zobrazit kompletní kód procedury v PMA, objevil jsem „fígl“: stačí si nechat vyexportovat strukturu tabulky.

Tak to by mělo být vše. Snažil jsem se to vysvětlit „česky“, snad se mi to povedlo.

Komentáře:

  1. Discogio6. 5. 2007 15.12

    Ač jsem tady nereagoval ani nepamatuju a několikrát už jsem měl nutkání napsat tady něco o objevení Ameriky.... tak...tohle už je moc i na mě :D Jestli takováhle trivialita doteď nebyla obsažena v předchozí verzi MySQL...tak potěšpánbůh, nedej bože jestli veškerý dotazy na vv1 byly psány x-krát do všech php souborů.... Tomu se říká 100% amatéřina :D Gratuluji. Věci, které jsou alespoň pro MSSQL naprostou běžnou záležitostí...jsou koukám pro MySQL 5 novinkou. Nedej bože až objevíš funkce které nabízí např. poslední verze ORACLu :) Nadruhou stranu jsem se solidně pobavil. Díky. :)



    odpovědět | Odpovědi: Lukáš [2],
  2. Lukáš6. 5. 2007 17.35

    Odpověď pro Discogio [1]: Pobavil jsem te tim, ze jsem s tim drive nedelal, nebo tim, ze to MySQL umi az od 5?

    Opravim te: tomu se rikala amaterstina, kdyz to ted MySQL bezne umi; takova novinka to neni - prvni stable releas MySQL 5 je totiz nekdy z roku 2005 nebo 2004 (nevim presne). Je jeste treba si taky uvedomit, ze MySQL je ponekud levnejsi nez MSSQL a Oracle. Mrkni nekde na popis vsech funkci, ktere pribyly v MySQL 5, mozna se pobavis jeste vic ;).

    Jo, vv1 bylo (a stale je) delano, co se tyce SQL takto tezkopadne, proto jsem uz poslednich par mesicu doslova se slintanim ocekaval stable Debianu s MySQL 5. Ted je prede mnou predelavani ;).



    odpovědět | Odpovědi: Discogio [3],
  3. Discogio6. 5. 2007 20.11

    Odpověď pro Lukáš [2]: Však mě je jasný, že MySQL 5ka nevyšla teď hned a předtim po ní nebylo ani vidu a slechu, ale mě pobavilo to, že to ty předchozí verze neuměly...jinak mimochodem..MSSQL2005 je zdarma ve verzi Express stejně je tomu i u Oraclu ;)
    Tak alespoň doufám, že když už jsi přešel na PHP5 tak jsi konečně začal s OOP.
    Tak Good luck při snižování zátěže na serveru při přechodu na vyspělejší technologie :)



    odpovědět | Odpovědi: Lukáš [4],
  4. Lukáš6. 5. 2007 20.16

    Odpověď pro Discogio [3]: To te mozna taky pobavi, ale podle toho, co jsem cetl na internetu, je PHPckovsky OOP pomalejsi nez funkce… Takze se do OOP moc nehrnu, abych jeste vic nezvysil zatez :). Nebyt tohoto, uz bych se do nej davno pustil.

    A s tema databazema zdarma mas pravdu :).



    odpovědět
  5. Pavel Stehule13. 8. 2007 8.58

    Zdarec,

    ty apostrofy kolem identifikatoru tam urcite byt nemusi. Urcite ne u kazdeho. Pouzivaji se pouze u nestandardnich identifikatoru, napr. obsahujici mezeru, male velke znaky, atd.

    Pavel



    odpovědět | Odpovědi: Lukáš [6],
  6. Lukáš13. 8. 2007 11.17

    Odpověď pro Pavel Stehule [5]: Je to možné. V mém případě MUSÍ, protože vstupní proměnné mají stejné názvy.



    odpovědět
  7. Spawn4. 9. 2007 15.57

    Ja tomu moc nerozumim, tohle uz budou asi pokročilejší SQL dotazy :(



    odpovědět
  8. Jan Turoň12. 9. 2007 17.49

    Discogio, je to přehledné, stručné a srozumitelné vysvětlení použití uložených procedur MySQL v PHP. Ve své aroganci nevidíš nic než vlastní úhel pohledu, kritizuješ MySQL, i když očividně nemáš zkušenosti v praktickém srovnání (píšeš ...Jestli takováhle trivialita doteď nebyla obsažena v předchozí verzi MySQL...). Vsadil bych se, že tě v nějaké firmičce posadili za ORACLE, ke kterému se programátor na volné noze nedostane, čímž vzrostlo tvé ego (ale očividně ses nedostal k Linuxu a pořádnému síťovému programování), které se tu snažíš procpat. Zde se ale píše o rychlé, rozšířené, spolehlivé a bezplatné MySQL.
    Dobrý programátor vidí dál než na špičku svého nosu, vidí široký okruh své neznalosti a vidí tedy, jak hloupě působí, když se chvástá někdo jako ty. Přeji bolestné objevování Ameriky: světa za hranicemi tvého ega :o)



    odpovědět
  9. Jan Turoň12. 9. 2007 18.06

    Nový phpMyAdmin vkládat uložené procedury i spouště umí: stačí vyplnit delimiter do pole Oddělovač.

    Pro výšeuvedený příklad by se hodila uložená funkce. Její syntaxe je podobná, pouze vrací výsledek:
    CREATE FUNCTION `pocitadlo_vloz` (`poc` int, `ip` VARCHAR(15)) RETURNS INT(1)
    BEGIN
    ...
    RETURN 1;
    END|
    (Oddělovač |)
    není nutné tak využívat proměnnou

    Pavle Stehule, apostrofy u promennych byt MUSÍ, jinak hrozí SQL inject: představ si, že ti někdo zadá do pole pro heslo řetězec "neco OR 1=1", pak se přihlásí na libovolný účet. Může třeba také zadat "; SHUTDOWN;" a shodit celý server. Ani apostrofy nejsou všemocné, lze zadat např. heslo="\' OR 1=1 \', z toho důvodu se všechny proměnné ošetřují funkcí addslashes()

    zdroj: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html



    odpovědět | Odpovědi: Pavel Stehule [10],
  10. Pavel Stehule10. 10. 2007 14.21

    Odpověď pro Jan Turoň [9]: To je omyl. Tak to chodi v PHP, kdezto diskuze je o ulozenych procedurach. Jednk v SQL apostrofy alespon v MySQL urcuji, ze se jedna o identifikator, ktery nevyhovuje standardu .. napr. je case sensitive, nebo obsahuje nepripustne znaky. Dale, kazdy prikaz ulozene procedury (vyjma dynamickeho SQL) je predzpracovan, tedy nehrozi riziko SQL injektaze (coz je take jeden z duvodu, proc se pouzivaji ulozene procdury). A abych jen neteoretizoval, vyzkousel jsem si to:

    DELIMITER //
    CREATE PROCEDURE sql_injecting_test(predikat varchar(20))
    SELECT * FROM test WHERE v = predikat; //
    DELIMITER ;


    mysql> select * from test where v = 'verejne' or true;
    select * from test where v = 'verejne' or true;
    +------+--------------+
    | id | v |
    +------+--------------+
    | 1 | verejne |
    | 2 | prisne tajne |
    +------+--------------+
    2 rows in set (0,01 sec)

    mysql> call sql_injecting_test('verejne');
    call sql_injecting_test('verejne');
    +------+---------+
    | id | v |
    +------+---------+
    | 1 | verejne |
    +------+---------+
    1 row in set (0,00 sec)

    mysql> call sql_injecting_test('verejne \'' or true);
    call sql_injecting_test('verejne \'' or true);
    Empty set (0,00 sec)


    Všimněte si, že k SQL injektáži nedošlo. Zákonitě použil jsem uložené procedury .. prepared statements, což je absoutní ochrana proti SQL injektáži.



    odpovědět

Přidat nový komentář:




Ochrana proti spamu. Napište prosím číslici pět: