จัดการข้อมูลที่มีลักษณะเป็นอาเรย์ใน MySQL อย่างมีประสิทธิภาพโดยใช้ชนิดข้อมูล JSON

1. บทนำ

ความจำเป็นในการจัดการข้อมูลแบบอาเรย์ใน MySQL

ในฐานข้อมูล ข้อมูลมักถูกจัดเก็บภายใต้รูปแบบเชิงสัมพันธ์ อย่างไรก็ตาม ขึ้นอยู่กับความต้องการของแอปพลิเคชัน อาจมีประโยชน์ในการจัดเก็บค่าหลายค่าในคอลัมน์เดียว ในกรณีเช่นนี้โครงสร้างข้อมูลแบบ “อาเรย์” สามารถช่วยได้

ตัวอย่างเช่น พิจารณากรณีต่อไปนี้:

• จัดเก็บแท็กหลายรายการที่ผู้ใช้เลือก
• บันทึก URL รูปภาพหลายรายการสำหรับผลิตภัณฑ์
• รวมประวัติหรือบันทึกลงในฟิลด์เดียว

ข้อได้เปรียบของการใช้ประเภท JSON

MySQL ไม่ได้มีประเภท “อาเรย์” ตรง ๆ แต่ด้วยการใช้ประเภท JSON คุณสามารถจัดการข้อมูลแบบอาเรย์ได้ ประเภท JSON มีความยืดหยุ่นสูงและมีข้อได้เปรียบต่อไปนี้:

• รองรับโครงสร้างข้อมูลแบบซ้อนกัน
• อนุญาตให้ทำการจัดการข้อมูลโดยตรงภายในคำสั่งคิวรี
• จัดการรูปแบบข้อมูลหลายแบบในฟิลด์เดียว

ในบทความนี้ เราจะอธิบายวิธีจัดการข้อมูลแบบอาเรย์ใน MySQL อย่างมีประสิทธิภาพโดยใช้ประเภท JSON

2. พื้นฐานการจัดการอาเรย์ด้วยประเภท JSON ของ MySQL

JSON คืออะไร?

JSON (JavaScript Object Notation) เป็นรูปแบบการแลกเปลี่ยนข้อมูลที่เบาและเรียบง่าย MySQL สนับสนุนประเภท JSON เริ่มตั้งแต่รุ่น 5.7 ขึ้นไป ช่วยให้คุณสามารถจัดเก็บและจัดการข้อมูลในรูปแบบ JSON ได้โดยตรงในฐานข้อมูล

ตัวอย่าง: ข้อมูลต่อไปนี้สามารถจัดเก็บในฟิลด์ประเภท JSON ได้:

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

ข้อดีและกรณีใช้งานของประเภท JSON

ข้อได้เปรียบหลักของการใช้ประเภท JSON มีดังนี้:

1. โครงสร้างข้อมูลยืดหยุ่น : คุณสามารถจัดการข้อมูลความยาวเปลี่ยนแปลงได้โดยไม่ต้องแก้ไขสคีมาที่เชิงสัมพันธ์
2. การจัดการข้อมูลอย่างมีประสิทธิภาพ : ใช้งานฟังก์ชันในตัวของ MySQL (เช่น JSON_EXTRACT, JSON_ARRAY) เพื่อจัดการข้อมูลอย่างง่ายดาย
3. การออกแบบแบบไม่มีสคีมา : คุณไม่จำเป็นต้องแก้ไขสคีมาบ่อย ๆ เมื่อสเปคแอปพลิเคชันเปลี่ยนแปลง

กรณีใช้งาน:

• กำหนดหมวดหมู่หลายรายการให้กับข้อมูลผลิตภัณฑ์
• จัดเก็บการตั้งค่าผู้ใช้แบบกำหนดเอง
• ใช้ในเว็บแอปพลิเคชันที่ประมวลผลข้อมูล JSON ที่ซ้อนกัน

3. การดำเนินการพื้นฐานกับอาเรย์ JSON

สร้างอาเรย์ JSON

ใน MySQL คุณสามารถใช้ฟังก์ชัน JSON_ARRAY เพื่อสร้างอาเรย์แบบ JSON ได้อย่างง่ายดาย อาเรย์มีประโยชน์เมื่อคุณต้องการจัดเก็บค่าหลายรายการในคอลัมน์เดียว

