wz

MySQL: příklad vlastní funkce

14:04 18.01.2015

V tomto příspěvku je upravená kopie jedné MySQL funkce, kterou bylo potřeba vytvořit. Představme si, že máme databázi, ve které mohou být skupiny, z nichž každá může mít správce. Po funkci chceme, aby vrátila email správce konkrétní skupiny. Pokud správce neexistuje, vrací se výchozí email nastavený jinde v konfiguraci. Jména tabulek a konkrétní vztahy mezi nimi nejsou tak důležité, proto jsou dotazy uvedené níže zjednodušené. Cílem nebylo představovat závislosti v konkrétní databázi, ale ukázat, jak by přibližně takový script pro vytvoření funkce mohl vypadat.

DELIMITER $$

DROP FUNCTION IF EXISTS `get_admin_email`$$

CREATE FUNCTION `get_admin_email`(idGroup INT) RETURNS VARCHAR(128) CHARSET utf8
BEGIN

DECLARE mail VARCHAR(128);

	SELECT u.email
	INTO mail
	FROM user u
	JOIN ... -- tady kus sql chybí
	WHERE ...	-- tady kus sql chybí	
	LIMIT 1;

IF mail IS NULL THEN
	SELECT VALUE 
	INTO mail
	FROM config 
	WHERE ... --tady kus sql chybí 
  ;
END IF;

RETURN mail;

END$$

DELIMITER ;

MySQL: Collate with no character set

13:54 23.02.2014

V MySQL na mě při vytváření vlastní funkce vyskočila následující hláška:

Error Code : 1235
This version of MySQL doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'

Ve funkci jsem měl:

...
CREATE FUNCTION get_id(
	_id_user INT,
	_ip VARCHAR(255) COLLATE utf8_bin
)
...

Náprava spočívala v drobné změně, takže definice funkce vypadala nějak takto:

...
CREATE FUNCTION get_id(
	_id_user INT,
	_ip VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin
)
...

MySQL: Illegal mix of collations

13:40 23.02.2014

MySQL na mě při použití vlastní funkce zařval následující:

Error Code : 1267
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_czech_ci,IMPLICIT) for operation '='

Jak lze tento problém vyřešit (existuje několik možností), je celkem pěkně popsané např. zde.

Ve zkratce: Došlo k míchání různých znakových sad dohromady.

MySQL: Podmínka v ORDER části

22:12 26.09.2013

Potřeboval jsem v MySQL udělat takové řazení výsledných záznamů, že se mi hodilo v order části vytvořit podmínku ve stylu, že pokud u záznamů platí něco, tak řaď takto, jinak takto. To v MySQL lze. Viz. dokumentace k CASE. Dokonce lze v CASE použít jiné CASE. Jako takový příklad vytržený z kontextu zkusím uvést toto:

...
ORDER BY
   (CASE 
       WHEN cas IS NULL THEN "23:59:59" 
       ELSE cas
    END)

Takto jsem si zařídil, že se mi záznamy řadili dle času od času nejnižšího k času vyššímu, přičemž záznamy, kde byl čas NULL, nebyly při takovém řazení na začátku, ale až na konci. Časy v mé tabulce byly maximálně 23:59, což je 23:59:00, takže NULL záznamy se mi dostaly krásně na konec.

MySQL - Oddělovač u GROUP_CONCAT

00:39 15.11.2012

Když v MySQL použiji GROUP_CONCAT, mám hodnoty v příslušném sloupci oddělené pomocí defaultního oddělovače (čárky). Pokud se nám tento oddělovač nelíbí, lze ho změnit. Já např. v jednom svém scriptu chtěl mít za oddělovač čárku s mezerou. Příklad vytržený z kontextu následuje. To podstatné v něm je klíčové slovo SEPARATOR.

SELECT s.*,
				(	SELECT GROUP_CONCAT(o.name SEPARATOR ', ') 
					FROM pobocka p 
					JOIN obec o ON o.id_obec = p.id_obec
					WHERE p.id_skola = s.id_skola 
				) as pobocky
			FROM skola s
			ORDER BY s.name

MySQL - Dočasné tabulky

03:08 30.10.2012

MySQL dovoluje vytvářet dočasné tabulky, které existují pouze do doby, než se ukončí klientovo session. Po ukončení session jsou tabulky automaticky odstraněny. Případně je lze pomocí DROP TABLE odstranit dříve.

Příklad uvedu jeden - a to tento nepraktický. Vytvořím tabulku, vložím do ní data, nechám si je vypsat. Tyto data se mi skutečně vypíší. Když si pak v databázi zkusím tuto tabulku vyhledat, nenajdu jí tam.

