Practical MySQL & MariaDB

อธิบาย Collation ของ MySQL: วิธีตั้งค่า, เปลี่ยนแปลง, และเพิ่มประสิทธิภาพการจัดเรียงอักขระ

การตั้งค่าและการจัดการเซิร์ฟเวอร์
目次

1. บทนำ

MySQL เป็นหนึ่งในระบบจัดการฐานข้อมูลที่ใช้กันอย่างแพร่หลายที่สุด และภายในนั้น “Collation” (การจัดเรียง) เป็นการตั้งค่าที่สำคัญซึ่งมีผลต่อวิธีการเปรียบเทียบและจัดเรียงข้อมูลแบบสตริง

ความสำคัญของ Collation

หากไม่ได้กำหนด Collation ที่เหมาะสม ผลการค้นหาอาจไม่สอดคล้องกับความคาดหวัง และประสิทธิภาพของฐานข้อมูลอาจลดลง โดยเฉพาะในระบบหลายภาษา ความแม่นยำของการเปรียบเทียบอักขระและพฤติกรรมการค้นหาจะได้รับผลกระทบอย่างมีนัยสำคัญ

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

2. Collation คืออะไร?

Collation หมายถึงกฎที่ MySQL ใช้เมื่อทำการเปรียบเทียบและจัดเรียงค่าประเภทสตริง

บทบาทของ Collation

  • การจัดเรียงสตริง: กำหนดลำดับของข้อมูลสตริง
  • การเปรียบเทียบสตริง: กำหนดเกณฑ์การเปรียบเทียบเช่น WHERE name = 'Sagawa'
  • ความแม่นยำของการค้นหา: มีผลต่อการสนับสนุนหลายภาษาและความแม่นยำของการจับคู่

ความสัมพันธ์กับชุดอักขระ

Collation มีความสัมพันธ์อย่างใกล้ชิดกับชุดอักขระ ตัวอย่างเช่น ชุดอักขระ utf8 มี Collation ดังต่อไปนี้

  • utf8_general_ci : การเปรียบเทียบแบบไม่สนใจตัวพิมพ์ใหญ่/เล็ก
  • utf8_bin : การเปรียบเทียบแบบไบนารี

แนวทางการตั้งชื่อ Collation

character_set_comparison_type

ตัวอย่าง:

  • utf8_general_ci : การเปรียบเทียบแบบไม่สนใจตัวพิมพ์ใหญ่/เล็ก (ci: case insensitive).
  • utf8_bin : การเปรียบเทียบแบบไบนารี.

3. ระดับการกำหนดค่า Collation ใน MySQL

ใน MySQL, สามารถกำหนดค่า Collation ได้ที่ระดับต่อไปนี้ทั้งหมดห้าระดับ:

ระดับเซิร์ฟเวอร์

SHOW VARIABLES LIKE 'collation_server';

เพื่อเปลี่ยนการตั้งค่า ให้เพิ่มสิ่งต่อไปนี้ในไฟล์ my.cnf แล้วรีสตาร์ทเซิร์ฟเวอร์.

[mysqld]
collation_server=utf8mb4_unicode_ci

ระดับฐานข้อมูล

ALTER DATABASE database_name DEFAULT COLLATE utf8mb4_unicode_ci;

ระดับตาราง

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ระดับคอลัมน์

ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(255) COLLATE utf8mb4_unicode_ci;

ระดับสตริงลิเทอรัล

SELECT * FROM table_name WHERE column_name = 'value' COLLATE utf8mb4_bin;

4. ประเภท Collation หลักและลักษณะของมัน

utf8_general_ci

  • คุณลักษณะ: การเปรียบเทียบแบบไม่สนใจตัวพิมพ์ใหญ่/เล็กและรวดเร็ว
  • หมายเหตุ: ความแม่นยำต่ำกว่าและไม่สอดคล้องเต็มที่กับมาตรฐาน Unicode

utf8_unicode_ci

  • คุณลักษณะ: การเปรียบเทียบความแม่นยำสูงตามมาตรฐาน Unicode
  • หมายเหตุ: ช้ากว่า utf8_general_ci เล็กน้อย

utf8_bin

  • คุณลักษณะ: แยกแยะตัวพิมพ์ใหญ่/เล็กและต้องการการจับคู่ที่ตรงกันอย่างแม่นยำ
  • กรณีการใช้งาน: รหัสผ่านและการเปรียบเทียบตัวระบุ

