Xử lý dữ liệu dạng mảng trong MySQL một cách hiệu quả bằng các kiểu JSON

1. Giới thiệu

Cần xử lý dữ liệu dạng mảng trong MySQL

Trong các cơ sở dữ liệu, dữ liệu thường được lưu trữ theo thiết kế quan hệ. Tuy nhiên, tùy thuộc vào yêu cầu của ứng dụng, việc lưu trữ nhiều giá trị trong một cột có thể hữu ích. Trong trường hợp như vậy, một cấu trúc dữ liệu dạng “mảng” có thể giúp ích.

Ví dụ, hãy xem các trường hợp sau:

• Lưu trữ nhiều thẻ (tags) được người dùng chọn.
• Lưu trữ nhiều URL hình ảnh cho một sản phẩm.
• Hợp nhất lịch sử hoặc nhật ký vào một trường duy nhất.

Ưu điểm khi tận dụng kiểu JSON

MySQL không cung cấp một “kiểu mảng” trực tiếp, nhưng bằng cách sử dụng kiểu JSON, bạn có thể xử lý dữ liệu dạng mảng. Kiểu JSON rất linh hoạt và cung cấp những lợi thế sau:

• Hỗ trợ cấu trúc dữ liệu lồng nhau.
• Cho phép thao tác dữ liệu trực tiếp trong các truy vấn.
• Quản lý nhiều định dạng dữ liệu trong một trường.

Trong bài viết này, chúng ta sẽ giới thiệu cách xử lý dữ liệu dạng mảng hiệu quả trong MySQL bằng kiểu JSON.

2. Cơ bản về xử lý mảng với kiểu JSON của MySQL

Kiểu JSON là gì?

JSON (JavaScript Object Notation) là một định dạng trao đổi dữ liệu nhẹ và đơn giản. MySQL hỗ trợ kiểu JSON gốc từ phiên bản 5.7 trở đi, cho phép bạn lưu và thao tác dữ liệu dạng JSON trực tiếp trong cơ sở dữ liệu.

Ví dụ: dữ liệu dưới đây có thể được lưu trữ trong một trường kiểu JSON:

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

Lợi ích và các trường hợp sử dụng của kiểu JSON

Những lợi thế chính khi sử dụng kiểu JSON là:

1. Cấu trúc dữ liệu linh hoạt : Bạn có thể xử lý dữ liệu có độ dài biến đổi mà không cần thay đổi sơ đồ quan hệ.
2. Thao tác dữ liệu hiệu quả : Sử dụng các hàm nội bộ của MySQL (ví dụ JSON_EXTRACT, JSON_ARRAY) để thao tác dữ liệu một cách dễ dàng.
3. Thiết kế không cần schema : Bạn không cần sửa đổi cấu trúc thường xuyên khi các yêu cầu của ứng dụng thay đổi.

Các trường hợp sử dụng:

• Gán nhiều danh mục cho thông tin sản phẩm.
• Lưu trữ cài đặt tùy chỉnh của người dùng.
• Dùng trong các ứng dụng web xử lý dữ liệu JSON lồng nhau.

3. Các thao tác cơ bản trên mảng JSON

Tạo mảng JSON

Trong MySQL, bạn có thể sử dụng hàm JSON_ARRAY để dễ dàng tạo các mảng có định dạng JSON. Mảng hữu ích khi bạn muốn lưu nhiều giá trị trong một cột.

Ví dụ sử dụng

Trong truy vấn sau chúng ta tạo một mảng JSON có tên tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Kết quả:

["PHP", "MySQL", "JavaScript"]

Ví dụ áp dụng

Dưới đây là một ví dụ lưu một mảng JSON vào cơ sở dữ liệu thông qua câu lệnh INSERT.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Trích xuất dữ liệu từ mảng JSON

Để truy xuất dữ liệu được lưu trong một mảng JSON, bạn sử dụng hàm JSON_EXTRACT. Nó cho phép bạn lấy các phần tử cụ thể trong mảng.

Ví dụ sử dụng

Trong ví dụ sau, chúng ta lấy phần tử thứ hai (đánh số bắt đầu từ 0) của mảng.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Kết quả:

"MySQL"

Truy xuất nhiều phần tử

Bạn cũng có thể lấy nhiều phần tử cùng một lúc.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Thêm, cập nhật và xóa dữ liệu

Thêm dữ liệu vào mảng

Bằng cách sử dụng hàm JSON_ARRAY_APPEND, bạn có thể thêm dữ liệu mới vào một mảng hiện có.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Kết quả:

["PHP", "MySQL", "JavaScript"]

Cập nhật dữ liệu trong mảng

Bạn có thể cập nhật một phần tử cụ thể trong mảng bằng JSON_SET.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Kết quả:

["PHP", "Python", "JavaScript"]

Xóa dữ liệu khỏi mảng

Sử dụng JSON_REMOVE để xóa một phần tử cụ thể trong mảng.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Kết quả:

["PHP", "JavaScript"]

4. Tìm kiếm và lọc mảng JSON

Tìm kiếm các mảng chứa dữ liệu cụ thể

Để xác định xem một mảng JSON có chứa giá trị cụ thể không, bạn sử dụng hàm JSON_CONTAINS. Hàm này trả về true nếu mảng JSON đã cho chứa giá trị được chỉ định.

Ví dụ sử dụng

Trong ví dụ sau, chúng ta kiểm tra xem mảng JSON có chứa “MySQL” không.

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Kết quả:

1  (if present)
0  (if not present)

Ví dụ áp dụng: tìm kiếm có điều kiện

Nếu bạn muốn truy vấn các hàng trong cơ sở dữ liệu chứa mảng JSON có giá trị cụ thể, bạn có thể sử dụng JSON_CONTAINS trong mệnh đề WHERE.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

Truy vấn này lấy các hàng mà cột tags bao gồm “MySQL”.

Lấy độ dài của mảng

Để lấy số lượng phần tử trong một mảng JSON, sử dụng hàm JSON_LENGTH. Hàm này hữu ích cho phân tích dữ liệu hoặc lọc có điều kiện.

Ví dụ sử dụng

Trong ví dụ sau, chúng ta lấy số lượng phần tử trong mảng.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Kết quả:

3

Ví dụ thực tiễn: trích xuất hàng đáp ứng điều kiện

Để trích xuất các hàng mà số lượng phần tử bằng hoặc lớn hơn một giá trị nhất định, sử dụng JSON_LENGTH trong mệnh đề WHERE.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

Truy vấn này chọn các hàng mà cột tags có hai hoặc nhiều hơn phần tử.

Ví dụ thực tiễn với truy vấn có điều kiện

Bạn có thể kết hợp nhiều điều kiện để thực hiện tìm kiếm nâng cao hơn. Truy vấn sau tìm các hàng mà mảng tags chứa “JavaScript” và có ba hoặc nhiều hơn phần tử.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Các trường hợp thực tế: học qua các kịch bản thực tế cho mảng JSON

Cách lưu trữ danh mục sản phẩm dưới dạng mảng JSON

Trong các trang thương mại điện tử, sản phẩm có thể thuộc nhiều danh mục. Trong những trường hợp này, sử dụng mảng JSON để lưu trữ thông tin danh mục có thể hiệu quả.

Ví dụ: lưu trữ dữ liệu danh mục cho sản phẩm

Dưới đây là ví dụ về tạo bảng với cột JSON có tên categories và lưu nhiều danh mục.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

Cấu trúc dữ liệu này cho phép lưu trữ ngắn gọn ngay cả khi một sản phẩm thuộc nhiều danh mục.

Truy vấn sản phẩm thuộc danh mục cụ thể

Bằng cách tận dụng kiểu dữ liệu JSON, bạn có thể dễ dàng tìm kiếm sản phẩm thuộc danh mục cụ thể.

Ví dụ truy vấn

Truy vấn sau tìm tất cả các sản phẩm trong danh mục “Electronics”.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Kết quả:

Laptop
Smartphone

Truy vấn này cho phép bạn linh hoạt lấy danh sách sản phẩm theo danh mục.

Ví dụ: lọc theo khoảng giá

Hãy xem cách lưu thông tin giá trong JSON và truy vấn sản phẩm dựa trên khoảng giá.

