แปลงฐานข้อมูลเป็น utf8mb4 ให้รองรับภาษาไทยและอีโมจิ

ใน MySQL และ MariaDB ชุดอักขระที่ชื่อ utf8 แบบเดิม (ปัจจุบันเรียกว่า utf8mb3) เก็บอักขระได้สูงสุด 3 ไบต์ต่อตัว ภาษาไทยใช้ 3 ไบต์จึงเก็บได้ แต่อีโมจิและอักขระพิเศษบางกลุ่มใช้ 4 ไบต์ เมื่อผู้ใช้พิมพ์อีโมจิลงในฟอร์ม ข้อมูลจะถูกตัดทิ้งหรือเกิด Error Incorrect string value ส่วนฐานข้อมูลเก่าที่ยังเป็น latin1 ก็มักแสดงภาษาไทยเพี้ยนเมื่อย้ายเซิร์ฟเวอร์

ชุดอักขระที่ควรใช้ในปัจจุบันคือ utf8mb4 ซึ่งเป็น UTF-8 ครบทุกอักขระ บทความนี้อธิบายวิธีตรวจสอบสถานะปัจจุบัน แปลงฐานข้อมูล ตาราง และคอลัมน์เป็น utf8mb4 และตั้งค่าเซิร์ฟเวอร์ให้ฐานข้อมูลใหม่ใช้ utf8mb4 โดยอัตโนมัติ

สิ่งที่ต้องเตรียม

• MySQL 5.7 ขึ้นไป (แนะนำ MySQL 8) หรือ MariaDB 10.3 ขึ้นไป
• ผู้ใช้ฐานข้อมูลที่มีสิทธิ์ ALTER ในฐานข้อมูลเป้าหมาย และสิทธิ์ root หรือ sudo หากต้องการแก้ไฟล์ตั้งค่าเซิร์ฟเวอร์
• ไฟล์สำรองฐานข้อมูลล่าสุดที่ทดสอบกู้คืนแล้ว ตาม สำรองและกู้คืนฐานข้อมูล MySQL/MariaDB ด้วย mysqldump
• ช่วงเวลาที่มีผู้ใช้งานน้อย เพราะการแปลงตารางขนาดใหญ่จะสร้างตารางใหม่ทั้งตารางและอาจล็อกการเขียนระหว่างนั้น

หมายเหตุ: การแปลง Character Set เป็นการแก้ไขข้อมูลจริงทุกแถว ถ้าข้อมูลเดิมถูกเก็บผิดรูปแบบ (เช่น ภาษาไทยที่ถูกเก็บในคอลัมน์ latin1) การแปลงแบบปกติจะทำให้ข้อมูลเพี้ยนถาวร ให้สำรองก่อน และทดลองกับสำเนาฐานข้อมูลก่อนทำกับของจริงเสมอ

ขั้นตอนที่ 1: ตรวจสอบ Character Set ปัจจุบัน

เข้า mysql client แล้วดูค่าระดับฐานข้อมูล (แทน shopdb ด้วยชื่อฐานข้อมูลของคุณ)

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'shopdb';

ดูระดับตาราง

SELECT TABLE_NAME, ENGINE, ROW_FORMAT, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_TYPE = 'BASE TABLE';

และดูคอลัมน์ข้อความที่ยังไม่ใช่ utf8mb4

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'shopdb'
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME <> 'utf8mb4';

ถ้าคำสั่งสุดท้ายไม่คืนแถวใดเลย แปลว่าทุกคอลัมน์เป็น utf8mb4 แล้ว ไม่ต้องแปลงข้อมูล อาจเหลือเพียงการตั้งค่าเซิร์ฟเวอร์และแอปพลิเคชันในขั้นตอนที่ 5-6

ขั้นตอนที่ 2: เลือก Collation

Collation กำหนดวิธีเปรียบเทียบและเรียงลำดับข้อความ ควรเลือกให้เหมือนกันทั้งฐานข้อมูล เพื่อไม่ให้เกิด Error Illegal mix of collations เวลา JOIN ตาราง

ตัวอย่างในบทความนี้ใช้ utf8mb4_unicode_ci ซึ่งใช้ได้ทั้งสองระบบ

ขั้นตอนที่ 3: เปลี่ยนค่าเริ่มต้นของฐานข้อมูล

