MySQL

In diesem Bereich werden einige kleine Query-Schnippsel aufgeführt. Problemlösungen für meist kleiner Aufgabenstellungen.

Details

MySQL Updaten z.B. in XAMPP/LAMP/WAMP Installationen

Von Zeit zu Zeit kommen Sie nicht umhin auch z.B. Ihre lokale Entwicklungsumgebung zu aktualisieren. Viele Websever nutzen PHP und hier findet laufend eine Ablösung von Versionen statt. Auf Webservern ist das meist durch den Hoster erledigt und dann kann ein Update mit wenigen Klicks aktiviert werden. Unter XAMPP / LAMP / WAMP etc. gestaltet sich das komplizierter, nicht nur weil man sich dort selbst drum kümmern muss, sondern weil es diese Updates meist nur als "Gesamtpaket" gibt, bei dem nicht nur PHP sondern auch MySQL aktualisiert wird. Das von Vorteil, weil auch bei MySQL hat sich sehr viel getan - allein schon der Umstieg auf MariaDB ist lohnenswert.

Nun, dass ist aber nicht der eigentliche Inhalt dieses Beitrags, sondern auf ein Problem einzugehen, welches beim Update von MySQL auftritt. MySQL zeigt sich hier sehr störrisch. Man möchte behaupten, dass eine Updatefähigkeit für MySQL nie vorgesehen war, so umständlich und widerspenstig verhält es sich.

Dieser Beitrag richtet sich an Lösungssuchende, die genau daran scheitern, eine bestehende langjährig gewachsene Entwicklungsumgebung erfolgreich und verlustfrei updaten zu wollen oder zu müssen. Ich selbst stand unter dem Zwang nach dem ich 4 Jahre nach Erstinstallation meiner XAMPP-Entwicklungsumgebung das neue CMS GRAVity ausprobieren wollte und feststellen musste, dass dieses nur unter PHP 5.6 läuft, was in meiner Umgebung nicht gegeben war.

Weiterlesen: Update von MySQL z.B. unter XAMPP und auftretende Probleme und Fehlerquellen

Details

Zielstellung

Häufig gibt es die Situation, und sei es nur zur Datenanalyse oder Fehlersuche, dass man die Datensätze in einer MySQL-Tabelle finden möchte, die es in einem Abfrageresultat gibt, aber nicht in dem anderen.

Z.B.: Sie wollen alle Produkt-Ids/Artikelnummern von Produkten finden, die es in einer Artikelgruppe gibt aber nicht in der anderen.

Umsetzung / Lösung per MySQL

Für die Lösung sind 3 Bausteine notw.

  1. Damit alle DS mit einer einzigen Abfrage aufgelistet werden, die es in der einen Artikelgruppe nicht gibt bzw. in der anderen nicht gibt, führen wir zwei Teilabfragen durch, die dann mit UNION zusammengeführt werden und als eine gemeinsame Ergebnisliste ausgegeben werden. Hier könnten weitere Teilergebnislisten angefügt werden z.B. für weiter Artikelgruppen.
  2. Innerhalb dieser Teilabfragen für jede Artikelgruppe erstellen wir zunächst eine Trefferliste über alle gefundenen DS die entweder der einen oder anderen ArtGrp angehören. Dazu nutzen wir zwei Teilabfrage, die mit UNION ALL zusammengefügt werden. Dann reduzieren wir diese Treffer auf je max. 1 DS pro artnr durch Verwendung von GROUP BY artnr. Das ist unsere Gesamtmenge.
  3. An diese gruppierte Gesamttrefferliste fügen wir nun per LEFT JOIN die Datensätze der zu prüfenden ArtikelGruppe an. Wir suchen als die Vergleichs-Teilmenge. Dort wo es keinen Treffer gibt, wird das Feld artnr mit NULL <null> geschrieben. Genau nach diesem NULL fragen wir dann ab und lassen uns per artnr IS NULL als Trefferliste alle Datensätze zeigen, die keine ArtNr aus dem LEFT JOIN bekommen haben.

Hier mal ein Codebeispiel, die Umsetzung beispielsweise erfolgen kann:

Weiterlesen: MySQL: Vergleich von Datensätzen die es in eine Resultat gibt aber nicht im anderen

Details

Diese kleine Liste zeigt einige MySQL-Befehle die auf Kommandozeile eigegeben werden können.

Aufgabe Kommando Beispiel
Anzeigen der Tabellenstruktur 
DESCRIBE tabelle;
löschen einer Spalte 
ALTER TABLE tabelle DROP COLUMN spalte;
Einfügen einer Spalte 
ALTER TABLE tabelle ADD spalte tinyint(4) NULL;
Ändern von Spalteneigenschaften 
ALTER TABLE tabelle MODIFY spalte NOT NULL;

