Bernhard Häussner

MySQL-Queries sparen mit GROUP_CONCAT

22.06.2009, 20:10
Ein MySQL-Query mit GROUP_CONCAT

Ein MySQL-Query mit GROUP_CONCAT

Fast jede (Web-)Anwendung, die eine Datenbank wie MySQL benutzt, muss irgendwie das Problem lösen, zu einem Artikel mehrere Tags, zu einem Eintrag mehrere Kommentare, zu einem Wort mehrere Synonyme zu finden, die sich in einer weiteren Tabelle befinden. Jedes Mal, wenn mehrere Zeilen Abgefragt werden sollten, die wieder mehrere untergeordnete Zeilen haben, bedeutet das meistens für jedes Element einen weiteren Query zu starten, oder eine zweite Abfrage mit einer weiteren Liste zu starten, oder eine riesige Reihe Datensätze abzufragen und der Anwendung das Gruppieren zu überlassen. Mit GROUP_CONCAT (Dokumentation) lässt sich in MySQL (fast) alles in ein SQL-Query zusammenfassen.

Einführung

Nehmen wir eine einfache Datenbankstuktur an, wie die eines Blogs. Es gibt blogeintraege mit einem Titel, blogtags mit einem Namen. Dann gibt es noch eine Tabelle, die beides verbindet, z.B. blogtaglinks. Jeder Datensatz hat natürlich eine eindeutige ID. Wenn man jetzt alle Blogeinträge auflisten will, könnte man das wie folgt machen:

SELECT Titel FROM blogeintraege

Um die Anzahl der Verbundenen Tags herauszufinden, wäre ein solcher Join üblich:

SELECT Titel, COUNT(distinct(t.ID))
FROM blogeintraege b, blogtags t, blogtaglinks l
WHERE b.ID = l.ID_entry and t.ID = l.ID_tag
GROUP BY b.ID
+-------------------------+-----------------------+
| Titel                   | COUNT(distinct(t.ID)) |
+-------------------------+-----------------------+
| Hello World!            |                     2 |
| The CMS                 |                     2 |
| To be Released Later... |                     1 |
+-------------------------+-----------------------+
3 rows in set (0.01 sec)

Hier werden jeweils alle Spalten, die den selben Blogeintrag repräsentieren gruppiert und dann aus diesen Gruppen die Anzahl der verschiedenen Tags abgefragt. Dabei ist COUNT die Gruppenfunktion, die die nun versteckten, „weg-gruppierten“, Datensätze verarbeitet - hier zählt.

Die GROUP_CONCAT-Funktion

MySQL und andere RDBMSs kennen für Zahlen einige Funktionen, die Summe, Durchschnitt etc. gruppierter Zahlen berechnen können. MySQL kennt darüber hinaus die GROUP_CONCAT()-Funktion, welche die „versteckten“ Werte (also auch CHAR) in einer Spalte zusammen zu hängen. Mit dieser Funktion wäre es also möglich, die Tags direkt aufzulisten:

SELECT Titel, GROUP_CONCAT(distinct(t.name))
FROM blogeintraege b, blogtags t, blogtaglinks l
WHERE b.ID = l.ID_entry and t.ID = l.ID_tag
GROUP BY b.ID
+-------------------------+---------------------------------+
| Titel                   | GROUP_CONCAT(distinct(t.name))  |
+-------------------------+---------------------------------+
| Hello World!            | Projekt: Mein Blog,Persönliches |
| The CMS                 | PHP,Projekt: Mein Blog          |
| To be Released Later... | Projekt: Mein Blog              |
+-------------------------+---------------------------------+
3 rows in set (0.00 sec)

Jetzt wird anstatt der Anzahl der Tags eine Komma-getrennte Liste der Tags angezeigt. Das ist insofern ein recht großer Fortschritt, da wir nicht nochmal eine zweite Abfrage machen müssen, die uns jeweils die Namen der Tags heraus sucht.

Weiter Ausbauen

