JSON sang SQL: câu lệnh INSERT, thiết kế bảng và bẫy ánh xạ kiểu

Kết luận: hãy quyết định một dòng đại diện cho thực thể nghiệp vụ nào trước khi thiết kế bảng, rồi viết VALUES nhiều dòng tường minh trong một transaction; chốt cách đặt tên trường và ánh xạ kiểu trước khi nhập thay vì vá chúng trong SQL.

JSON mô tả hình dạng; SQL còn mô tả ràng buộc. Khi bạn chuyển cái trước sang cái sau, mọi ràng buộc bạn không ghi ra sẽ quay lại sau này dưới dạng dữ liệu bẩn.

1. Quyết định trước: một bảng hay một bản ghi mỗi dòng JSON

Hãy nhìn lớp ngoài cùng của dữ liệu trước khi thiết kế bất kỳ bảng nào. Quyết định sai ở chỗ này thì mọi câu INSERT sau đó đều phải viết lại.

Chỉ có một tiêu chí: một dòng đại diện cho thực thể nghiệp vụ nào? Nếu một dòng là đơn hàng, các dòng chi tiết phải nằm ở bảng riêng. Nếu một dòng là người dùng, tag thuộc bảng con hoặc cột JSON, không phải chuỗi nối bằng dấu phẩy.

2. Từ mảng đến INSERT: mẫu chạy được nhỏ nhất

Dạng an toàn nhất luôn liệt kê tên cột tường minh và không bao giờ dựa vào thứ tự cột:

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

Ba chi tiết quan trọng:

• Viết mọi tên cột. Khi schema sau này thêm cột, không có gì bị lệch sang trường sai.
• VALUES nhiều dòng hoạt động trong SQLite, PostgreSQL và MySQL, và tiết kiệm rất nhiều vòng gọi so với việc lặp INSERT từng dòng.
• NULL là từ khóa; đừng đặt nó trong ngoặc. Viết 'NULL' sẽ lưu một chuỗi bốn ký tự.

Nếu khóa JSON không khớp tên cột (userId so với user_id), hãy thêm một lớp ánh xạ. Đừng đoán bằng COALESCE bên trong SQL. Sửa tên trường ở lớp dữ liệu rẻ hơn nhiều so với vá trong truy vấn.

3. Object lồng nhau: bảng cha cộng bảng con

Đơn hàng là trường hợp một-nhiều kinh điển:

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

Điều này tương ứng với JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Các điểm chính:

• Độ dài mảng items quyết định số dòng con; bảng cha nhận đúng một dòng.
• Lưu tiền dưới dạng số nguyên cent (total_cents), không bao giờ dùng FLOAT.
• Cho bảng con một khóa ngoại order_id và commit cả hai lệnh insert trong một transaction, nếu không cha có thể tồn tại trong khi các mục của nó bị thiếu.
• Nếu dữ liệu chỉ đọc và không ai lọc theo từng dòng hàng, một cột JSON là chấp nhận được (PostgreSQL jsonb, MySQL JSON, hoặc SQLite TEXT với các hàm json1), nhưng hãy chấp nhận cái giá: không đánh chỉ mục được và truy vấn khó.

Hãy làm phẳng các object lồng cố định như user.address thành cột (address_city, address_zip). Chỉ tách thành bảng khi lồng nhau có thể tiếp tục lớn lên; tách một hình dạng cố định chỉ thêm JOIN.

4. NULL so với trường thiếu: SQL không có "khóa vắng mặt"

JSON cho phép một khóa vắng mặt hoàn toàn. Cột SQL thì cố định. Sau khi nhập, cả hai trường hợp trông giống hệt — NULL — dù chúng có thể mang nghĩa khác nhau:

Hai lỗi phổ biến:

1. Dùng chuỗi rỗng thay vì NULL. Sau này WHERE email IS NULL sẽ bỏ sót những dòng đó, và chỉ số "người dùng không có email" của bạn âm thầm đếm thiếu.
2. Kiểm tra bằng col = NULL. Trong logic ba giá trị của SQL, NULL = NULL cho ra NULL, không phải TRUE. Bạn phải viết IS NULL:

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

Nếu cần giữ việc một khóa có tồn tại hay không, hãy thêm cột đánh dấu boolean thay vì suy ra từ NULL. Hợp đồng API cũng nên nêu điều này; xem Thiết kế JSON API và đặt tên trường.

5. Quoting và escaping: đừng bao giờ nối chuỗi SQL

Chuỗi JSON có thể chứa dấu nháy đơn, nháy kép, dấu gạch chéo ngược, xuống dòng, và cả \u0000. Với chuỗi tự dựng, một lần escape sót sẽ làm lệch phần còn lại của câu lệnh, và trong trường hợp tệ nhất trở thành SQL injection.

Trong SQL chuẩn, dấu nháy đơn được escape bằng cách nhân đôi:

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

Mã production nên dùng binding tham số và để driver xử lý escaping cùng kiểu:

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

Chỉ tự escape khi bạn buộc phải xuất tệp .sql, và khi đó tối thiểu:

• thay ' bằng '';
• nếu giữ xuống dòng, xác nhận cơ sở dữ liệu đích chấp nhận literal chuỗi nhiều dòng;
• loại bỏ \u0000, vì hầu hết cơ sở dữ liệu từ chối NUL trong chuỗi;
• MySQL mặc định coi dấu gạch chéo ngược là ký tự escape, nên \ phải thành \\, hoặc chuyển sang chế độ chuẩn.

