โลกของ MySQL และ MariaDB

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

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

1. บทนำ

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

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

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

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

2. Collation คืออะไร?

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

บทบาทของ Collation

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

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

Collation มีความเกี่ยวข้องอย่างใกล้ชิดกับชุดตัวอักษร ตัวอย่างเช่น ชุดตัวอักษร utf8 มีการตั้งค่า collation ดังนี้:

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

รูปแบบการตั้งชื่อ

character_set_name_comparison_method

ตัวอย่าง:

  • 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 db_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;

ระดับค่าลITERAL สตริง

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

คุณสามารถตรวจสอบหรือแก้ไข 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

ฐานข้อมูล

ALTER DATABASE database_name
DEFAULT CHARACTER SET utf8mb4
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) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

เมื่อเปรียบเทียบคอลัมน์ที่มี collation แตกต่างกัน ให้ใช้คลาส COLLATE เพื่อหลีกเลี่ยงข้อผิดพลาด

SELECT * FROM table_name
WHERE column1 COLLATE utf8mb4_unicode_ci = column2;

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

6. หมายเหตุและแนวปฏิบัติที่ดีที่สุด

เมื่อกำหนดค่า collation ใน MySQL ควรพิจารณาสิ่งระมัดระวังและแนวปฏิบัติดังต่อไปนี้เพื่อให้ได้ประสิทธิภาพสูงสุด

หมายเหตุสำคัญ

การผสมผสานการจัดเรียงตัวอักษรที่ต่างกัน

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

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

การเปลี่ยนการจัดเรียงตัวอักษรและข้อมูลที่มีอยู่

การเปลี่ยนการจัดเรียงตัวอักษรอาจส่งผลต่อวิธีการเปรียบเทียบหรือค้นหาข้อมูลที่มีอยู่แล้ว

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

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

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

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

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

1. เลือกการจัดเรียงตัวอักษรตามความต้องการ

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

2. รักษาความสอดคล้อง

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

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

  • ทดสอบการเปลี่ยนแปลงการกำหนดค่าต่าง ๆ ในสภาพแวดล้อมเตรียมความพร้อม
  • สำรองข้อมูลในระบบผลิตเสมอก่อนนำไปใช้งาน

4. เพิ่มประสิทธิภาพ

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

5. ใช้มาตรฐาน Unicode ล่าสุด

ในระบบหลายภาษา ควรใช้ utf8mb4_0900_ai_ci เพื่อความแม่นยำและความเข้ากันได้ที่ดีกว่า

7. สรุป

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

สิ่งสำคัญที่ต้องจดจำ

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

การเพิ่มประสิทธิภาพการใช้การจัดเรียงตัวอักษร

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