Ví dụ dữ liệu

Dưới đây, chúng ta lưu thông tin giá của từng sản phẩm bằng cách sử dụng kiểu JSON.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Ví dụ truy vấn

Để tìm kiếm các sản phẩm có giá 100.000 hoặc hơn, bạn sử dụng JSON_EXTRACT.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Kết quả:

Laptop

Sử dụng JSON_TABLE để mở rộng và ví dụ truy vấn

Khi bạn muốn truy vấn dữ liệu JSON dưới dạng quan hệ, bạn có thể sử dụng JSON_TABLE. Hàm này cho phép bạn mở rộng một mảng JSON thành một bảng ảo.

Ví dụ sử dụng

Dưới đây là một ví dụ nơi mảng JSON được mở rộng và mỗi danh mục được hiển thị dưới dạng một dòng riêng biệt.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Kết quả:

category_name
--------------
Electronics
Computers
Mobile Devices

6. Lưu ý khi sử dụng kiểu JSON

Các điểm tối ưu hiệu suất

Mặc dù kiểu JSON rất linh hoạt, nếu không thiết kế đúng có thể ảnh hưởng tiêu cực đến hiệu suất của cơ sở dữ liệu. Dưới đây là các điểm tối ưu hiệu suất chính.

1. Sử dụng chỉ mục

Trong MySQL bạn không thể trực tiếp đặt chỉ mục lên cột JSON, nhưng bạn có thể tạo một cột ảo và chỉ mục một khóa cụ thể.

Ví dụ: tạo chỉ mục thông qua cột ảo

Trong ví dụ này chúng ta chỉ mục khóa price trong dữ liệu JSON.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Bằng cách sử dụng cột ảo, bạn có thể cải thiện đáng kể hiệu suất tìm kiếm trên dữ liệu kiểu JSON.

2. Tránh cấu trúc JSON quá phức tạp

Cấu trúc JSON lồng sâu ảnh hưởng đến độ dễ đọc của truy vấn và hiệu suất. Khi thiết kế dữ liệu, hãy áp dụng cấu trúc JSON càng đơn giản càng tốt.

Ví dụ tốt:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Cấu trúc cần tránh:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

Cách sử dụng chỉ mục hiệu quả

Khi sử dụng cột ảo để tạo chỉ mục, các điểm sau cần được xem xét:

1. Cột ảo phải được STORED.
2. Sử dụng JSON_EXTRACT để trích xuất các khóa cụ thể vào cột ảo.

Ví dụ, để trích xuất giá trị khóa categories và tạo chỉ mục:

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

Tầm quan trọng của việc xác thực dữ liệu

Mặc dù dữ liệu kiểu JSON linh hoạt, nhưng cũng có nguy cơ lưu trữ định dạng không đúng. Để duy trì tính toàn vẹn dữ liệu, hãy sử dụng các phương pháp sau.

1. Sử dụng ràng buộc CHECK

Từ MySQL 8.0 trở lên, bạn có thể sử dụng ràng buộc CHECK để xác thực cấu trúc hoặc nội dung dữ liệu JSON.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Xác thực ở cấp độ ứng dụng

Khi chèn dữ liệu, bạn nên xác thực định dạng JSON ở cấp độ ứng dụng. Trong các ngôn ngữ lập trình như PHP hoặc Python, bạn có thể sử dụng thư viện chuẩn để xác thực JSON.

7. Các câu hỏi thường gặp về việc sử dụng dữ liệu dạng mảng trong MySQL

Câu hỏi 1: MySQL có cung cấp kiểu mảng không?

A1: MySQL không có kiểu “mảng” trực tiếp. Tuy nhiên, bạn có thể xử lý dữ liệu dạng mảng bằng cách sử dụng kiểu JSON. Bằng cách sử dụng kiểu JSON, bạn có thể lưu nhiều giá trị trong một cột và thao tác chúng thông qua các truy vấn.

Ví dụ:

SELECT JSON_ARRAY('value1', 'value2', 'value3') AS example_array;

Kết quả:

["value1", "value2", "value3"]

Câu hỏi 2: Bạn có thể đặt chỉ mục lên dữ liệu kiểu JSON không?

