Tabelle mit Testdaten füllen: von INSERT-Anweisungen von Hand bis zum Generieren mit SQL

Fast alle DBAs, die ich kenne, waren vorher Entwickler oder sind es immer noch. Ich habe programmiert, bevor ich mich den Datenbanken gewidmet habe: 2008 habe ich die erste Version von Calíope begonnen, und seit 2016 arbeite ich als DBA. Aus diesen ersten Jahren als Entwickler ist mir ein Problem geblieben, das nichts Raffiniertes an sich hat und das wir alle kennen: Man legt eine neue Tabelle an und braucht Daten darin, um die Anwendung laufen zu sehen.

Dieser Beitrag geht die Wege durch, auf denen ich es gelöst habe, in der Reihenfolge, in der ich sie gelernt habe. Die Beispiele sind für MySQL geschrieben, und jeder Codeblock sagt, auf welchen Engines er unverändert läuft.

Alle verwenden diese Tabelle:

CREATE TABLE clientes (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100)  NOT NULL,
    email  VARCHAR(150)  NOT NULL,
    saldo  DECIMAL(10,2) NOT NULL DEFAULT 0,
    alta   DATE          NOT NULL
);

Auf den anderen Engines ändert sich nur das Autoinkrement: GENERATED ALWAYS AS IDENTITY in PostgreSQL und Oracle, IDENTITY(1,1) in SQL Server und INTEGER PRIMARY KEY in SQLite. ClickHouse hat kein Autoinkrement.

1. Ein INSERT nach dem anderen

Am Anfang habe ich die Daten aus Unerfahrenheit und weil ich keinen anderen Weg kannte, von Hand eingegeben. Ein INSERT, dann noch eins, dann noch eins:

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Ana Mora', 'ana@ejemplo.com', 150.00, '2010-03-01');

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Luis Rojas', 'luis@ejemplo.com', 80.50, '2010-03-02');

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Sofía Vargas', 'sofia@ejemplo.com', 0.00, '2010-03-05');

In Oracle schreibt man das Datum als DATE '2010-03-01': Eine bloße Zeichenkette wird mit dem Datumsformat der Sitzung gelesen, und mit dem Standardformat schlägt sie fehl.

Das funktioniert, hat aber zwei Probleme. Mehr als eine Handvoll Zeilen zu schreiben kostet viel Mühe, und die Daten sind immer dieselben. Mit drei Kunden merkt man nicht, dass die Seitenaufteilung kaputt ist, dass die Liste langsam ist oder dass ein langer Name die Ansicht sprengt.

2. Die Tabelle, die sich selbst kopiert

Später habe ich entdeckt, dass ein INSERT kein VALUES braucht: Es kann ein SELECT dahinter haben. Und wenn das SELECT aus derselben Tabelle liest, in die eingefügt wird, verdoppelt sich die Tabelle:

INSERT INTO clientes (nombre, email, saldo, alta)
SELECT nombre, email, saldo, alta
FROM clientes;

Jede Ausführung verdoppelt die Zeilen. Aus den drei vom Anfang werden 6, dann 12, 24 … und nach 18 Ausführungen sind es schon 786.432. Die Spalte id steht nicht in der Liste, also nummeriert die Engine die Kopien.

Das Problem ist, dass die Daten immer noch dieselben drei Zeilen sind, nur wiederholt, und wenn email einen eindeutigen Index hat, schlägt die zweite Ausführung fehl. Abhilfe schafft es, die Werte in der Kopie selbst zu variieren:

INSERT INTO clientes (nombre, email, saldo, alta)
SELECT CONCAT(nombre, ' ', id),
       CONCAT(id, '.', email),
       ROUND(RAND() * 1000, 2),
       alta + INTERVAL FLOOR(RAND() * 365) DAY
FROM clientes;

Auf anderen Engines ändern sich die Zufallsfunktion und die Datumsarithmetik:

