JSON nach SQL: INSERT-Statements, Tabellendesign und Typabbildungsfallen

Kurz gesagt: Entscheiden Sie, welche Geschäftsentität eine Zeile darstellt, bevor Sie Tabellen entwerfen, und schreiben Sie dann ausdrückliche Mehrzeilen-VALUES innerhalb einer Transaktion; klären Sie Feldbenennung und Typabbildung vor dem Import, statt sie in SQL zu flicken.

JSON beschreibt Form; SQL beschreibt auch Beschränkungen. Wenn Sie Ersteres in Letzteres überführen, kommt jede Beschränkung, die Sie nicht aufgeschrieben haben, später als schmutzige Daten zurück.

1. Zuerst entscheiden: eine Tabelle oder ein Datensatz pro JSON-Zeile

Sehen Sie sich die äußerste Ebene der Daten an, bevor Sie irgendeine Tabelle entwerfen. Falsch entschieden, muss jedes nachfolgende INSERT neu geschrieben werden.

Es gibt nur ein Kriterium: Welche Geschäftsentität stellt eine Zeile dar? Ist eine Zeile eine Bestellung, müssen die Positionen in ihrer eigenen Tabelle leben. Ist eine Zeile ein Nutzer, gehören Tags in eine Kindtabelle oder eine JSON-Spalte, nicht in einen kommagetrennten String.

2. Vom Array zum INSERT: die kleinste lauffähige Vorlage

Die sicherste Form listet die Spaltennamen immer ausdrücklich und verlässt sich nie auf die Spaltenreihenfolge:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

Drei Details zählen:

• Jeden Spaltennamen schreiben. Bekommt das Schema später eine Spalte, rutscht nichts ins falsche Feld.
• Mehrzeilige VALUES funktionieren in SQLite, PostgreSQL und MySQL und sparen im Vergleich zu einer Schleife einzelner Zeilen-Inserts eine große Zahl von Roundtrips.
• NULL ist ein Schlüsselwort; quotieren Sie es nicht. 'NULL' zu schreiben speichert einen String aus vier Zeichen.

Passen die JSON-Schlüssel nicht zu den Spaltennamen (userId versus user_id), fügen Sie eine Abbildungsschicht hinzu. Raten Sie nicht mit COALESCE im SQL. Feldnamen in der Datenschicht zu korrigieren ist weit billiger, als sie in Abfragen zu flicken.

3. Verschachtelte Objekte: eine Elterntabelle plus eine Kindtabelle

Bestellungen sind der klassische Eins-zu-viele-Fall:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

Das entspricht dem JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Kernpunkte:

• Die Länge des items-Arrays bestimmt die Zahl der Kindzeilen; die Elterntabelle bekommt genau eine Zeile.
• Geld als ganzzahlige Cent speichern (total_cents), nie als FLOAT.
• Der Kindtabelle einen Fremdschlüssel order_id geben und beide Inserts in einer Transaktion festschreiben, sonst kann das Elternteil existieren, während seine Positionen fehlen.
• Sind die Daten nur lesbar und filtert niemand nach Position, ist eine JSON-Spalte akzeptabel (PostgreSQL jsonb, MySQL JSON oder SQLite TEXT mit den json1-Funktionen), aber akzeptieren Sie die Kosten: keine Indizierung und umständliche Abfragen.

Feste verschachtelte Objekte wie user.address in Spalten abflachen (address_city, address_zip). Nur in eine Tabelle aufteilen, wenn die Verschachtelung weiter wachsen kann; eine feste Form aufzuteilen fügt nur JOINs hinzu.

4. NULL versus fehlende Felder: SQL kennt keinen „fehlenden Schlüssel“

JSON lässt einen Schlüssel vollständig fehlen. SQL-Spalten sind fest. Nach einem Import sehen beide Fälle identisch aus – NULL –, obwohl sie Unterschiedliches bedeuten können:

Zwei häufige Fehler:

