Lưu mảng JSON vào cơ sở dữ liệu MySQL với kiểu dữ liệu JSON

Từ phiên bản 5.7, MySQL hỗ trợ kiểu dữ liệu JSON gốc, cho phép lưu trữ, truy vấn, và thao tác trên tài liệu JSON hiệu quả. Đây là cách chuẩn để lưu mảng hoặc đối tượng JSON thay vì lưu dạng TEXT/VARCHAR.

Ví dụ minh họa

Tạo bảng với cột JSON:

mysql> CREATE TABLE DemoTable1438 (
    EmployeeDetails JSON
);

Chèn mảng JSON (3 đối tượng nhân viên):

mysql> INSERT INTO DemoTable1438 VALUES (
    '[{"EmployeeId":"EMP-101","EmployeeName":"Chris"},
      {"EmployeeId":"EMP-102","EmployeeName":"David"},
      {"EmployeeId":"EMP-103","EmployeeName":"Sam"}]'
);

Xem dữ liệu (MySQL tự format đẹp):

mysql> SELECT * FROM DemoTable1438;

+----------------------------------------------------------------------------------+
| EmployeeDetails                                                                  |
+----------------------------------------------------------------------------------+
| [{"EmployeeId": "EMP-101", "EmployeeName": "Chris"}, {"EmployeeId": "EMP-102", ... |
+----------------------------------------------------------------------------------+

Các thao tác phổ biến với cột JSON

1. Trích xuất giá trị (-> và ->>)

-- Lấy phần tử đầu tiên (index 0), trả về JSON
SELECT EmployeeDetails->'$[0]' FROM DemoTable1438;
-- {"EmployeeId": "EMP-101", "EmployeeName": "Chris"}

-- Lấy giá trị text của EmployeeName phần tử đầu (->> bỏ dấu ngoặc)
SELECT EmployeeDetails->>'$[0].EmployeeName' FROM DemoTable1438;
-- Chris

2. Tìm kiếm trong JSON

-- Tìm hàng có nhân viên tên 'David'
SELECT * FROM DemoTable1438 
WHERE JSON_CONTAINS(EmployeeDetails, '{"EmployeeName": "David"}', '$');

3. Cập nhật một phần (JSON_SET, JSON_REMOVE)

-- Thêm/sửa thuộc tính
UPDATE DemoTable1438 
SET EmployeeDetails = JSON_SET(EmployeeDetails, '$[0].Department', 'IT');

-- Xóa phần tử
UPDATE DemoTable1438 
SET EmployeeDetails = JSON_REMOVE(EmployeeDetails, '$[1]');

4. Index trên JSON (Virtual Column + Index)

ALTER TABLE DemoTable1438 
ADD COLUMN first_emp_name VARCHAR(20) 
GENERATED ALWAYS AS (EmployeeDetails->>'$[0].EmployeeName') STORED,
ADD INDEX idx_first_emp_name (first_emp_name);

Lợi ích của kiểu JSON so với TEXT/VARCHAR

• Kiểm tra tính hợp lệ: MySQL từ chối JSON không hợp lệ khi INSERT/UPDATE.
• Truy vấn mạnh: JSON_EXTRACT, JSON_SEARCH, JSON_CONTAINS, JSON_TABLE (MySQL 8.0).
• Index: Tạo index trên virtual column trích xuất từ JSON.
• Lưu trữ tối ưu: Dạng nhị phân nội bộ, đọc/ghi nhanh hơn TEXT.

Lưu ý: Kích thước tài liệu JSON tối đa được cấu hình bởi max_allowed_packet (mặc định 4MB - 1GB tùy cấu hình).