• PostgreSQL: random() und alta + (random() * 365)::int.
• SQLite: abs(random()) % 1000 und date(alta, '+' || (abs(random()) % 365) || ' days').
• SQL Server: RAND() liefert für alle Zeilen der Anweisung denselben Wert. Damit er sich von Zeile zu Zeile ändert, nimmt man ABS(CHECKSUM(NEWID())) % 1000.
• Oracle: DBMS_RANDOM.VALUE(0, 1000).
• ClickHouse: rand() % 1000.

Eine Warnung: Das Wachstum ist exponentiell, und das gilt auch für das, was das Transaktionsprotokoll schreibt. Zwei Ausführungen zu viel, und die Tabelle ist viermal so groß.

3. Die Zeilen mit SQL erzeugen

Mit den Jahren habe ich gelernt, dass man nicht von vorhandenen Zeilen ausgehen muss: Die Engine selbst kann eine Zahlenreihe erzeugen, und aus jeder Zahl wird eine Zeile. In MySQL geht das mit einer rekursiven CTE:

SET SESSION cte_max_recursion_depth = 100000;

INSERT INTO clientes (nombre, email, saldo, alta)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 100000
)
SELECT CONCAT('Cliente ', n),
       CONCAT('cliente', n, '@ejemplo.com'),
       ROUND(RAND() * 10000, 2),
       DATE '2020-01-01' + INTERVAL FLOOR(RAND() * 2000) DAY
FROM serie;

Die erste Zeile ist nötig, weil MySQL die Rekursion standardmäßig nach 1000 Ebenen abbricht.

Jede Engine hat ihren eigenen Weg, die Reihe zu erzeugen, und bei manchen ist er viel kürzer:

SELECT seq FROM seq_1_to_100000;

SELECT n FROM generate_series(1, 100000) AS n;

SELECT value FROM GENERATE_SERIES(1, 100000);

SELECT LEVEL FROM dual CONNECT BY LEVEL <= 100000;

SELECT number + 1 FROM numbers(100000);

In SQLite, und in SQL Server vor 2022, verwendet man die rekursive CTE:

WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 100000
)
SELECT n FROM serie;

SQL Server kennt kein RECURSIVE und bricht die Rekursion nach 100 Ebenen ab. Eine Option am Ende hebt die Grenze auf:

WITH serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 100000
)
SELECT n FROM serie
OPTION (MAXRECURSION 0);

Seit wann geht das?

2008 wusste ich nicht, dass es das gibt, und in MySQL, das ich damals benutzte, gab es das tatsächlich nicht. Das ist die zeitliche Abfolge, von der ältesten zur neuesten:

• PostgreSQL 8.0, Januar 2005: generate_series. Rekursive CTEs kamen später, mit 8.4 (2009).
• SQL Server 2005, November 2005: rekursive CTEs. GENERATE_SERIES kam erst mit SQL Server 2022.
• Oracle: CONNECT BY LEVEL auf dual wurde schon in Oracle 10g (2004) als Zeilengenerator verwendet. Die rekursive Standard-CTE kam mit 11g R2 (2009).
• SQLite 3.8.3, Februar 2014: WITH RECURSIVE.
• MariaDB 10.0 (2014): die Sequence-Engine und ihre Tabellen seq_1_to_N. Rekursive CTEs in MariaDB 10.2 (2017).
• ClickHouse: numbers() gibt es, seit es 2016 als Open Source veröffentlicht wurde.
• MySQL 8.0, April 2018: rekursive CTEs. Davor gab es keine Möglichkeit, eine Reihe mit einer einzigen Anweisung zu erzeugen.

Mit anderen Worten: 2008 konnten PostgreSQL, SQL Server und Oracle das schon. In MySQL musste man zehn Jahre länger warten, und bis dahin blieb nur, die Tabelle zu verdoppeln, oder eine Prozedur mit einer Schleife.

4. Eine Prozedur, die die Tabelle liest

Als ich etwas professioneller geworden war, habe ich eine gespeicherte Prozedur geschrieben, die diese Arbeit für mich erledigte: Sie las die Spalten der Tabelle und ihre Typen aus information_schema und baute ein INSERT mit einem passenden Zufallswert für jede Spalte.