utf8mb4_unicode_ci

  • คุณลักษณะ: สอดคล้องกับมาตรฐาน Unicode สมัยใหม่และเหมาะสำหรับระบบหลายภาษา
  • กรณีการใช้งาน: แอปพลิเคชันที่จัดการอีโมจิและสัญลักษณ์พิเศษ

5. วิธีตรวจสอบและเปลี่ยน Collation

ใน MySQL, คุณสามารถตรวจสอบและแก้ไขการตั้งค่า Collation ได้ที่ระดับฐานข้อมูล, ตาราง, และคอลัมน์.

วิธีตรวจสอบ Collation

ตรวจสอบ Collation ของฐานข้อมูล

SELECT SCHEMA_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

ตรวจสอบ Collation ของตาราง

SHOW TABLE STATUS WHERE Name = 'table_name';

ตรวจสอบ Collation ของคอลัมน์

SHOW FULL COLUMNS FROM table_name;

วิธีเปลี่ยน Collation

เปลี่ยน Collation ของฐานข้อมูล

ALTER DATABASE database_name
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

เปลี่ยน Collation ของตาราง

ALTER TABLE table_name
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

เปลี่ยน Collation ของคอลัมน์

ALTER TABLE table_name
MODIFY COLUMN column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

การเปลี่ยน Collation ชั่วคราว

เมื่อเปรียบเทียบคอลัมน์ที่มี Collation แตกต่างกัน คุณสามารถใช้คำสั่ง COLLATE เพื่อป้องกันข้อผิดพลาดได้.

SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;

ควรสำรองข้อมูลของคุณเสมอก่อนทำการเปลี่ยนแปลงและตรวจสอบความเข้ากันได้กับแอปพลิเคชันของคุณ.

6. ข้อควรพิจารณาที่สำคัญและแนวปฏิบัติที่ดีที่สุดสำหรับ Collation

เมื่อกำหนดค่า Collation ใน MySQL จำเป็นต้องเข้าใจประเด็นสำคัญและปฏิบัติตามแนวทางการทำงานที่เหมาะสม ด้านล่างนี้เป็นบันทึกสำคัญและแนวปฏิบัติที่ดีที่สุดสำหรับการจัดการ Collation อย่างมีประสิทธิภาพ.

ข้อควรพิจารณาที่สำคัญ

การทำงานระหว่าง Collation ที่แตกต่างกัน

การเปรียบเทียบหรือการ join คอลัมน์ที่มี Collation แตกต่างกันอาจทำให้เกิดข้อผิดพลาด.

  • ตัวอย่างข้อผิดพลาด:
    ERROR 1267 (HY000): Illegal mix of collations for operation '='
  • วิธีแก้: ใช้คำสั่ง COLLATE เพื่อทำให้ Collation สอดคล้องกัน.
    SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;

การเปลี่ยน Collation และข้อมูลที่มีอยู่แล้ว

การเปลี่ยน Collation อาจส่งผลต่อข้อมูลที่มีอยู่ เนื่องจากพฤติกรรมการเปรียบเทียบและการค้นหาอาจเปลี่ยนแปลง จึงต้องมีการตรวจสอบอย่างละเอียด.

ผลกระทบต่อประสิทธิภาพ

  • Collation ที่มีความแม่นยำสูงเช่น utf8mb4_unicode_ci อาจทำงานช้ากว่า utf8_general_ci .
  • เมื่อจัดการกับชุดข้อมูลขนาดใหญ่ การเลือก Collation สามารถส่งผลอย่างมีนัยสำคัญต่อประสิทธิภาพและต้องเลือกอย่างระมัดระวัง.

ปัญหาในการย้ายข้อมูล

เมื่อเปลี่ยน Collation ควรตรวจสอบความเข้ากันได้กับแอปพลิเคชันและระบบภายนอก.

แนวปฏิบัติที่ดีที่สุด

1. เลือก Collation ตามความต้องการ

