MySQL - Tips und Tricks

Joins und GroupBy

Mit Joins kann man verknüpfte Tabellen abfragen. Mit groupby bündelt man doppelte Einträge. Mit group_concat kann man mehrere Werte aus den verknüpften Tabellen (z.B. bei mehrerern Kategorien) zusammenfassen.

Beispiel News

Quelle: http://www.typo-script.de/mysql-script/mysql-typo3-beispiel-zu-group_concat-mit-join (2013-10)

Folgende Abfrage soll umgesetzt werden: Zeige mir alle Datensätze, die zu einer Kategorie gehören. Konkret in diesem Fall, alle Newsbeiträge zu allen Newskategorien.

SELECT
  tt_news_cat.uid AS cat_id,
  GROUP_CONCAT(DISTINCT tt_news.uid ORDER BY tt_news.uid DESC SEPARATOR ',') AS news_id

FROM tt_news
  LEFT OUTER JOIN tt_news_cat_mm ON tt_news.uid = tt_news_cat_mm.uid_local
  LEFT OUTER JOIN tt_news_cat ON tt_news_cat.uid = tt_news_cat_mm.uid_foreign

GROUP BY tt_news_cat.uid

+----+----------------+
| cat_id | news_id    |
+----+----------------+
|  1     | 1,2,3,4    |
|  2     | 5,6        |
|  3     | 7,8,9,10   |
|  4     | 11,12      |
+--------+------------+

Die Syntax der GROUP_CONCAT Funktion ist folgendermaßen aufgebaut: GROUP_CONCAT([DISTINCT] expr [,expr ...]

            [ORDER BY {unsigned_integer | col_name | expr}
                [ASC | DESC] [,col_name ...]]
            [SEPARATOR str_val])

Desweiteren sollte beachtet werden, das die maximale Zeichenanzahl der GROUP_CONCAT - Spalte auf eine Länge von 1024 Zeichen begrenzt ist. Sollte dies nicht ausreichen, kann man in der mysql.conf die Systemvariable group_concat_max_len an seine Bedürfnisse anpassen.

Wert mit Werten aus einer Komma separierten Liste vergleichen

Gerade bei Typo3 hat man oft das Problem, das man eine id mit einem Datenbank Textfeld mit einer Liste von Werten die mit Komma getrennt vorliegen vergleichen will. Die Typo funktionen sind Performancefresser. Eleganter geht es mit einer geschickten MySQL Abfrage. Beispiel finde den Wert 7 in der Liste des Feldes usergroup (da steht z.B. 3,5,7,8,9 drin)

WHERE CONCAT(',',usergroup,',') LIKE '%,7,%' 

oder:

WHERE FIND_IN_SET('7',usergroup)>0 

UPDATE mit Werten aus anderer Tabelle

aus: http://www.tutorials.de/forum/relationale-datenbanken-sql-mysql-oracle-db2-mssql/174881-mysql-update-mit-werten-aus-anderer-tabelle.html

Datum des Zugriffs: 4.4.2008

zunächst mal: Updates über mehrere Tabellen sind grundsätzlich "tricky" (siehe dazu die MySQL-Doku, speziell die Kommentare).

Dann: Der Tabellen-Name "order" ist nicht sehr empfehlenswert, da der Interpreter diesen Namen gerne mit dem ORDER BY verwechselt, ergo muss der Tabellenname order immer als `order` geschrieben werden, sonst droht Syntax-Error.

Ein weiteres Problem ist die Gruppen-Funktion MIN() ohne GROUP BY.

So, jetzt 2 Lösungsvorschläge, einmal mit SubQuery (ab MySQL 4.1): Code:

UPDATE
 user AS u
LEFT JOIN
 (SELECT uid, MIN(datum) AS mindatum FROM `order` GROUP BY uid) AS o ON u.id = o.uid
SET
 u.createDate = o.mindatum

und einmal mit temporärer Tabelle (MySQL 4.0 und früher): Code:

DROP TABLE IF EXISTS tmp_order;

CREATE TEMPORARY TABLE tmp_order TYPE = HEAP
 SELECT uid, MIN(datum) AS mindatum FROM `order` GROUP BY uid;

UPDATE
 user
LEFT JOIN
 tmp_order ON user.id = tmp_order.uid
SET
 user.createDate = tmp_order.mindatum;

DROP TABLE IF EXISTS tmp_order;