create temporary table pokus(
   id int not null auto_increment,
   title varchar(150) not null,
   primary key(id)
);

insert into pokus(title) values('záznam 1');
insert into pokus(title) values('záznam 2');

select * from pokus;

MySQL reflexe

01:48 30.10.2012

Do tohoto příspěvku jsem vypsal několik SQL příkazů, pomocí kterých si lze udělat povědomí o dostupném prostředí.

SHOW DATABASES; - zavoláme-li, dostaneme všechny databáze, které jsou v databázovém systému k dispozici - včetně např. information_schema

Pomocí USE nazevDatabaze; nastavíme databázi "nazevDatabaze" jako aktivní

Chceme-li znát název aktuální databáze, lze zavolat SELECT DATABASE(); Není-li žádná databáze aktivní, vrátí to NULL.

Pomocí SHOW TABLES FROM databaze; si lze vypsat všechny tabulky v databázi "databaze". Vynecháme-li část FROM, tak se tabulky vyberou z aktuální databáze.

K jednotlivým tabulkám si lze vypsat informace jako počet řádků - a to pomocí SHOW TABLE STATUS;

Pomocí SHOW COLUMNS FROM tabulka lze vypsat všechny sloupce v tabulce "tabulka". Vidíme tu i to, jakého je sloupec typu, jestli je null, jaká je defaultní hodnota, ...

Informace ke klíčům použitým u sloupců dané tabulky, si lze vypsat pomocí SHOW KEYS FROM tabulka;

Obdobně můžeme získat informace k indexům : SHOW INDEX FROM tabulka

MySQL - Timestamp jako datum

19:34 29.10.2012

Už několikrát jsem potřeboval pracovat s tabulkami, ve kterých byl čas uložen jako timestamp - tedy např. jako takováto hodnota 1351535002. Když pak potřebuji rychle zjistit, jakému času toto číslo odpovídá, chodím např. na tuto stránku, kde si vše naklikam.

Převod do čitelnější formy si lze samozřejmě zařídit i v MySQL - např. takto:

SELECT FROM_UNIXTIME('1351535002')

Aktuální timestamp v MySQL získám takto:

select UNIX_TIMESTAMP();

Pokud bychom chtěli získat timestamp jen jako datum (tj. bez času), tak to by šlo takto:

select DATE(FROM_UNIXTIME('1351535002'));

A když bychom chtěli výstup v jiném formátu, tak dotaz by mohl vypadat takto: :)

select DATE_FORMAT(DATE(FROM_UNIXTIME('1351535002')),'%d.%m.%Y') as datum

MySQL - STRAIGHT_JOIN

23:22 22.10.2012

Když v MySQL budu spojovat více tabulek, neznamená to, že tabulky budou spojeny v pořadí, ve kterém jsou uvedeny v dotazu. Pomocí STRAIGHT_JOIN si toto pořadí lze vynutit. Takový dotaz pak vypadá nějak takto:

SELECT STRAIGHT_JOIN * FROM ....

V MySQL dokumentaci o tom pojednáno např. zde.

MySQL - Poddotazy s exists

12:43 21.10.2012

V poddotazech lze v MySQL využít EXISTS. To vrací true, pokud poddotaz vrátí nějaký řádek.

Normálně jsem s exists nepotřeboval vůbec pracovat, ale dostal jsem se k němu v souvislosti s optimalizací dotazů, protože použití EXISTS je efektivnější, než použití IN.

Na použití EXISTS jsem připravil jeden (trochu nepraktický) příklad:

CREATE TABLE IF NOT EXISTS `tabulka` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nazev` varchar(30) COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=5 ;

INSERT INTO `tabulka` (`id`, `nazev`) VALUES
(1, 'A'),
(2, 'B'),
(3, 'C'),
(4, 'D');

CREATE TABLE IF NOT EXISTS `vazebni` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_tabulka` int(11) NOT NULL,
  `text` text COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=4 ;

INSERT INTO `vazebni` (`id`, `id_tabulka`, `text`) VALUES
(1, 1, 'Něco pro A'),
(2, 1, 'Ještě něco pro A'),
(3, 3, 'Text pro C');

Dotaz s pomocí IN:

SELECT * 
FROM tabulka
WHERE id
IN (

SELECT id_tabulka
FROM vazebni
)

Dotaz s pomocí EXISTS:

SELECT * 
FROM tabulka
WHERE EXISTS (

SELECT 1 
FROM vazebni
WHERE tabulka.id = vazebni.id_tabulka
)