JSON do SQL: instrukcje INSERT, projekt tabeli i pułapki mapowania typów

Najważniejsze: przed projektowaniem tabel zdecyduj, jaką jednostkę biznesową reprezentuje jeden wiersz, a następnie zapisz jawny wielowierszowy VALUES w obrębie jednej transakcji; nazewnictwo pól i mapowanie typów ustal przed importem, zamiast łatować je w SQL.

JSON opisuje kształt; SQL opisuje także ograniczenia. Przenosząc pierwsze do drugiego, każde niewypisane ograniczenie wraca później jako brudne dane.

1. Zdecyduj najpierw: jedna tabela czy jeden rekord per wiersz JSON

Zanim zaprojektujesz jakąkolwiek tabelę, spójrz na zewnętrzną warstwę danych. Błędna decyzja oznacza, że każdy późniejszy INSERT trzeba przepisać.

Kryterium jest tylko jedno: jaką jednostkę biznesową reprezentuje jeden wiersz? Jeśli wiersz to zamówienie, pozycje zamówienia muszą żyć we własnej tabeli. Jeśli wiersz to użytkownik, tagi należą do tabeli podrzędnej albo kolumny JSON, a nie do łańcucha rozdzielanego przecinkami.

2. Z tablicy do INSERT: najmniejszy wykonywalny szablon

Najbezpieczniejsza forma zawsze wymienia nazwy kolumn jawnie i nigdy nie polega na kolejności kolumn:

复制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);

Trzy szczegóły mają znaczenie:

• Wpisz każdą nazwę kolumny. Gdy schemat później zyska kolumnę, nic nie przesunie się w złe pole.
• Wielowierszowy VALUES działa w SQLite, PostgreSQL i MySQL i oszczędza wiele rund w porównaniu z pętlą wstawiającą pojedyncze wiersze.
• NULL to słowo kluczowe; nie ujmuj go w cudzysłowy. Zapis 'NULL' przechowuje czteroznakowy łańcuch.

Jeśli klucze JSON nie zgadzają się z nazwami kolumn (userId versus user_id), dodaj warstwę mapowania. Nie zgaduj za pomocą COALESCE w SQL. Naprawianie nazw pól w warstwie danych jest znacznie tańsze niż łatowanie ich w zapytaniach.

3. Zagnieżdżone obiekty: tabela nadrzędna plus tabela podrzędna

Zamówienia to klasyczny przypadek jeden-do-wielu:

复制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);

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

Kluczowe punkty:

• Długość tablicy items określa liczbę wierszy podrzędnych; tabela nadrzędna dostaje dokładnie jeden wiersz.
• Przechowuj pieniądze jako liczby całkowite w najmniejszej jednostce (total_cents), nigdy jako FLOAT.
• Nadaj tabeli podrzędnej klucz obcy order_id i zatwierdź oba wstawienia w jednej transakcji, inaczej rodzic może istnieć, a jego pozycji brakować.
• Jeśli dane są tylko do odczytu i nikt nie filtruje po pozycji, kolumna JSON jest akceptowalna (PostgreSQL jsonb, MySQL JSON albo SQLite TEXT z funkcjami json1), ale zaakceptuj koszt: brak indeksowania i niewygodne zapytania.

Stałe zagnieżdżone obiekty, takie jak user.address, spłaszczaj do kolumn (address_city, address_zip). Dziel na tabelę tylko wtedy, gdy zagnieżdżenie może dalej rosnąć; dzielenie stałego kształtu dodaje tylko JOIN-y.

4. NULL a brakujące pola: SQL nie ma „nieobecnego klucza”

JSON pozwala, aby klucz w ogóle nie istniał. Kolumny SQL są stałe. Po imporcie oba przypadki wyglądają identycznie — NULL — choć mogą znaczyć co innego:

Dwa częste błędy:

1. Używanie pustego łańcucha zamiast NULL. Później WHERE email IS NULL pomija te wiersze, a metryka „użytkownicy bez e-maila” po cichu zaniża wynik.
2. Testowanie przez col = NULL. W trójwartościowej logice SQL NULL = NULL daje NULL, a nie TRUE. Trzeba zapisać IS NULL:

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

Jeśli musisz zachować informację, czy klucz w ogóle istniał, dodaj logiczną kolumnę znacznika, zamiast wnioskować to z NULL. Kontrakt API też powinien to określać; zobacz Projektowanie API JSON i nazewnictwo pól.

5. Cytowanie i ucieczka: nigdy nie sklejaj łańcuchów SQL

Łańcuchy JSON mogą zawierać pojedyncze cudzysłowy, podwójne cudzysłowy, backslashe, nowe linie, a nawet \u0000. Przy ręcznie budowanych łańcuchach jedna pominięta ucieczka przesuwa resztę instrukcji, a w najgorszym przypadku staje się wstrzyknięciem SQL.

W standardowym SQL pojedynczy cudzysłów escape'uje się przez podwojenie:

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

Kod produkcyjny powinien używać wiązania parametrów i pozwolić sterownikowi zająć się ucieczką oraz typami:

复制// 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]);

Ręcznie escape'uj tylko wtedy, gdy musisz wyeksportować plik .sql, a wtedy co najmniej:

• zastąp ' przez '';
• jeśli zachowujesz nowe linie, potwierdź, że docelowa baza akceptuje wieloliniowe literały łańcuchowe;
• usuń \u0000, ponieważ większość baz odrzuca NUL wewnątrz łańcuchów;
• MySQL domyślnie traktuje backslash jako znak ucieczki, więc \ musi stać się \\ albo przełącz się na tryb standardowy.