ALTER DATABASE shopdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

คำสั่งนี้เปลี่ยนเฉพาะค่าเริ่มต้นสำหรับตารางที่จะสร้างใหม่ ตารางเดิมยังไม่เปลี่ยน ต้องทำขั้นตอนถัดไปต่อ

ขั้นตอนที่ 4: แปลงทุกตาราง

คำสั่ง ALTER TABLE ... CONVERT TO CHARACTER SET จะแปลงทั้งค่าเริ่มต้นของตารางและทุกคอลัมน์ข้อความ (CHAR, VARCHAR, TEXT) พร้อมแปลงข้อมูลในคอลัมน์ให้ด้วย ตัวอย่างสำหรับตารางเดียว

ALTER TABLE customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ถ้ามีหลายตาราง ให้ใช้ SQL สร้างชุดคำสั่งให้อัตโนมัติ แล้วบันทึกเป็นไฟล์เพื่อตรวจก่อนรัน

mysql -u root -p -N -B -e "SELECT CONCAT('ALTER TABLE \`', TABLE_NAME, '\` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shopdb' AND TABLE_TYPE = 'BASE TABLE';" > convert-shopdb.sql

cat convert-shopdb.sql

เมื่อตรวจแล้วว่ารายชื่อตารางถูกต้อง ให้รันไฟล์ที่ได้ (ควรรันใน tmux ถ้าตารางใหญ่)

mysql -u root -p shopdb < convert-shopdb.sql

ถ้าตารางมี Foreign Key เชื่อมกันและเกิด Error เรื่อง Foreign Key ระหว่างแปลง ให้เพิ่มบรรทัด SET foreign_key_checks = 0; ไว้บนสุดของไฟล์ และ SET foreign_key_checks = 1; ไว้ท้ายไฟล์ แล้วรันใหม่

ขั้นตอนที่ 5: ตั้งค่าเซิร์ฟเวอร์ให้ใช้ utf8mb4 เป็นค่าเริ่มต้น

MySQL 8 ใช้ utf8mb4 เป็นค่าเริ่มต้น และแพ็กเกจ MariaDB บน Ubuntu ก็ตั้งค่านี้ไว้ให้แล้ว แต่ MariaDB บางรุ่นหรือบางการติดตั้ง (รวมถึงเซิร์ฟเวอร์ที่ย้ายค่าตั้งเดิมมาจากเครื่องเก่า) ยังเป็น latin1 ให้ตรวจก่อน

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

ถ้ายังไม่ใช่ utf8mb4 ให้เพิ่มค่าต่อไปนี้ในส่วน [mysqld] ของไฟล์ตั้งค่า (Ubuntu กับ MySQL คือ /etc/mysql/mysql.conf.d/mysqld.cnf, Ubuntu กับ MariaDB คือ /etc/mysql/mariadb.conf.d/50-server.cnf, AlmaLinux/Rocky ใช้ไฟล์ใน /etc/my.cnf.d/)

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

จากนั้นรีสตาร์ต (ชื่อ Service อาจเป็น mysql, mysqld หรือ mariadb แล้วแต่ระบบ)

sudo systemctl restart mariadb

ขั้นตอนที่ 6: ตั้งค่าแอปพลิเคชันให้เชื่อมต่อด้วย utf8mb4

ต่อให้ฐานข้อมูลเป็น utf8mb4 แล้ว ถ้าแอปพลิเคชันยังเชื่อมต่อด้วย utf8 อีโมจิจะยังบันทึกไม่ได้ ตัวอย่างการตั้งค่า

• PHP PDO: ใส่ charset=utf8mb4 ใน DSN เช่น mysql:host=localhost;dbname=shopdb;charset=utf8mb4
• PHP mysqli: เรียก $mysqli->set_charset('utf8mb4'); หลังเชื่อมต่อ
• WordPress: ใน wp-config.php ตั้ง define( 'DB_CHARSET', 'utf8mb4' ); และปล่อย DB_COLLATE เป็นค่าว่าง
• Laravel: ใน config/database.php ค่า charset เป็น utf8mb4 และ collation เป็นค่าเดียวกับที่เลือกในขั้นตอนที่ 2

ตรวจสอบผลลัพธ์