ตัวอย่างการใช้งาน

ในคิวรีต่อไปนี้ เราสร้างอาเรย์ JSON ชื่อ tags

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

ผลลัพธ์:

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

ตัวอย่างที่นำไปใช้จริง

ตัวอย่างนี้แสดงการจัดเก็บอาเรย์ JSON ลงในฐานข้อมูลผ่านคำสั่ง INSERT

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

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

ดึงข้อมูลจากอาเรย์ JSON

เพื่อดึงข้อมูลที่จัดเก็บในอาเรย์ JSON คุณใช้ฟังก์ชัน JSON_EXTRACT ซึ่งอนุญาตให้คุณดึงส่วนของอาเรย์ที่ต้องการได้

ตัวอย่างการใช้งาน

ตัวอย่างต่อไปนี้ เราดึงอิลิเมนต์ที่สอง (ดัชนีเริ่มต้น 0) ของอาเรย์

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

ผลลัพธ์:

"MySQL"

ดึงอิลิเมนต์หลายรายการพร้อมกัน

คุณสามารถดึงอิลิเมนต์หลายรายการได้พร้อมกัน

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

เพิ่ม, แก้ไข, และลบข้อมูล

การเพิ่มข้อมูลลงในอาเรย์

ด้วยฟังก์ชัน JSON_ARRAY_APPEND คุณสามารถเพิ่มข้อมูลใหม่เข้าไปในอาเรย์ที่มีอยู่ได้

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

ผลลัพธ์:

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

การแก้ไขข้อมูลภายในอาเรย์

คุณสามารถอัปเดตอิลิเมนต์เฉพาะภายในอาเรย์ได้โดยใช้ JSON_SET

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

ผลลัพธ์:

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

การลบข้อมูลออกจากอาเรย์

การลบข้อมูลออกจากอาเรย์

Use JSON_REMOVE เพื่อลบองค์ประกอบเฉพาะภายในอาร์เรย์

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

ผลลัพธ์:

["PHP", "JavaScript"]

4. ค้นหาและกรองอาร์เรย์ JSON

ค้นหาอาร์เรย์ที่มีข้อมูลเฉพาะ

เพื่อกำหนดว่าอาร์เรย์ JSON มีค่าเฉพาะหรือไม่ คุณใช้ฟังก์ชัน JSON_CONTAINS ฟังก์ชันนี้จะคืนค่าว่าอาร์เรย์ JSON ที่ระบุมีค่าที่กำหนดอยู่หรือไม่

วิธีใช้งานตัวอย่าง

ในตัวอย่างต่อไปนี้เราตรวจสอบว่าอาร์เรย์ JSON มีค่า “MySQL” หรือไม่

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

ผลลัพธ์:

1  (if present)
0  (if not present)

ตัวอย่างที่ใช้: ค้นหาแบบมีเงื่อนไข

หากคุณต้องการค้นิวรรกลในฐานข้อมูลที่มีอาร์เรย์ JSON ที่มีค่าที่ระบุอยู่ คุณสามารถใช้ JSON_CONTAINS ในส่วน WHERE ได้

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

คำสั่งนี้จะดึงข้อมูลจากแถวที่คอลัมน์ tags มีค่า “MySQL”

หาความยาวของอาร์เรย์

เพื่อหาจำนวนสมาชิกในอาร์เรย์ JSON ใช้ฟังก์ชัน JSON_LENGTH ฟังก์ชันนี้มีประโยชน์สำหรับการวิเคราะห์ข้อมูลหรือกรองแบบมีเงื่อนไข

วิธีใช้งานตัวอย่าง

ในตัวอย่างต่อไปนี้เราจะหาจำนวนสมาชิกในอาร์เรย์

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

ผลลัพธ์:

3

ตัวอย่างปฏิบัติ: ดึงแถวที่ตรงตามเงื่อนไข

เพื่อดึงแถวที่จำนวนสมาชิกเท่ากับหรือมากกว่าเกณฑ์ที่กำหนด ใช้ JSON_LENGTH ในส่วน WHERE

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

คำสั่งนี้จะเลือกแถวที่คอลัมน์ tags มีสมาชิกสองรายการขึ้นไป

