In deze blogpost ga ik enkele weinig gebruikte doch zeer handige MySQL functies en statements uitleggen. MySQL heeft namelijk veel meer te bieden dan je denkt qua queries.
Om de queries uit te voeren heb ik een voorbeeld database opgebouwd die je hier kan downloaden en gebruiken om de queries te testen. Alle queries hieronder werden uitgevoerd op een MySQL 5.0 database op Windows, je kan zelf op je eigen computer makkelijk een MySQL database en PhpMyAdmin installeren met XAMPP (of MAPM als je een Mac gebruiker bent).

SOUNDEX(), SOUNDS LIKE

Om maar onmiddellijk met de deur in huis te vallen één van de meest tot de verbeelding sprekende functies uit MySQL: SOUNDEX. Met de Soundex library kan je vergelijkingen uitvoeren op basis van de uitspraak van een string. Zo kan je bijvoorbeeld makkelijk een zoekfunctie in je site inbouwen die niet alleen op exacte spelling zoekt maar ook matched op basis van uitspraak. Let wel op dat je deze functie niet als algemene zoekfunctie van je site dient te gebruiken, daarvoor zijn FULL TEXT SEARCH en LIKE queries natuurlijk veel geschikter. Wel is dit handig als je bijvoorbeeld door een database van plaatsnamen of medewerkers moet zoeken. Daar verschilt de schrijfwijze nogal eens (bv Christian of Christiaan, Jonathan of Jonatan) en kan je met SOUNDEX veel betere resultaten bereiken.

Deze functie is gebaseerd op het fonetische algoritme Soundex wat voor elke uitspraak een tekstuele representatie geeft. Door middel van deze representatie te vergelijken kan je woorden vinden die hetzelfde klinken.

SELECT
  id,
  title,
  SOUNDEX(title)
FROM products

Als we de soundex waarde van de title column uit de products table zouden trekken zie je dat de SOUNDEX waarde voor de 3 saw films gelijk is.

Maar de echte kracht ligt hem in het feit dat strings die hetzelfde klinken dezelfde soundex waarde hebben. Een snel voorbeeldje:

SELECT
  id,
  title
FROM products AS p
WHERE p.title SOUNDS LIKE 'Shaw'

Met deze query haal je enkel producten die klinken als ‘Shaw’ op, in onze test database voldoen de 3 Saw films aan dit criteria.

Wat ook nog waard is om te noteren is dat performance zeker in het oog dient gehouden te worden als je vaak gebruik maakt van SOUNDEX en dat de SOUNDEX waarde van een string altijd als eerste letter dezelfde letter heeft als de string zelf, op die manier kan je natuurlijk belangrijke results mis lopen. Een oplossing is een substring te doen op de SOUNDEX waarden waarbij je het eerste karakter van de waarde negeert.

Ook is Soundex gebaseerd op het Engels en werkt het niet helemaal goed met UTF-8 data, dat kan dus wel eens vreemde resultaten geven als je database anderstalige gegevens bevat.

COALESCE()

Waarschijnlijk één van de meest vreemd genaamde functies: COALESCE. Coalesce betekent in het engels eigenlijk blend of mix together, en dat is exact wat deze comparison functie doet. Dit is een functie waar je een arbitrair aantal velden aan meegeeft en je de eerste niet-null waarde terugkrijgt. Zeer handig voor het ophalen van bijvoorbeeld default waarden, vertalingen etc ...

SELECT
  id,
  COALESCE(title_fr, title, 'Onbekend') 
    AS title
FROM products

Vorige query geeft je alle films terug met id en franstalige title. Is het title_fr veld gelijk aan NULL valt de query terug op de waarde van het title veld en is ook deze gelijk aan NULL dan krijg je de string ‘Onbekend’ terug. Zo heeft het veld wat je als title kiest altijd een waarde, ook al zijn beide title's niet ingesteld.

Deze functie kan ook erg handig zijn om default waarden uit een andere table op te halen indien een column van je table null blijkt te zijn. Zo kan je aan elke group een default price geven voor films die onder deze groep vallen. Elke film kan op zijn beurt dan weer een specifieke price krijgen die de group price overschrijft.

SELECT 
  p.title, 
  p.sku,
  COALESCE(p.price, g.price) AS price,
  g.name
FROM products AS p
LEFT JOIN productgroups AS g
  ON p.group_id = g.id

Bovenstaande query haalt voor elke film de price op uit de productgroups table, tenzij voor de film specifiek een waarde is ingesteld. Zijn beide niet ingesteld krijg je gewoon NULL terug als price.

In sommige gevallen is de meerwaarde van COALESCE beperkt en kan je hetzelfde bereiken met een simpel IFNULL statement maar in sommige gevallen bespaart COALESCE je een zeer complexe IFNULL structuur met één simpele en leesbare function.

GROUP_CONCAT

De GROUP_CONCAT functie is echt een lifesaver als je een website uitbouwt die zwaar gebruik maakt van tags. Met GROUP_CONCAT kan je gerelateerde data uit een andere tabel als comma separated list ophalen. Stel je wil elke movie ophalen en daarbij een lijst van tags gescheiden op komma. Zonder GROUP_CONCAT is dan je enige optie om voor elke movie nog een query uit te voeren waarbij je de tags ophaalt. Maar hier komt GROUP_CONCAT to the rescue.

SELECT 
  p.id, 
  p.title,
  GROUP_CONCAT(t.tag) AS tags
FROM products AS p 
LEFT JOIN products_tags AS pt
  ON pt.product_id = p.id
LEFT JOIN tags AS t
  ON t.id = pt.tag_id
GROUP BY p.id
ORDER BY p.title

Met deze query haal je het id, de title en een comma separated list van tags op voor alle producten in één enkele query. Wil je een andere SEPARATOR gebruiken kan je dat door de SEPARATOR flag meet te geven aan de GROUP_CONCAT statement en je kan de items die je met GROUP_CONCAT ophaalt ook nog sorteren.

SELECT 
  p.id, 
  p.title,
  GROUP_CONCAT(t.tag ORDER BY t.tag SEPARATOR '|') AS tags
FROM products AS p 
LEFT JOIN products_tags AS pt
  ON pt.product_id = p.id
LEFT JOIN tags AS t
  ON t.id = pt.tag_id
GROUP BY p.id
ORDER BY p.title

Conclusie

Zoals je ziet biedt MySQL nog veel meer buiten het gewone SELECT, WHERE en LIKE werk en kan je met wat creatieve code en kennis van de minder bekende functies en statements heel wat werk besparen.