Jetzt wird es Zeit ein bisschen zu Optimieren. In diesem Beispiel macht es wenig Sinn, aber theoretisch könnten zwei Tags den gleichen Namen haben. In diesem Fall würde uns MySQL aber den Namen nur einmal anzeigen, da distinct() doppelte Werte aussortiert. Wir würden also weniger Tags bekommen, als ein COUNT(distinct(t.ID)) anzeigt. Das können wir lösen, indem wir die distinct()-Funktion auf die IDs der Tags anwenden:

SELECT Titel, GROUP_CONCAT(distinct(t.ID),'-', t.name)
FROM blogeintraege b, blogtags t, blogtaglinks l
WHERE b.ID = l.ID_entry and t.ID = l.ID_tag
GROUP BY b.ID

Das würde uns für jeden Blogeintrag eine Liste erstellen, wie 42-Webbrowser,28-Productivity,27-Design. Die eigentlich überflüssigen IDs werden später noch entfernt, z.B. mit PHP.

Wenn wir zusätzlich noch die Erstellungsdaten z.B. der letzten drei Kommentare abfragen wollen, zeigt sich, warum wir distinct() verwenden müssen: Sonst wird jeder Tag so oft angezeigt, je nach dem, wie viele Kommentare ein Eintrag hat. Wenn wir also noch eine Tabelle benutzen, wie blogkommentare mit creation_time, und uns jeweils die letzten Erstellungszeiten anzeigen lassen wollen, benutzen wir:

SELECT Titel, GROUP_CONCAT(distinct(t.ID),'-', t.name), 
GROUP_CONCAT(distinct(c.ID),'-',c.creation_time ORDER BY creation_time DESC)
FROM blogeintraege b, blogtags t, blogtaglinks l, blogkommentare c
WHERE b.ID = l.ID_entry and t.ID = l.ID_tag and c.entry_id = b.id
GROUP BY b.ID

Jetzt haben wir noch eine weitere Spalte mit einem Wert, wie 34-2008-10-20 18:28:41,33-2008-10-19 23:22:37. Doch je mehr Spalten wir abfragen, desto unübersichtlicher werden die Listen. Außerdem hängt ja immer noch die ungewollte, aber doch notwendige id davor.

Verarbeitung mit PHP

Diese minderen kosmetischen Makel lassen sich dann in der Anwendung noch beheben. Hier zeige ich, wie ich mit PHP die Listen in Arrays lade und sortiere:

function arrayifyGrpConcats($q,$resultset) {
  $regexp='/\\bgroup_concat\\((.*?) separator \'(?<sep>.*?)\'\\) +as +(?<name>\w+)\\b/i';
  if ( preg_match_all($regexp,$q,$matches,PREG_SET_ORDER) ) {
    foreach ($matches as $grpCoClause) {
      if (isset($grpCoClause["name"],$grpCoClause["sep"])) {
        $name=$grpCoClause["name"];
        if (strpos($name,'_',1)===false) {
          $foldername=$name;
          $subname=false;
        } else {
          $foldername=substr($name,0,strpos($name,'_',1));
          $subname=substr($name,strpos($name,'_',1)+1);
        }
        $s=$grpCoClause["sep"];
        foreach ($resultset as $id=>$row) {
          $val=$row[$name];
          unset($resultset[$id][$name]);
          if ($subname) {
            if (!isset($resultset[$id][$foldername])) {
              $resultset[$id][$foldername]=array();
            }
            $vals=explode($s,$val);
            for ($i=0, $len=count($vals);$i<$len;$i++) {
              if (!isset($resultset[$id][$foldername][$i])) {
                $resultset[$id][$foldername][$i]=array();
              }
              $resultset[$id][$foldername][$i][$subname]=preg_replace('/\\d+-/','',$vals[$i],1);
            }
          } else {
             $resultset[$id][$foldername]=explode($s,$val);
          }
        }
      }
    }
  }
  return $resultset;
}