Details

Problembeschreibung

Viele CMS wie Joomla, Wordpress, Drupal oder auch andere Framework, Webshops, Foren usw. nutzen für die Benennung Ihrer Datenbank-Tabellen Präfixe. Oft wird der Wunschpräfix schon bei der Installation abgefragt und alle Tabellen entsprechend benannt. In früheren Joomla-Installationen (z.B. bis 2.5) wurde ein Präfix jos_ vorgeschlagen. Wenn man diesen so verwendet, birgt das jedoch ein Sicherheitsrisiko. Jeder potentielle Hacker kenn so schon die Namen der Datenbanktabellen. Verwendet man hingegen eigenen Präfixe für die Tabellen, schafft man höhere Sicherheit. Seit Joomla 3 wird der Präfix genau aus diesem Grund zufällig erzeugt und vorgeschlagen. Wurde dieser Sicherheitsaspekt bei der Installation nicht berücksichtigt sollte man die Umbenennung nachträglich machen - Aber wie?

Außerdem kann es in der Administration und Pflege der Datenbank weitere Gründe für eine Umbenennung der Tabellen-Präfixe geben, z.B. wenn man mal eben vor einem Datenrestore schnell noch die alten Tabellen umbenennen will, ändert man einfach mal den Präfix.

Nun können in einem Projekt schnell man 100 und mehr Tabellen liegen. Diese manuell umzubenennen nervt einfach. Über MySQL und auch PhpMyAdmin oder Adminer gibt es keine Möglichkeiten dies elegant und schnell zu erledigen. Man müsste wirklich Tabelle für Tabelle einzeln umbenennen.

Weiterlesen: Ändern des Tabellen-Präfixes von MySQL-Datenbanken

Details

Zielstellung: Datenimport in eine vorhanden MySQL-Tabelle

Der Import von Excel-Daten ohne Programmieraufwand und mit den i.d.R. vorhanden Webdesigner-Tools wie phpMyAdmin ist möglich, aber doch nicht ganz so unkompliziert wie man denkt. Einer der wichtigen Gründe ist, dass Excel beim Speichern von CSV wenig Konfigurationsmöglichkeiten bietet und auch beim Import durch phpMyAdmin stehen kaum Konfigurationsmöglichkeiten zur Verfügung. Man ist also darauf angewiesen, dass beide Programme das liefern was man sich wünscht und muss dafür dann einfach deren "Spielregeln" einhalten.

Meine Tests habe dabei gezeigt, dass es ziemlich egal zu sein scheint, ob man nun aus dem Excel heraus erst eine CSV-Datei speichert und diese dann importiert, oder gleich direkt auf eine *.xls zugreift. Der Bequemlichkeit wg. nutze ich also gleich die xls-Datei und beziehe mich in meine Erläuterungen darauf.

Das größte Problem besteht darin die Daten in den Strukturen und vor allen Feldtypen zu erhalten wie wir uns unsere MySQL-Tabelle wünschen. Bei Import versucht phpMyAdmin möglichst intelligent zu entscheiden welcher Feldtyp und mit welcher Formatierung für die Daten geeignet ist. Das kann schon mal schief gehen und wg. der fehlenden Einflussnahmemöglichkeit müssen wir alternative Wege finden. Kritisch sind gerade mal Felder mit Decimalwerten oder auf PLZ, die gerne zum Integer umgewandelt werden, obwohl sie eigentlich besser als Zeichenkette (varchar) behandelt werden sollten (wg. evtl. führender Vornullen bei deutschen PLZ).

 

Weiterlesen: Mit phpMyAdmin Daten direkt aus Excel importieren

Details

Problembeschreibung

Es kann schon mal durch unsaubere Scripte passieren, dass über GET-Requests in die Datenbank Formularinhalte in Felder eingetragen werden, bei denen die Sonderzeichen url-encodiert sind. So stünde dann dort statt des Leerzeichens %20.

Wenn man seine Tabelleninhalte nun von diesen Dingen befreiten möchte, z.B. per MySQL-Update-Anweisung, dann tritt ein Problem auf: Man will nach %20 suchen. Das geht aber nicht so einfach weil das %-Zeichen in MySQL als Wildcard verwendet wird. Man muss es also escapen. Die übliche Escape-Syntax wie z.B. in PHP durch "\" geht nicht.

 

Lösung

Das gute an der Lösung ist, man kann jedes beliebige Zeichen in MySQL als Escape-Zeichen definieren - mit dem Parameter ESCAPE.

Ein kompletter Query kann dann z.B. so aussehen:

Weiterlesen: Suchen und ersetzen von URL-kodierten Sonderzeichen in MySQL-Datenbankfeldern