รันคำสั่งตรวจคอลัมน์ในขั้นตอนที่ 1 อีกครั้ง ต้องไม่มีแถวใดคืนมา จากนั้นทดสอบบันทึกอักขระ 4 ไบต์ในตารางทดสอบ ตัวอย่างนี้ใส่อีโมจิหน้ายิ้มผ่านรหัสฐานสิบหก จึงไม่ต้องพิมพ์อีโมจิใน Terminal

CREATE TABLE charset_test (t VARCHAR(20)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
INSERT INTO charset_test VALUES (CONCAT('ทดสอบ ', CONVERT(0xF09F9880 USING utf8mb4)));
SELECT t, HEX(t) FROM charset_test;
DROP TABLE charset_test;

ถ้า INSERT สำเร็จและค่า HEX ลงท้ายด้วย F09F9880 แปลว่าบันทึกอักขระ 4 ไบต์ได้แล้ว สุดท้ายให้ลองพิมพ์ภาษาไทยและอีโมจิผ่านฟอร์มจริงของเว็บไซต์

ปัญหาที่พบบ่อย

ERROR 1071: Specified key was too long; max key length is 767 bytes (หรือ 1000 bytes)

utf8mb4 จองพื้นที่ Index 4 ไบต์ต่ออักขระ คอลัมน์ VARCHAR(255) จึงต้องใช้ 1,020 ไบต์ ตาราง InnoDB ที่ใช้ ROW_FORMAT=DYNAMIC (ค่าเริ่มต้นตั้งแต่ MySQL 5.7 และ MariaDB 10.2) รองรับ Index ได้ถึง 3,072 ไบต์จึงไม่มีปัญหา แต่ตารางเก่าที่เป็น COMPACT หรือ REDUNDANT จำกัดที่ 767 ไบต์ ให้เปลี่ยนรูปแบบแถวก่อนแปลง

ALTER TABLE customers ROW_FORMAT=DYNAMIC;

ถ้าข้อความระบุ 1000 bytes แปลว่าเป็นตาราง MyISAM ให้เปลี่ยนเป็น InnoDB ด้วย ALTER TABLE customers ENGINE=InnoDB; หรือลดความยาวคอลัมน์ที่ทำ Index ลง เช่น เหลือ VARCHAR(191)

หลังแปลงแล้วภาษาไทยกลายเป็นตัวอักษรแปลก เช่น สวัส

อาการนี้เกิดเมื่อข้อมูลเดิมเป็นไบต์ UTF-8 ที่ถูกเก็บในคอลัมน์ latin1 (แอปพลิเคชันเก่าเชื่อมต่อด้วย latin1 แต่ส่งข้อความ UTF-8) การ CONVERT ปกติจะแปลงซ้ำอีกชั้นจึงเพี้ยน วิธีที่ถูกคือกู้คืนจากไฟล์สำรอง แล้วแปลงคอลัมน์นั้นผ่าน BLOB เพื่อให้ MySQL ไม่แปลงไบต์ แต่เปลี่ยนเพียงการตีความ

ALTER TABLE customers MODIFY name BLOB;
ALTER TABLE customers MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ต้องใส่ชนิดข้อมูล ความยาว ค่า NOT NULL และ DEFAULT ให้ตรงกับคอลัมน์เดิมเสมอ (ใช้ VARBINARY แทน BLOB ได้ถ้าคอลัมน์มี Index) และให้ทดลองกับสำเนาฐานข้อมูลก่อน

Illegal mix of collations

เกิดเมื่อ JOIN หรือเปรียบเทียบคอลัมน์ที่ใช้ Collation ต่างกัน เช่น ตารางหนึ่งเป็น utf8mb4_0900_ai_ci อีกตารางเป็น utf8mb4_unicode_ci ให้ใช้คำสั่งตรวจในขั้นตอนที่ 1 หาตารางที่ต่างจากส่วนใหญ่ แล้วแปลงให้เหมือนกันทั้งฐานข้อมูล

ถ้านำเข้าไฟล์ Dump ที่มี Collation ซึ่งเซิร์ฟเวอร์ไม่รู้จัก ดูวิธีแก้ใน นำเข้าไฟล์ SQL ขนาดใหญ่ผ่าน Command Line หากยังแก้ไม่ได้หรือกังวลเรื่องข้อมูลเสียหาย ติดต่อทีมงาน THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/