ตัวอย่างปฏิบัติพร้อมกับการค้นหาที่มีเงื่อนไข

คุณสามารถรวมเงื่อนไขหลายรายการเพื่อค้นหาที่ซับซ้อนได้มากขึ้น คำสั่งต่อไปนี้จะค้นิวรรกลที่อาร์เรย์ tags มีค่า “JavaScript” และมีสมาชิกสามรายการขึ้นไป

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

5. กรณีการใช้งานจริง: เรียนรู้ผ่านสถานการณ์จริงสำหรับอาร์เรย์ JSON

วิธีเก็บหมวดหมู่ผลิตภัณฑ์เป็นอาร์เรย์ JSON

ในเว็บไซต์อีคอมเมิร์ซ สินค้าอาจมีหลายหมวดหมู่ ในกรณีเหล่านี้การใช้อาร์เรย์ JSON เพื่อเก็บข้อมูลหมวดหมู่สามารถมีประสิทธิภาพ

ตัวอย่าง: เก็บข้อมูลหมวดหมู่สำหรับผลิตภัณฑ์

ด้านล่างนี้เป็นตัวอย่างการสร้างตารางที่มีคอลัมน์ JSON ชื่อ categories และเก็บหมวดหมู่หลายรายการ

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'));

โครงสร้างข้อมูลนี้ช่วยให้การเก็บข้อมูลกระชับแม้สินค้าอยู่ในหมวดหมู่หลายรายการ

ค้นหาสินค้าที่อยู่ในหมวดหมู่เฉพาะ

โดยใช้ประเภท JSON คุณสามารถค้นหาสินค้าที่อยู่ในหมวดหมู่เฉพาะได้อย่างง่ายดาย

ตัวอย่างคำสั่งค้นหา

คำสั่งต่อไปนี้จะค้นหาสินค้าทั้งหมดในหมวด “อิเล็กทรอนิกส์”

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

ผลลัพธ์:

Laptop
Smartphone

คำสั่งนี้ช่วยให้คุณดึงรายการสินค้าตามหมวดหมู่ได้อย่างยืดหยุ่น

ตัวอย่าง: กรองตามช่วงราคา

มาดูวิธีเก็บข้อมูลราคาในรูปแบบ JSON และค้นหาสินค้าตามช่วงราคา

ตัวอย่างข้อมูล

ด้านล่างเราจะเก็บข้อมูลราคาสินค้าแต่ละรายการโดยใช้ประเภท 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"]}');

ตัวอย่างคำสั่งค้นหา

หากต้องการค้นหาสินค้าที่มีราคา 100,000 หรือมากกว่า คุณใช้ JSON_EXTRACT

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

ผลลัพธ์:

Laptop

การใช้ JSON_TABLE เพื่อขยายและตัวอย่างการค้นหา

เมื่อคุณต้องการค้นหาข้อมูล JSON ในรูปแบบเชิงสัมพันธ์ คุณสามารถใช้ JSON_TABLE ได้ ฟังก์ชันนี้ช่วยให้คุณขยายอาเรย์ JSON เป็นตารางเสมือน

ตัวอย่างการใช้งาน

ด้านล่างเป็นตัวอย่างที่อาเรย์ JSON ถูกขยายและแต่ละประเภทแสดงเป็นแถวแยกต่างหาก

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

ผลลัพธ์:

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

6. คำแนะนำเมื่อใช้ประเภท JSON

จุดสำคัญในการเพิ่มประสิทธิภาพ

แม้ประเภท JSON จะยืดหยุ่นสูง แต่หากออกแบบไม่ถูกต้องอาจส่งผลต่อประสิทธิภาพของฐานข้อมูลได้ ด้านล่างคือจุดสำคัญในการเพิ่มประสิทธิภาพ

1. การใช้ดัชนี

ใน MySQL คุณไม่สามารถตั้งดัชนีโดยตรงบนคอลัมน์ JSON ได้ แต่คุณสามารถสร้างคอลัมน์เสมือนและตั้งดัชนีบนคีย์เฉพาะ

ตัวอย่าง: สร้างดัชนีผ่านคอลัมน์เสมือน

ในตัวอย่างนี้เราตั้งดัชนีบนคีย์ price ภายในข้อมูล JSON

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