Dấu nháy kép từ JSON không cần escape bên trong chuỗi SQL đặt trong nháy đơn. Thêm một lớp escaping nữa chính là cách các dấu gạch chéo lạc lọt vào dữ liệu của bạn.

6. Ánh xạ kiểu: sáu giá trị JSON đáp ở đâu trong SQL

JSON chỉ có string, number, boolean, null, object và array. SQL có hệ thống kiểu chi tiết hơn nhiều. Sai sót thường xảy ra ở độ chính xác và ngữ nghĩa chứ không phải cú pháp:

Boolean ẩn lỗi tinh vi nhất: JSON false trở thành 0 trong MySQL TINYINT(1), khứ hồi trông ổn, và không ai nhận ra. Nhưng khoảnh khắc một đoạn mã chạy if (value) trên chuỗi "false", nó được đánh giá là truthy. Script chuyển đổi nên gọi Boolean(value) một cách tường minh.

PostgreSQL JSONB phân tích và khử trùng khóa, giữ lần xuất hiện cuối, phù hợp cho truy vấn; JSON giữ nguyên văn bản gốc và thứ tự khóa. SQLite JSON chỉ là TEXT cộng một bộ hàm, không có ràng buộc kiểu thật. Trước khi chọn, hãy hỏi xem bảng này có cần lọc trên các trường bên trong JSON không.

7. Insert hàng loạt và transaction: theo lô, commit, chạy lại được

Khi khối lượng lớn lên, ba điều quyết định một lần nhập có còn kiểm soát được không:

1. Theo lô. Lặp N câu INSERT một dòng tốn N vòng mạng. Gộp 500 đến 1000 dòng vào một VALUES nhiều dòng, hoặc dùng bulk loader của chính cơ sở dữ liệu (PostgreSQL COPY, MySQL LOAD DATA, insert nhiều dòng trong một transaction SQLite).
2. Dùng transaction. Tất cả hoặc không gì cả, để một bảng nạp một nửa không bao giờ sống sót sau lỗi:

复制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. Làm cho nó idempotent. Script nhập bị chạy lại liên tục. Dùng upsert thay vì xóa-rồi-chèn:

ON CONFLICT cần một ràng buộc duy nhất hoặc khóa chính, nếu không nó không bao giờ kích hoạt. Khi không có khóa tự nhiên, hãy dựng chỉ mục duy nhất trên khóa nghiệp vụ như email. Khi hoàn toàn không có khóa, hãy thêm id ổn định phía JSON — đừng bao giờ dựa vào id tự tăng để idempotent, vì mỗi lần chạy lại sẽ chèn bản trùng.

8. Khác biệt dialect nhìn nhanh

Để một tệp SQL chạy được trên cả ba, tối thiểu hãy tránh ON CONFLICT, dấu backtick và SERIAL. Với dự án đa cơ sở dữ liệu, hãy sinh schema và câu upsert riêng; đừng kỳ vọng một script phù hợp mọi nơi.

9. Ba điểm kiểm tra sau khi nhập

Trước khi xóa tệp nguồn, hãy xác nhận ba điều:

1. Số dòng. SELECT COUNT(*) FROM users; phải bằng độ dài mảng JSON. Thiếu một dòng thường có nghĩa bản ghi cuối bị cắt.
2. Cột có tỷ lệ NULL cao nhất. Điều đó chỉ ra tên khóa không đồng nhất hoặc lồng nhau chưa được mở rộng. Quay lại xem nguồn có dùng cả userId và user_id không.
3. Tiền và định danh dài. Lấy giá trị lớn nhất và nhỏ nhất, xác nhận cả chuyển đổi dấu phẩy động lẫn số nguyên đều không cắt chúng.

Nếu cần hiểu hình dạng dạng bảng trước, hãy dùng cách làm phẳng trong Chuyển đổi JSON, CSV và dữ liệu dạng bảng. Nếu đối tượng là đồng nghiệp chỉ muốn đọc dữ liệu, bốn cách chuyển JSON sang Excel ít công hơn là dựng một cơ sở dữ liệu.

Vì sao số mất độ chính xác sau khi nhập JSON vào SQL?

Số nguyên trên 2^53 đã mất độ chính xác trong JSON.parse của JavaScript, và ghi vào BIGINT không thể phục hồi. Những id như vậy nên là chuỗi trong JSON và được chuyển sang BIGINT hoặc TEXT khi nhập.

Nên dùng gì làm khóa chính khi JSON không có id?

Để cơ sở dữ liệu sinh ra một khóa, hoặc tạo một UUID ổn định trước khi nhập. Đừng bao giờ dùng chỉ số mảng làm id: chạy lại sẽ sắp xếp lại dữ liệu và trỏ sai dòng vào sai bản ghi. Một chỉ mục duy nhất trên khóa nghiệp vụ (email, mã đơn hàng) cũng cho phép upsert.

Phải làm gì khi INSERT hàng loạt gặp xung đột khóa duy nhất?

Thay INSERT thường bằng upsert tương ứng với dialect của bạn, và xác nhận cột xung đột có ràng buộc duy nhất. Xóa trước rồi chèn sau cũng được, nhưng phải nằm trong một transaction và bạn chấp nhận một khoảng thời gian ngắn không có dòng nào.

Mảng lồng nhau có luôn cần bảng con không?

Không. Với mục đích hiển thị chỉ đọc mà không truy vấn theo mục con, một cột JSON đơn giản hơn. Ngay khi bạn cần lọc hoặc tổng hợp theo tag hay SKU, bảng con có chỉ mục là lựa chọn bền vững. Yếu tố quyết định là cách bạn truy vấn, không phải cách dữ liệu có hình dạng.