พิจารณาตามข้อกำหนดต่อไปนี้เมื่อต้องเลือก Collation:

  • ความแม่นยำเป็นลำดับแรก: ใช้ utf8_unicode_ci หรือ utf8mb4_unicode_ci เมื่อจำเป็นต้องเปรียบเทียบอย่างแม่นยำ.
  • ประสิทธิภาพเป็นลำดับแรก: เลือก utf8_general_ci หากยอมรับความแม่นยำที่ต่ำกว่าเล็กน้อยได้.
  • รองรับหลายภาษา: ใช้ Collation ที่อิง utf8mb4 เมื่อจัดการกับอีโมจิและอักขระพิเศษ.

2. ทำให้ Collation เป็นมาตรฐานเดียวกัน

การใช้ Collation เดียวกันทั่วทั้งฐานข้อมูลช่วยป้องกันข้อผิดพลาดและความไม่สอดคล้องกัน.

  • กำหนด Collation เดียวกันที่ระดับฐานข้อมูล, ตาราง, และคอลัมน์.
  • ใช้คำสั่ง COLLATE สำหรับการปรับเปลี่ยนชั่วคราวเมื่อจำเป็น.

3. ทดสอบและสำรองข้อมูลก่อนทำการเปลี่ยนแปลง

ก่อนทำการเปลี่ยนแปลง:

  • ตรวจสอบพฤติกรรมในสภาพแวดล้อมการทดสอบ.
  • สร้างการสำรองข้อมูลเต็มของข้อมูลการผลิต.

4. ปรับประสิทธิภาพการทำงาน

เพื่อลดผลกระทบของ Collation ต่อประสิทธิภาพการสืบค้น:

  • ใช้ Collation ที่มีประสิทธิภาพสูงสำหรับคอลัมน์ที่ถูกสืบค้นบ่อย (เช่น คอลัมน์ที่มีดัชนี).
  • ปรับ Collation ชั่วคราวในคำสืบค้นเฉพาะเมื่อจำเป็น.

5. ใช้ Unicode เวอร์ชันล่าสุด

สำหรับระบบหลายภาษา การใช้ utf8mb4_0900_ai_ci ที่อิงตามมาตรฐาน Unicode ล่าสุดช่วยเพิ่มความแม่นยำและความเข้ากันได้.

7. สรุป

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

ประเด็นสำคัญที่ควรจำ

  1. Collation คืออะไร? Collation กำหนดวิธีการเปรียบเทียบและเรียงลำดับสตริง การเลือก Collation ที่เหมาะสมช่วยปรับปรุงความแม่นยำและประสิทธิภาพของฐานข้อมูล.
  2. ระดับการกำหนดค่าที่ยืดหยุ่น MySQL อนุญาตให้กำหนด Collation ได้หลายระดับ: เซิร์ฟเวอร์, ฐานข้อมูล, ตาราง, คอลัมน์, และลิเทรัล ความยืดหยุ่นนี้ทำให้สามารถกำหนดค่าที่เหมาะสมที่สุดในแต่ละชั้นได้.
  3. ลักษณะสำคัญของ Collation หลัก
  • utf8_general_ci : เร็วแต่ความแม่นยำต่ำกว่า.
  • utf8_unicode_ci : แม่นยำมากขึ้นแต่ช้ากว่าเล็กน้อย.
  • utf8mb4_unicode_ci : รองรับ Unicode อย่างเต็มที่และสนับสนุนอีโมจิและอักขระพิเศษ.
  1. การตรวจสอบเชิงปฏิบัติและการปรับเปลี่ยน บทความนี้แนะนำคำสั่ง SQL เพื่อเช็คและเปลี่ยน collation พร้อมคำแนะนำการนำไปใช้เชิงปฏิบัติ
  2. ข้อควรพิจารณาและแนวปฏิบัติที่ดีที่สุด เข้าใจข้อควรระวังสำคัญเมื่อเปลี่ยน collation และควรทำการทดสอบและสำรองข้อมูลล่วงหน้า

การใช้ Collation อย่างมีประสิทธิภาพ

เนื่องจาก collation มีผลอย่างมากต่อการเปรียบเทียบและการจัดเรียงสตริง การกำหนดค่าอย่างเหมาะสมจึงเป็นสิ่งสำคัญ ใช้วิธีการและแนวปฏิบัติที่ดีที่สุดที่แนะนำในบทความนี้เพื่อเลือกการกำหนดค่าที่เหมาะสมที่สุดสำหรับระบบของคุณ