Tipps und Tricks » MySQL Tricks

Tipps und Tricks für MySQL

Moderne Datenbanken können deutlich mehr, als gemeinhin bekannt ist. Meist ist die Verarbeitung erheblich schneller, wenn diese Möglichkeiten genutzt werden. Einigen Beispiele aus der Praxis will ich hier nennen.

Falls nichts anderes angegeben ist, beziehe ich mich aut MySQL, was keinesfalls bedeuten muß, dass diese oder wenigstens ähnlich Kommandos auf anderen Datenbanken nicht genutzt werden können.

Dateioperationen

Wer aus einem MySQL-Client heraus Dateien erzeugen oder verarbeiten möchte, muss sich an ein paar kleine Eigenheiten gewöhnen: Für die Erzeugung von Dateien, z.B. über das Statement into outfile, gelten die Dateirechte des Serverprozesses. Zudem ist neuerdings das Ausgabeverzeichnis in der Konfiguration fix vorgegeben. Der Pfad kann mittels SHOW VARIABLES LIKE "secure_file_priv" abgefragt werden. Der Server überschreibt aus Sicherheitsgründen generell keine existierende Datei, der Anwender muss dafür sorgen, dass die Datei nicht existiert. 

Das Ausgabeverzeichnis hat unter Debian Jessie (default /var/lib/mysql-files) nur user-Rechte für den Server. Somit kann nur root auf die Dateien zugreifen, was natürlich sehr restriktiv ist. Für meine Entwicklungsumgebung habe ich meinem Standard-User zusätzlich Gruppenrechte der Gruppe mysql erteilt, und auch dem Verzeichnis rwx für die Gruppe gewährt. Für Produktivsysteme ist der jeweilige Anwendungsfall zu betrachten. Ein Cronjob zum Verschieben erzeugter Dateien (z.B. in ein Home-Verzeichnis) ist für viele Anwendungsfälle ein praktikabler Ausweg.

Für eine einzulesende Datei gelten die Rechte des Users, der den Client betreibt.

Falls die Rechte nicht passen, treten die typischen Fehler (13, mangelnde Rechte) oder ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

 

Stringfunktionen innerhalb der Datenbank nutzen

Oft sind Sonderzeichen in der Datenbank, speziell in text oder varchar columns enthalten. Im Programmfluß ist es oft störend, einzelne Rückgabewerte nachzubearbeiten. In derartigen Fällen sind die Stringfunktionen innerhalb der Datenbank sehr nützlich.

Das folgende Beispiel zeigt, wie Zeilenumbruch und Carriage Return entfernt werden. Entfernt werden die Zeichen mittels replace(x, y, z), der Term "as products_description" sorgt dafür, daß die originale Spaltenbezeichnung zurückgegeben wird.

select replace(replace(products_description, '\r', ''), '\n', '') as products_description 
from products_description
where language_id = 2;

CSV-Datei per SQL erzeugen

So einfach kann es sein:

select id, name, address, phone from customers
into outfile '/tmp/phonelist.csv'
fields terminated by ','
enclosed by '"'
lines terminated by '\n'

Gerade in diesem Fall kann auch die o.a. Nutzung der Stringfunktion außerordentlich hilfreich sein, denn Zeilenumbrüche innerhalb einer CSV-Datei zerstören deren Struktur. Falls nach dem Import der Datei der Zeilenumbruch wieder nötig sein sollte, empfiehlt sich die Ersetzung durch eine brauchbare Markierung.

Zahl der Datensätze pro Tabelle ermitteln

select table_name, table_rows from information_schema.tables where table_schema = '<DB-Name>';

liefert eine schnelle Lösung, leider nicht zuverlässig. Wer's genau wissen will, muß die Zahl per select count(*) from table ermitteln

Engine umstellen

Schon seit geraumer Zeit wird mit MySQL eine neuere Engine geliefert, die einige Vorteile bietet. Die alte MyISAM-Engine wird wohl langfristig eingestellt.

Um die Vorteile der neuen InnoDB zu nutzen, müssen alte Tabellen umgestellt werden. Das geht einfach mit dem Kommando alter table customers engine = InnoDB

Nun ist es natürlich mühsam, das für viele Tabellen zu tun. Aber auch das lässt sich mit Bordmitteln der DB erledigen. Wir holen uns die Tabellenname aus dem Data Dictionary, verknüpfen sie mit dem Kommando und geben sie als SQL-Script aus. Anschließend führen wir dieses Script aus:

select concat('alter table ', table_name, ' engine = InnoDB;') as qry 
from information_schema.tables where table_schema = 'db' and engine = 'MyISAM'
into outfile '/var/lib/mysql-files/isam2inno.sql';

\. /var/lib/mysql-files/isam2inno.sql

 

 

 

Powered by Etomite CMS.