DELIMITER //
CREATE PROCEDURE poblar(IN p_tabla VARCHAR(64), IN p_filas INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE v_columnas TEXT;
    DECLARE v_valores  TEXT;

    SELECT GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY ordinal_position),
           GROUP_CONCAT(
               CASE
                   WHEN data_type IN ('int', 'bigint', 'smallint', 'tinyint')
                       THEN 'FLOOR(RAND() * 1000)'
                   WHEN data_type IN ('decimal', 'double', 'float')
                       THEN 'ROUND(RAND() * 1000, 2)'
                   WHEN data_type IN ('date', 'datetime', 'timestamp')
                       THEN 'CURRENT_DATE - INTERVAL FLOOR(RAND() * 2000) DAY'
                   ELSE CONCAT('LEFT(SHA2(RAND(), 256), ',
                               LEAST(IFNULL(character_maximum_length, 32), 32), ')')
               END
               ORDER BY ordinal_position)
      INTO v_columnas, v_valores
      FROM information_schema.columns
     WHERE table_schema = DATABASE()
       AND table_name = p_tabla
       AND extra NOT LIKE '%auto_increment%';

    SET @sql = CONCAT('INSERT INTO `', p_tabla, '` (', v_columnas, ') VALUES (', v_valores, ')');
    PREPARE sentencia FROM @sql;
    WHILE i < p_filas DO
        EXECUTE sentencia;
        SET i = i + 1;
    END WHILE;
    DEALLOCATE PREPARE sentencia;
END //
DELIMITER ;

CALL poblar('clientes', 1000);

Die anderen Engines haben ihren eigenen Spaltenkatalog (information_schema in PostgreSQL und SQL Server, ALL_TAB_COLUMNS in Oracle, pragma_table_info in SQLite) und ihre eigene prozedurale Sprache, also muss man sie für jede neu schreiben.

5. Heute, mit Calíope

Diese Prozedur ist der Vorfahre einer Funktion von Calíope. In Kataloge, in der Tabellenliste, hat das Kontextmenü einer Tabelle den Eintrag Mit Dummy-Daten füllen (Rechtsklick auf dem Mac, gedrückt halten auf dem iPad). Man wählt, wie viele Zeilen man möchte, von 1 bis 5000, und Calíope:

• liest die Spalten und ihre Typen aus dem Katalog des Servers, einschließlich der Werte eines ENUM;
• überspringt Autoinkrement- und berechnete Spalten, die die Engine selbst füllt;
• erzeugt für den Typ jeder Spalte einen gültigen Beispielwert und zeigt einen davon vor dem Start;
• fügt in Stapeln von 50 Zeilen ein, mit Fortschrittsbalken, und wenn ein Stapel fehlschlägt, sagt es, warum: ein Wert außerhalb des Bereichs, eine CHECK-Bedingung, ein Duplikat.

In Spalten, die Fremdschlüssel sind, sind die Werte ebenfalls zufällig. In MySQL, MariaDB und PostgreSQL schaltet Calíope die Prüfung während des Ladens ab, sodass Zeilen übrig bleiben können, die auf ein nicht vorhandenes übergeordnetes Element verweisen. Das ist nützlich, um zu testen, wie die Anwendung auf einen JOIN ohne Treffer reagiert. In SQL Server und Oracle bleibt die Prüfung eingeschaltet, und ein Stapel mit einem Wert, den es in der übergeordneten Tabelle nicht gibt, schlägt mit seiner Meldung fehl.

Es funktioniert mit MySQL, MariaDB, PostgreSQL, SQL Server, Oracle und ClickHouse.

Was ich wofür verwende

Keiner dieser Wege ist aus meiner Arbeit verschwunden:

• Ein paar INSERT von Hand, wenn ich bestimmte Daten brauche, die ich kenne, etwa den Fall, der einen Fehler reproduziert.
• Die mit SQL erzeugte Reihe, wenn ich Menge brauche und steuern will, wie sich die Werte verteilen: hunderttausend Kunden, 80 % davon im letzten Jahr angelegt.
• Calíope, wenn die Tabelle nur irgendetwas enthalten soll, damit ich die Ansicht funktionieren sehe.