A2: Bạn không thể đặt chỉ mục trực tiếp lên kiểu JSON. Tuy nhiên, bạn có thể trích xuất các khóa hoặc giá trị cụ thể thành một cột ảo và sau đó đặt chỉ mục trên cột đó.

Ví dụ:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Q3: Có giới hạn kích thước cho dữ liệu JSON không?

A3: Kiểu dữ liệu JSON của MySQL có thể lưu trữ lên tới 4 GB dữ liệu. Tuy nhiên, việc sử dụng dữ liệu JSON rất lớn có thể làm giảm hiệu suất, vì vậy thiết kế dữ liệu đúng cách là rất quan trọng.

Khuyến nghị:

• Lưu chỉ những dữ liệu tối thiểu cần thiết.
• Tránh cấu trúc JSON lồng sâu.

Q4: Làm thế nào để cập nhật một phần tử cụ thể trong mảng JSON?

A4: Bạn cập nhật các phần tử cụ thể bên trong mảng JSON bằng hàm JSON_SET.

Ví dụ:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Kết quả:

["PHP", "Python", "JavaScript"]

Q5: So sánh giữa kiểu JSON và thiết kế bảng truyền thống

A5: Kiểu JSON cung cấp độ linh hoạt cao nhưng cũng khác nhau về bản chất so với thiết kế quan hệ truyền thống.

8. Tóm tắt

Lợi ích của việc xử lý dữ liệu dạng mảng với kiểu JSON trong MySQL

Bài viết này giải thích kiểu JSON để xử lý dữ liệu dạng mảng trong MySQL. Các điểm chính được đề cập ở đây là:

1. Tại sao nên sử dụng kiểu JSON MySQL thiếu kiểu mảng trực tiếp, nhưng bằng cách sử dụng kiểu JSON bạn có thể lưu nhiều giá trị trong một cột và thực hiện các thao tác dữ liệu linh hoạt.
2. Các thao tác JSON cơ bản

• Học cách tạo mảng JSON, trích xuất dữ liệu, cập nhật và xoá giá trị.
• Sử dụng các hàm như JSON_ARRAY, JSON_EXTRACT, và JSON_SET để thao tác dữ liệu dạng mảng một cách hiệu quả.

3. Tìm kiếm và lọc

• Sử dụng JSON_CONTAINS để tìm kiếm các giá trị cụ thể trong mảng JSON.
• Sử dụng JSON_LENGTH để lấy độ dài mảng và thực hiện lọc có điều kiện.

4. Các trường hợp ứng dụng thực tế Chúng ta đã học các kịch bản ứng dụng cụ thể như quản lý danh mục sản phẩm và lọc theo giá.
5. Cẩn thận và tối ưu hóa

• Thảo luận cách lập chỉ mục dữ liệu JSON thông qua cột ảo và tầm quan trọng của việc xác thực dữ liệu JSON.

Các bước tiếp theo để tận dụng kiểu JSON

Bằng cách sử dụng kiểu JSON trong MySQL, bạn có thể triển khai quản lý dữ liệu linh hoạt hơn so với thiết kế cơ sở dữ liệu quan hệ truyền thống. Tuy nhiên, thiết kế đúng và xem xét hiệu suất vẫn là yếu tố quan trọng.

Các chủ đề cần học tiếp:

• Sử dụng chỉ mục kết hợp Thiết kế các chỉ mục kết hợp giữa cột kiểu JSON và cột thường.
• Hàm JSON nâng cao Sử dụng JSON_MERGE, JSON_OBJECT và các hàm khác để thực hiện các thao tác phức tạp hơn.
• Thao tác dữ liệu cấp ứng dụng Thao tác dữ liệu JSON MySQL một cách hiệu quả bằng các ngôn ngữ như PHP hoặc Python.

Tổng kết cuối cùng

Qua bài viết này bạn đã học cách xử lý dữ liệu dạng mảng một cách hiệu quả bằng kiểu JSON trong MySQL. Bằng cách áp dụng kiến thức này, bạn có thể thiết kế các hệ thống cơ sở dữ liệu linh hoạt và mở rộng hơn.