Podwójne cudzysłowy z JSON nie wymagają ucieczki wewnątrz łańcucha SQL w pojedynczych cudzysłowach. Dodanie dodatkowej warstwy ucieczki to sposób, w jaki w danych zostają zbłąkane backslashe.

6. Mapowanie typów: gdzie sześć wartości JSON trafia w SQL

JSON ma tylko łańcuch, liczbę, wartość logiczną, null, obiekt i tablicę. SQL ma znacznie subtelniejszy system typów. Błędy zdarzają się zwykle w precyzji i semantyce, a nie w składni:

Wartości logiczne ukrywają najsubtelniejszy błąd: JSON false staje się 0 w MySQL TINYINT(1), obieg wygląda dobrze i nikt nie zauważa. Ale gdy jakiś kod wykona if (value) na łańcuchu "false", zostanie on potraktowany jako prawda. Skrypty konwersji powinny jawnie wywoływać Boolean(value).

PostgreSQL JSONB parsuje i deduplikuje klucze, zachowując ostatnie wystąpienie, co pasuje do odpytywania; JSON zachowuje oryginalny tekst i kolejność kluczy. SQLite JSON to po prostu TEXT plus zestaw funkcji, bez prawdziwego ograniczenia typu. Przed wyborem zapytaj, czy ta tabela wymaga filtrowania po polach wewnątrz JSON-a.

7. Wstawianie zbiorcze i transakcje: partiami, z zatwierdzeniem, powtarzalne

Wraz ze wzrostem wolumenu trzy rzeczy decydują, czy import pozostaje kontrolowany:

1. Partiami. Pętla N pojedynczych INSERT kosztuje N rund sieciowych. Połącz 500 do 1000 wierszy w jeden wielowierszowy VALUES albo użyj własnego ładowarki zbiorczej bazy (PostgreSQL COPY, MySQL LOAD DATA, wielowierszowe wstawienia wewnątrz transakcji SQLite).
2. Użyj transakcji. Wszystko albo nic, aby na wpół załadowana tabela nigdy nie przetrwała błędu:

复制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. Uczyń go idempotentnym. Skrypty importu są ciągle uruchamiane ponownie. Użyj upsert, zamiast usuwania i wstawiania:

ON CONFLICT wymaga unikalnego ograniczenia lub klucza głównego, inaczej nigdy nie zadziała. Gdy brak naturalnego klucza, zbuduj unikalny indeks na kluczu biznesowym, takim jak email. Gdy nie ma żadnego klucza, dodaj stabilny identyfikator po stronie JSON — nigdy nie polegaj na identyfikatorze autoinkrementacyjnym w idempotencji, ponieważ ponowne uruchomienie wstawi duplikaty.

8. Różnice dialektów w skrócie

Aby jeden plik SQL działał w całej trójce, przynajmniej unikaj ON CONFLICT, backticks i SERIAL. W projektach między bazami generuj schemat i instrukcje upsert osobno; nie oczekuj, że jeden skrypt pasuje do wszystkich.

9. Trzy kontrole po imporcie

Zanim usuniesz plik źródłowy, zweryfikuj trzy rzeczy:

1. Liczbę wierszy. SELECT COUNT(*) FROM users; powinno równać się długości tablicy JSON. Brakujący wiersz zwykle oznacza, że ostatni rekord został obcięty.
2. Kolumna o najwyższym współczynniku NULL. Wskazuje na niespójne nazwy kluczy lub nierozwinięte zagnieżdżenie. Wróć i sprawdź, czy źródło używa zarówno userId, jak i user_id.
3. Pieniądze i długie identyfikatory. Wybierz wartość maksymalną i minimalną i potwierdź, że ani zmiennoprzecinkowe, ani konwersja całkowitoliczbowa ich nie obcięły.

Jeśli najpierw musisz zrozumieć kształt tabelaryczny, użyj podejścia spłaszczania z Konwersja JSON, CSV i danych tabelarycznych. Jeśli odbiorcami są współpracownicy, którzy chcą tylko przeczytać dane, cztery sposoby konwersji JSON do Excel wymagają mniej pracy niż postawienie bazy danych.

Dlaczego liczby tracą precyzję po imporcie JSON do SQL?

Liczby całkowite powyżej 2^53 utraciły już precyzję podczas JSON.parse w JavaScript i zapisanie ich do BIGINT nie przywróci jej. Takie identyfikatory powinny być łańcuchami w JSON-ie i konwertowane do BIGINT lub TEXT podczas importu.

Czego użyć jako klucza głównego, gdy JSON nie ma identyfikatora?

Pozwól bazie wygenerować go albo utwórz stabilny UUID przed importem. Nigdy nie używaj indeksu tablicy jako identyfikatora: ponowne uruchomienie zmienia kolejność danych i wskazuje niewłaściwe wiersze na niewłaściwe rekordy. Unikalny indeks na kluczu biznesowym (e-mail, numer zamówienia) umożliwia też upserty.

Co zrobić, gdy zbiorczy INSERT trafia na konflikt unikalnego klucza?

Zastąp zwykły INSERT pasującym upsertem dla twojego dialektu i potwierdź, że konfliktowa kolumna ma unikalne ograniczenie. Usunięcie a potem wstawienie też działa, ale musi odbyć się w jednej transakcji i akceptujesz krótkie okno bez wierszy.

Czy zagnieżdżone tablice zawsze wymagają tabeli podrzędnej?

Nie. Do odczytu bez odpytywania po podobiekcie kolumna JSON jest prostsza. Gdy tylko zaczynasz filtrować lub agregować po tagu albo SKU, tabela podrzędna z indeksami jest trwałym wyborem. Decyduje sposób odpytywania, a nie kształt danych.