MySQL - Tips und Tricks

Text in Zahlen umwandeln

http://stackoverflow.com/questions/11808573/sql-order-string-as-number (2015-10)

Cast your column value to an integer explicitly with

select col from yourtable

order by cast(col as unsigned)

or implicitly for instance with a mathematical operation which forces a conversion to number

select col from yourtable
order by col * 1

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.

Abfragen mit Pivot Tabellen

http://stackoverflow.com/questions/12808189/setting-column-values-as-column-names-in-the-sql-query-result

Pivot Abfragen kommen zum Einsatz, wenn Tabellen so aufgebaut sind, daß die Spaltennamen in der Tabelle drin sind. Eigentlich sollte man so etwas vermeiden, kommt aber z.B. beim Wordpress mit den Customfields vor. Es gibt keine Pivot Funktion in mySQL daher braucht man einen Workaround.

Beispiel: Tabelle:
id    col1     col2
----------------------
0      name    ax
0      name2   bx
0      name3   cx
1      name    dx
1      name2   ex
1      name2   fx

Das Ergebnis sollte so aussehen:

id   name    name2     name3
0    ax      bx         cx
1    dx      ex         fx

So gehts:

SELECT
  id,
  /* if col1 matches the name string of this CASE, return col2, otherwise return NULL */
  /* Then, the outer MAX() aggregate will eliminate all NULLs and collapse it down to one row per id */
  MAX(CASE WHEN (col1 = 'name') THEN col2 ELSE NULL END) AS name,
  MAX(CASE WHEN (col1 = 'name2') THEN col2 ELSE NULL END) AS name2,
  MAX(CASE WHEN (col1 = 'name3') THEN col2 ELSE NULL END) AS name3
FROM
  yourtable
GROUP BY id
ORDER BY id

Erklärung: Es wird nach id gruppiert (GROUP BY) Über Case wird geschaut ob der Wert da ist. Wenn nicht NULL zurückgegeben. MAX schmeißt alle NULLer raus und sorgt dafür daß es nur eine Zeile pro id gibt.

Das geht nur wenn man die gewünschten Spalten kennt. Die Spalten sind quasi statisch (hartcodiert). Wenn man die Spalten nicht kennt muß man das Statement dynamisch generieren:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when col1 = ''',
      col1,
      ''' then col2 end) AS ',
      col1
    )
  ) INTO @sql
FROM yourtable;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM yourtable 
                  GROUP BY id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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;