โดยใช้คอลัมน์เสมือน คุณสามารถปรับปรุงประสิทธิภาพการค้นหาในข้อมูลประเภท JSON ได้อย่างมาก

2. หลีกเลี่ยงโครงสร้าง JSON ที่ซับซ้อนเกินไป

โครงสร้าง JSON ที่ซ้อนกันลึกจะส่งผลต่อความเข้าใจของการค้นหาและประสิทธิภาพ เมื่อออกแบบข้อมูล ควรใช้โครงสร้าง JSON ที่เรียบง่ายที่สุดเท่าที่เป็นไปได้

ตัวอย่างที่ดี:

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

โครงสร้างที่ควรหลีกเลี่ยง:

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

วิธีใช้ดัชนีอย่างมีประสิทธิภาพ

เมื่อใช้คอลัมน์เสมือนสำหรับสร้างดัชนี ต้องพิจารณาจุดต่อไปนี้

1. คอลัมน์เสมือนต้องเป็น STORED
2. ใช้ JSON_EXTRACT เพื่อดึงคีย์เฉพาะลงในคอลัมน์เสมือน

ตัวอย่างเช่น เพื่อดึงค่าคีย์ categories แล้วตั้งดัชนี:

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

ความสำคัญของการตรวจสอบข้อมูล

แม้ว่าข้อมูลประเภท JSON จะยืดหยุ่น แต่ก็เสี่ยงต่อการเก็บรูปแบบที่ไม่ถูกต้อง เพื่อรักษาความสมบูรณ์ของข้อมูล ใช้วิธีการดังต่อไปนี้

1. ใช้ข้อจำกัด CHECK

ตั้งแต่ MySQL 8.0 ขึ้นไป คุณสามารถใช้ข้อจำกัด CHECK เพื่อตรวจสอบโครงสร้างหรือเนื้อหาข้อมูล JSON

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

2. การตรวจสอบที่ระดับแอปพลิเคชัน

เมื่อใส่ข้อมูล คุณควรตรวจสอบรูปแบบ JSON ที่ระดับแอปพลิเคชัน ในภาษาโปรแกรมเช่น PHP หรือ Python คุณสามารถใช้ไลบรารีมาตรฐานเพื่อตรวจสอบ JSON ได้

7. คำถามที่พบบ่อยเกี่ยวกับการใช้ข้อมูลแบบอาเรย์ใน MySQL

คำถาม 1: MySQL มีประเภท “อาเรย์” โดยตรงหรือไม่?

คำตอบ 1: MySQL ไม่มีประเภท “อาเรย์” โดยตรง อย่างไรก็ตาม คุณสามารถจัดการข้อมูลแบบอาเรย์ได้โดยใช้ประเภท JSON โดยการใช้ประเภท JSON คุณสามารถเก็บหลายค่าลงในคอลัมน์เดียวและจัดการผ่านการค้นหา

ตัวอย่าง:

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

ผลลัพธ์:

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

คำถาม 2: คุณสามารถตั้งดัชนีบนข้อมูลประเภท JSON ได้หรือไม่?

คำตอบ 2: คุณไม่สามารถตั้งดัชนีโดยตรงบนประเภท JSON ได้ อย่างไรก็ตาม คุณสามารถดึงคีย์หรือค่าเฉพาะเป็นคอลัมน์เสมือนแล้วตั้งดัชนีบนคอลัมน์นั้นได้

ตัวอย่าง:

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

นี่ช่วยให้ค้นหาค่าภายในข้อมูล JSON ได้อย่างมีประสิทธิภาพ

Q3: มีขีดจำกัดขนาดข้อมูล JSON ไหม?

A3: ชนิด JSON ของ MySQL สามารถเก็บข้อมูลได้สูงสุด 4 GB อย่างไรก็ตาม การใช้ข้อมูล JSON ขนาดใหญ่อาจทำให้ประสิทธิภาพลดลง ดังนั้นการออกแบบข้อมูลที่เหมาะสมเป็นสิ่งสำคัญ.

คำแนะนำ:

• เก็บเฉพาะข้อมูลที่จำเป็นอย่างน้อยที่สุด
• หลีกเลี่ยงโครงสร้าง JSON ซ้อนกันลึก