1. Einen leeren String statt NULL verwenden. Später verfehlt WHERE email IS NULL diese Zeilen, und Ihre Metrik „Nutzer ohne E-Mail“ zählt stillschweigend zu niedrig.
2. Mit col = NULL testen. In SQLs dreiwertiger Logik ergibt NULL = NULL den Wert NULL, nicht TRUE. Sie müssen IS NULL schreiben:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

Müssen Sie bewahren, ob ein Schlüssel überhaupt vorhanden war, fügen Sie eine boolesche Markierungsspalte hinzu, statt es aus NULL zu schließen. Der API-Vertrag sollte das ebenfalls festhalten; siehe JSON-API-Design und Feldbenennung.

5. Quoting und Escaping: niemals SQL-Strings verketten

JSON-Strings können einfache Anführungszeichen, doppelte Anführungszeichen, Backslashes, Zeilenumbrüche und sogar \u0000 enthalten. Bei handgebauten Strings verschiebt ein vergessenes Escape den Rest des Statements und wird im schlimmsten Fall zu SQL-Injection.

In Standard-SQL wird ein einfaches Anführungszeichen durch Verdoppeln escaped:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

Produktionscode sollte Parameterbindung verwenden und den Treiber Escaping und Typen erledigen lassen:

复制// PostgreSQL uses $1/$2, MySQL uses ?; this example uses node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Escape von Hand nur, wenn Sie eine .sql-Datei exportieren müssen, und dann mindestens so:

• ' durch '' ersetzen;
• sind Zeilenumbrüche erhalten, bestätigen, dass die Ziel-Datenbank mehrzeilige String-Literale akzeptiert;
• \u0000 entfernen, da die meisten Datenbanken NUL in Strings ablehnen;
• MySQL behandelt Backslash standardmäßig als Escape-Zeichen, \ muss also zu \\ werden, oder wechseln Sie in den Standardmodus.

Doppelte Anführungszeichen aus JSON brauchen innerhalb eines einfach quotierten SQL-Strings kein Escaping. Eine zusätzliche Escaping-Schicht ist der Weg, auf dem verirrte Backslashes in Ihre Daten gelangen.

6. Typabbildung: wo die sechs JSON-Werte in SQL landen

JSON hat nur String, Zahl, Boolean, null, Objekt und Array. SQL hat ein viel feineres Typsystem. Die Fehler passieren meist bei Präzision und Semantik, nicht bei der Syntax:

Booleans verbergen den subtilsten Bug: JSON false wird in MySQL TINYINT(1) zu 0, Roundtrips sehen in Ordnung aus, und niemand merkt es. Doch sobald irgendein Code if (value) auf dem String "false" ausführt, wertet er ihn als wahr. Konvertierungsskripte sollten ausdrücklich Boolean(value) aufrufen.

PostgreSQL JSONB parst und dedupliziert Schlüssel und behält das letzte Vorkommen, was dem Abfragen zugutekommt; JSON bewahrt den Originaltext und die Schlüsselreihenfolge. SQLite JSON ist nur TEXT plus eine Reihe von Funktionen, ohne echte Typbeschränkung. Fragen Sie vor der Wahl, ob diese Tabelle Felder innerhalb des JSON filtern muss.

7. Massen-Inserts und Transaktionen: bündeln, festschreiben, wiederholbar machen

Mit wachsendem Volumen entscheiden drei Dinge, ob ein Import kontrollierbar bleibt:

1. Bündeln. Eine Schleife aus N Einzeilen-INSERT-Statements kostet N Netzwerk-Roundtrips. Fassen Sie 500 bis 1000 Zeilen in einem Mehrzeilen-VALUES zusammen oder verwenden Sie den eigenen Massenlader der Datenbank (PostgreSQL COPY, MySQL LOAD DATA, Mehrzeilen-Inserts innerhalb einer SQLite-Transaktion).
2. Eine Transaktion verwenden. Alles oder nichts, damit eine halb geladene Tabelle einen Fehler nie überlebt:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. Idempotent machen. Importskripte werden ständig erneut ausgeführt. Verwenden Sie ein Upsert statt Löschen-dann-Einfügen:

