Migrationen von Daten zwischen zwei Datenbanken sind in der Regel eine komplexe Operation. Mit SQL kann man dabei sehr viele Probleme lösen, so dass meistens gar keine Programmierung in irgendeiner anderen Sprache notwendig sein müsste.

Die im folgenden beschriebenen Operationen sind aufwändig und nicht für Anwendungsfälle mit Nutzerinterkation geeignet. Sie eignen sich aber hervorragend für einmalige Migrationen oder Reparaturen an Datenbanken.

Die folgenden Beispiele beziehen sich auf MySQL, sind aber in vielen Fällen auch auf jede andere Form von SQL-Datenbank anwendbar.

SQL-Skripte

Als Programmierer denkt man bei SQL immer nur an einzelne Kommandos, die man aus der sicheren Umgebung seiner Programmiersprache (wie z.B. PHP) absetzt. Tatsächlich kann man aber auch halbwegs komplexe Skripte in SQL schreiben.

Diese Skripte speichert man einfach als .sql-Datei, und kann danach diese Datei komplett an die Datenbank zur Ausführung schicken. Bei MySQL funktioniert das wie folgt:

$ mysql -h $HOSTNAME -u $USERNAME -p$PASSWORD $DATABASE < MYSCRIPT.sql
$ mysql -h $HOSTNAME -u $USERNAME -p$PASSWORD $DATABASE < MYSCRIPT.sql > output.txt # Pipe output into file

Alternativ kann man das Skript über ein GUI-Tool wie z.B. PhpMyAdmin oder HeidiSQL ausführen.

Gerade bei komplexen Skripten sollte man die Möglichkeit nutzen, in SQL-Skripten Kommentare zu hinterlassen. Kommentare werden einfach mit einem # eingeleitet.

Übrigens lohnt sich vor komplexen Operationen, ein Backup der Datenbank zu erzeugen, bzw. erstmal an einer Kopie der Datenbank zu üben. Ein Backup erzeugt man mit einer einfachen Kommandozeile:

$ mysqldump -h $HOSTNAME -u $USERNAME -p$PASSWORD --skip-comments --add-drop-table --single-transaction --quick $DATABASE > dbdump-$(date "+%Y-%m-%d-%H-%M").sql

Sub-SELECT

Einer meiner größten Freunde für komplexe Fragen ist das Sub-SELECT. Damit kann eine WHERE-Bedingung die Werte eines anderen SELECT erhalten, und so komplexe Fragen lösen.

# Find categories without existing parents
SELECT * FROM categories WHERE categories.parent NOT IN (
  SELECT id FROM categories
);

Eine kleine Einschränkung bei schreibenden Operationen (INSERT, REPLACE, UPDATE, DELETE) ist, dass das Sub-SELECT sich nicht auf die selbe Tabelle beziehen darf, in der man gerade schreiben möchte. Es gibt aber tatsächlich einen kleinen Trick zum Schreiben mit Sub-SELECTs, den man dafür verwenden kann:

# Delete categories without existing parents
DELETE FROM categories WHERE categories.parent NOT IN (
  SELECT id FROM (
    SELECT id FROM categories
  ) AS tmp
);

Bedingte Ausführung

Bei der Änderung von Daten sollte man die Möglichkeiten von SQL nutzen, einige Kommandos fehlertoleranter zu machen. So kann ein INSERT fehlschlagen, wenn ein bereits vergebener UNIQUE-Key verwendet wird – ein INSERT IGNORE würde in diesem Fall davon ausgehen, dass die Zeile einfach nicht ausgetauscht werden muss, und macht mit der nächsten Zeile weiter.

Den Zusatz IGNORE gibt es auch für UPDATE und DELETE.

Bei der Erzeugung von neuen Tabellen hilft CREATE TABLE IF NOT EXISTS, bereits existierende Tabellen zu ignorieren.

SQL erzeugen… mit SQL

Gerade bei Migrationen kann es notwendig sein, Teile einer Datenbank in eine andere Form zu bringen. Wie bei jeder anderen Programmiersprache auch kann man mit SQL auch neues SQL erzeugen. Das Hilfsmittel dafür ist die SQL-Funktion CONCAT:

# Generate query for updating prices
SELECT
  CONCAT(
    "UPDATE articles_prices SET price = ", 
    products.products_price,
    " WHERE articles_prices.ean = ",
    QUOTE(products.products_model),
    ";"
  ) AS '# Update prices'
  FROM personal_offers
  JOIN products ON products.id = personal_offers.products_id
  WHERE personal_offers.type = 1
;

Dieses Ergebnis kann man z.B. als CSV ohne Trennzeichen exportieren lassen, was folgende Ausgabe erzeugt:

# Update prices
UPDATE articles_prices SET price = 12.40 WHERE articles_prices.ean = 'ABC 1';
UPDATE articles_prices SET price = 19.49 WHERE articles_prices.ean = 'ABC 2';
UPDATE articles_prices SET price = 14.99 WHERE articles_prices.ean = 'ABC 3';

Mit weiteren SQL-String-Funktionen wie z.B. IF oder REPLACE kann man auch komplexere Ausgaben erzeugen:

# Generate query for updating prices
SELECT
  CONCAT(
    "UPDATE articles_prices SET price = ", 
    IF(personal_offers.personal_offer = 0, products.products_price, personal_offers.personal_offer),
    " WHERE articles_prices.ean = ",
    QUOTE(REPLACE(products.products_model,' ','-')),
    ";"
  ) AS '# Update prices'
  FROM personal_offers
  JOIN products ON products.id = personal_offers.products_id
  WHERE personal_offers.type = 1
;

Die obigen Beispiele sind natürlich sehr trivial. Bei komplexen Migrationen können so aber relativ komplexe Operationen einfach gehandhabt werden, gerne auch zwischen infrastrukturell getrennten Datenbanken.

Oder ganz andere Dateien mit SQL erzeugen

Mit SQL kann man natürlich auch andere Dateien erzeugen als nur SQL-Skripte. Ein gutes Beispiel ist das Erzeugen einer statischen Redirect-Tabelle für den Apache:

SELECT 
  CONCAT(
    'RewriteRule\t/',
    tmp.url,
    '\thttps://%{HTTP_HOST}/',
    urls.path
    '\t[R=301,L]'
  ) AS '# Apache redirects'
  FROM tmp
  JOIN urls ON tmp.article_id = urls.article_id
  WHERE 1
;

Das generiert eine TSV-Datei, die man entweder in einer Tabellenkalkulation nachbearbeiten lassen kann – oder die passenden Texte, die man direkt in die Apache-Konfiguration übertragen lassen kann.

# Apache redirects
RewriteRule  /url_1/  https://%{HTTP_HOST}/url_a/  [R=301,L]
RewriteRule  /url_2/  https://%{HTTP_HOST}/url_b/  [R=301,L]

Fazit

Getreu nach dem Motto „Wenn man einen Hammer hat, sieht jedes Problem aus wie ein Nagel“ greifen wir als Programmierer bei der Lösung von SQL-Problemen meistens nicht zu SQL, sondern zu unserer Lieblings-Programmiersprache. Dabei nehmen wir zum Teil erhebliche Mehraufwände in Kauf (Aufbau der DB-Verbindung, Quoting, Transformationen).

Mit ein bisschen Kenntnis von SQL kann man diese Aufwände vermeiden, und gleichzeitig die komplette Kraft von SQL nutzen.