Q4: วิธีอัปเดตองค์ประกอบเฉพาะในอาร์เรย์ JSON อย่างไร?

A4: คุณอัปเดตองค์ประกอบเฉพาะภายในอาร์เรย์ JSON ด้วยฟังก์ชัน JSON_SET.

ตัวอย่าง:

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

ผลลัพธ์:

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

Q5: การเปรียบเทียบระหว่างชนิด JSON กับการออกแบบตารางแบบดั้งเดิม

A5: ชนิด JSON ให้ความยืดหยุ่นสูง แต่ก็แตกต่างจากการออกแบบฐานข้อมูลแบบสัมพันธ์แบบดั้งเดิม.

8. สรุป

ประโยชน์ของการจัดการข้อมูลแบบอาร์เรย์ด้วยชนิด JSON ใน MySQL

บทความนี้อธิบายชนิด JSON สำหรับการจัดการข้อมูลแบบอาร์เรย์ใน MySQL จุดสำคัญที่ครอบคลุมมีดังนี้:

1. ทำไมต้องใช้ชนิด JSON MySQL ไม่มีชนิดอาร์เรย์โดยตรง แต่ด้วยการใช้ชนิด JSON คุณสามารถเก็บค่าหลายค่าในคอลัมน์เดียวและทำการดำเนินการข้อมูลที่ยืดหยุ่นได้
2. การดำเนินการ JSON พื้นฐาน

• เรียนรู้วิธีสร้างอาร์เรย์ JSON, ดึงข้อมูล, อัปเดตและลบค่าต่าง ๆ
• ใช้ฟังก์ชัน เช่น JSON_ARRAY, JSON_EXTRACT และ JSON_SET เพื่อจัดการข้อมูลแบบอาร์เรย์อย่างมีประสิทธิภาพ

1. การค้นหาและกรอง

• ใช้ JSON_CONTAINS เพื่อค้นหาค่าที่เฉพาะเจาะจงภายในอาร์เรย์ JSON
• ใช้ JSON_LENGTH เพื่อรับความยาวของอาร์เรย์และทำการกรองตามเงื่อนไข

1. ตัวอย่างการใช้งานจริง เราได้เรียนรู้สถานการณ์การใช้งานจริงเช่นการจัดการหมวดหมู่สินค้าและการกรองตามราคา
2. ข้อควรระวังและการเพิ่มประสิทธิภาพ

• พูดถึงวิธีทำดัชนีข้อมูล JSON ผ่านคอลัมน์เสมือนและความสำคัญของการตรวจสอบความถูกต้องของข้อมูล JSON

ขั้นตอนต่อไปสำหรับการใช้ประโยชน์จากชนิด JSON

โดยใช้ชนิด JSON ใน MySQL คุณสามารถเปิดโอกาสให้การจัดการข้อมูลมีความยืดหยุ่นมากขึ้นกว่าการออกแบบฐานข้อมูลแบบสัมพันธ์ดั้งเดิม อย่างไรก็ตาม การออกแบบที่เหมาะสมและการพิจารณาประสิทธิภาพเป็นสิ่งสำคัญ

หัวข้อที่จะเรียนรู้ต่อไป:

• ใช้ดัชนีแบบรวม การออกแบบดัชนีโดยรวมคอลัมน์ชนิด JSON กับคอลัมน์ปกติ
• ฟังก์ชัน JSON ขั้นสูง การใช้ JSON_MERGE, JSON_OBJECT และฟังก์ชันอื่น ๆ เพื่อทำงานที่ซับซ้อนมากขึ้น
• การจัดการข้อมูลระดับแอปพลิเคชัน การจัดการข้อมูล JSON ของ MySQL อย่างมีประสิทธิภาพโดยใช้ภาษาเช่น PHP หรือ Python

สรุปสุดท้าย

ผ่านบทความนี้คุณได้เรียนรู้วิธีจัดการข้อมูลแบบอาร์เรย์อย่างมีประสิทธิภาพโดยใช้ชนิด JSON ใน MySQL โดยนำความรู้นี้มาปรับใช้คุณจะสามารถออกแบบระบบฐานข้อมูลที่ยืดหยุ่นและมีขนาดใหญ่ขึ้นได้