Details

Problembeschreibung/Zielstellung

Mitunter kommt es vor, dass man in einer MySQL-Tabelle varchar()-Datenfelder hat in denen Informationen stehen an die man herankommen möchte, z.B. um gezielt eine korrekte numerische Sortierung zu erreichen.

So können z.B. in Artikelnummern oder Matchcodes an irgend einer Stelle eine Information zu einem Produktdurchmesse kodiert sein. Nehme wir mal als Beispiel ein DIN-Normteil. Eine Artikelnummer oder ein Matchcode könnte z.B. so aussehen: DIN9861 0,5 71 D-HSS. An zweiter Stelle sehen wir hier unseren Durchmesser, an 3. Stelle eine Längenangabe.

Das Problem stellt sich jetzt in zwei Stufen da: Zum einen müssen wir die Stellen separieren an denen unsere Dimensionsagaben stehen, dann müssen diese von einem String in einen numerischen Wert umgewandelt werden, damit die darauf basierende Sortierung korrekt numerisch und eben nicht alphanumerisch erfolgt.

 

Weiterlesen: Korrekte numerische Sortierung von Datensätzen aus komplexen String-Feldern

Details

Problembeschreibung / Aufgabenstellung

Angenommen Sie haben haben eine Tabelle in der Sie Rohre mit unterschiedlichem Durchmesser verwalten und wollen nun per Datenbankabfrage das Rohr finden, welches von einem Abfragedurchmesser aus gesehen den nächst kleineren Durchmesser hat. Einfach nur eine kleiner-Abfrage verbunden mit Limit 1 wäre sehr unzuverlässig und denkbar sowieso nur bei sortierten Daten.

 

Lösung

Dazu braucht man keine aufwändigen Query-Konstrukte bauen, oder gar einer Trefferliste mit Datensätzen von kleineren Durchmesser mit z.B. php das Array durchlaufen und nach dem kleinsten suchen. Das geht schon allein mit MySQL-Boardmitteln durch z.B. folgenden Abfrage:

SELECT * FORM tabelle 
WHERE rohrdurchmesser < abfragedurchmesser
ORDER BY rohrdurchmesser DESC
LIMIT 1;

 

Weiterlesen: Mit MySQL einen nächst kleineren Wert finden

Details

Problembeschreibung

Wer sich mal die Mühe macht und unter Joomla 1.5.xx sich die Tabelle jos_session anschaut, wird evtl. feststellen dass diese extreme Größen annehmen kann. Bei einer dieser Kontrollen per phpMyAdmin sah ich in einem meiner Projekte, dass diese 1,6GB(!) groß geworden ist.

Bei weiterer Analyse zeigte sich:

  1. Es waren nur knapp 700 Einträge in der Tabelle
  2. und alle Einträge nur max 1 Stunde alt.

Also von daher war der eigentliche Tabelleninhalt vollkommen okay. Dadurch wurde mir klar, dass die Tabelle vermutlich mit gelöschten Datensätzen zugemüllt ist und dies offensichtlich kein ernst zu nehmendes Joomla-Problem ist.

Weiterlesen: jos_session-Tabelle wird extrem groß

Details

Problembeschreibung

Häufig wird in MySQL-Tabellen in Datensätzen auch Datum und Uhrzeit gespeichert, z.B. vom Erstellungs- oder Änderungszeitpunkt. Das erfolgt häufig als Integerzahl im Unix-Timestamp-Format und ist dann bei Tabellenanzeigen für Menschen schwer lesebar. Deshalb möchte man mitunter diesen UNIX-Timestamp gerne in eine normale Schreibweise von Datum und Uhrzeit angezeigt haben.

 

Hier die Lösung

... am Beispiel einer User-Tabelle, wo wie das Anmeldedatum umgewandelt haben wollen.

 

SELECT *, FROM_UNIXTIME(time) as realtime
FROM `session`
ORDER BY `time` DESC
LIMIT 30

 

Mit nachfolgender Lösung werden alle DS in einer MySQL-Tabelle gefunden, bei der das modified_on-Datum darauf verweist, dass der DS zuletzt vor 30 Tagen bearbeitet wurde:

select *, modified_on, timestampdiff(DAY, modified_on, now()) as diffdays 
from j2ms_virtuemart_categories c
  left join j2ms_users u on c.modified_by = u.id
  WHERE timestampdiff(DAY, modified_on, now()) < 30
ORDER BY timestampdiff(DAY, modified_on, now()) ASC 
limit 0, 100

 

Man kann die Datum/Zeit-Ausgabe auch gezielt formatieren in dem ein Formatstring übergeben wird:

FROM_UNIXTIME(`timestamp_column`, '%Y-%m-%d %H:%i:%s')