ON CONFLICT braucht eine Eindeutigkeitsbeschränkung oder einen Primärschlüssel, sonst greift es nie. Gibt es keinen natürlichen Schlüssel, bauen Sie einen eindeutigen Index auf einem Geschäftsschlüssel wie email. Gibt es gar keinen Schlüssel, ergänzen Sie auf der JSON-Seite eine stabile id – verlassen Sie sich nie auf eine Auto-Increment-id für Idempotenz, denn ein erneuter Lauf fügt Duplikate ein.

8. Dialektunterschiede auf einen Blick

Um eine SQL-Datei in allen drei lauffähig zu halten, vermeiden Sie mindestens ON CONFLICT, Backticks und SERIAL. Für datenbankübergreifende Projekte generieren Sie Schema und Upsert-Statements getrennt; erwarten Sie nicht, dass ein Skript für alle passt.

9. Drei Stichproben nach dem Import

Bevor Sie die Quelldatei löschen, prüfen Sie drei Dinge:

1. Zeilenzahl. SELECT COUNT(*) FROM users; sollte der JSON-Array-Länge entsprechen. Eine fehlende Zeile bedeutet meist, dass der letzte Datensatz abgeschnitten wurde.
2. Die Spalte mit der höchsten NULL-Rate. Sie deutet auf uneinheitliche Schlüsselnamen oder nicht expandierte Verschachtelung hin. Gehen Sie zurück und prüfen Sie, ob die Quelle sowohl userId als auch user_id verwendet.
3. Geldbeträge und lange Kennungen. Nehmen Sie Maximum und Minimum und bestätigen Sie, dass weder Gleitkomma- noch Ganzzahlkonvertierung sie abgeschnitten haben.

Müssen Sie zuerst die tabellarische Form verstehen, verwenden Sie den Abflachungsansatz in JSON, CSV und tabellarische Daten konvertieren. Sind die Adressaten Kollegen, die die Daten nur lesen wollen, sind die vier Wege, JSON nach Excel zu konvertieren weniger Arbeit, als eine Datenbank aufzusetzen.

Warum verlieren Zahlen nach dem Import von JSON in SQL Präzision?

Ganzzahlen oberhalb von 2^53 haben bereits während JSON.parse in JavaScript Präzision verloren, und sie in BIGINT zu schreiben kann sie nicht wiederherstellen. Solche IDs sollten in JSON Strings sein und beim Import zu BIGINT oder TEXT konvertiert werden.

Was sollte ich als Primärschlüssel verwenden, wenn das JSON keine id hat?

Lassen Sie die Datenbank einen generieren, oder erzeugen Sie vor dem Import eine stabile UUID. Verwenden Sie nie den Array-Index als id: Ein erneuter Lauf ordnet die Daten neu und zeigt die falschen Zeilen auf die falschen Datensätze. Ein eindeutiger Index auf einem Geschäftsschlüssel (E-Mail, Bestellnummer) ermöglicht auch Upserts.

Was tue ich, wenn ein Massen-INSERT auf einen Konflikt mit einem eindeutigen Schlüssel stößt?

Ersetzen Sie das einfache INSERT durch das passende Upsert Ihres Dialekts und bestätigen Sie, dass die konfliktverursachende Spalte eine Eindeutigkeitsbeschränkung hat. Erst löschen und dann einfügen funktioniert auch, muss aber in einer Transaktion geschehen, und Sie akzeptieren ein kurzes Fenster ohne Zeilen.

Brauchen verschachtelte Arrays immer eine Kindtabelle?

Nein. Für reine Anzeige ohne Abfrage nach Unterelementen ist eine JSON-Spalte einfacher. Sobald Sie nach Tag oder SKU filtern oder aggregieren müssen, ist eine Kindtabelle mit Indizes die nachhaltige Wahl. Der entscheidende Faktor ist, wie Sie abfragen, nicht wie die Daten geformt sind.