Diese Funktion kann direkt nach der Abfrage auf die Ergebnisse angewendet werden. Dazu übergibt man ihr das Result als durchnummeriertes Array mit je einem assoziativen Unterarray (Dictionary) pro Datensatz, der als Schlüssel die Spaltennamen und als Werte den jeweiligen Spaltenwert enthält. Die Funktion sucht dann im Query nach GROUP_CONCAT-Aufrufen der From group_concat(... separator ',') as iwas. Das Trennsymbol wird explizit angegeben, damit es hier keine Missverständnisse gibt.

Man sollte bedenken, dass MySQL mit dem Separator im Wert nichts macht. Wie man im SQL sieht habe ich das Problem mit dem Trennzeichen im Wert „gelöst“, indem ich es aus dem Kommentartext einfach gelöscht habe. Eine ausgefeiltere Methode habe ich bei stackoverflow gefunden.

Als Alias nach as kann man fast alles angeben. Die Funktion wird diesen Namen als Spaltennamen im Result-Array verwenden.

Als kleines Extra habe ich noch eingebaut, dass die Funktion optional mehrere Spalten zusammen nimmt, wenn sie ein Alias der Form gruppe_name erkennt: Sie wird dann im Resultset-Array unter gruppe ein nummeriertes Array anlegen, das ein assoziatives Arrays enthält mit den verschieden name. Wenn wir also zu einem Kommentar nicht nur das Datum, sondern auch den Autor wissen wollen, könnten wir group_by... as comm_time, group_by... as comm_name verwenden, um die Daten für jeden Kommentar zusammen zu halten.

Im Endeffekt kann das dann so aussehen:

SELECT b.id, b.Titel AS Titel,
group_concat(DISTINCT t.ID,'-', t.name separator ', ') as tags_Name, 
group_concat(DISTINCT t.id separator ', ') as tags_id, 
group_concat(DISTINCT c.ID,'-',c.creation_time separator ', ') as comm_time, 
group_concat(DISTINCT c.ID,'-',REPLACE(LEFT(c.Text,20),',','') 
     separator ', ') as comm_text 
FROM blogeintraege b, blogtaglinks l, blogtags t, blogkommentare c
WHERE b.ID = l.ID_entry and t.ID = l.ID_tag and c.entry_id=b.id
GROUP BY b.ID LIMIT 1,2

Was uns dann dieses Ergebnis liefert: (print_r)

Array
(
    [0] => Array
        (
            [id] => 2
            [Titel] => The CMS
            [tags] => Array
                (
                    [0] => Array
                        (
                            [Name] => PHP
                            [id] => 3
                        )

                    [1] => Array
                        (
                            [Name] => Projekt: Mein Blog
                            [id] => 2
                        )

                )

            [comm] => Array
                (
                    [0] => Array
                        (
                            [time] => 2008-10-19 23:22:37
                            [name] => Bernhard H.
                            [text] => Also das Kommentarsy
                        )

                    [1] => Array
                        (
                            [time] => 2008-10-20 18:28:41
                            [name] => Bernhard H.
                            [text] => So ich hab es jetz 
                        )

                )

        )

    [1] => Array
        (
            [id] => 10
            [Titel] => To be Released Later...
            [tags] => Array
                (
                    [0] => Array
                        (
                            [Name] => Projekt: Mein Blog
                            [id] => 2
                        )

                )

            [comm] => Array
                (
                    [0] => Array
                        (
                            [time] => 2008-10-20 19:48:50
                            [name] => Bernhard H.
                            [text] => Cool das klappt auc
                        )

                )

        )

)

Ich hoffe mit dieser Methode in Zukunft Datenbankabfragen zu vermindern und damit die Geschwindigkeit der Anwendungen weiter zu erhöhen. Zwei kleine Sachen noch:

SELECT @@group_concat_max_len

Dieser Wert ist die maximale Länger der mit GROUP_CONCAT gebildeten Spaltenwerte und lieget meist bei 1024. Außerdem kann es nötig sein den Spaltenwerte von BLOB zu konvertieren: CAST(... as CHAR).

Kurze URL http://1-co.de/b/1C. Post to twitter

Kommentare

keine





 
Χρόνογραφ
© 2008-2017 by Bernhard Häussner - Impressum - Login
Kurz-Link zu dieser Seite: http://1-co.de/b/1C