คำสั่ง SQL สำหรับการ ALTER TABLE เพื่อเปลี่ยน CHARACTER SET และ COLLATE ในฐานข้อมูลทุกตารางเป็น utf8mb4 และ utf8mb4_general_ci

ปัญหาเกิดจากตอนสร้าง database เลือกเป็น utf8 หรือ utf8mb3 เอาไว้ แต่ต้องการเปลี่ยน charset และ collate เป็น utf8mb4 แต่การจะมานั่ง update ทีละตารางก็ค่อนข้างเสียเวลา เลยต้องเกิดนวัตกรรมสำหรับความขี้เกียจนี้

สร้างชุดคำสั่ง SQL สำหรับเปลี่ยนแปลง CHARACTER SET และ COLLATE ของ Table

โปรดแทนที่ your_database_name ด้วยชื่อฐานข้อมูลของคุณที่ต้องการเปลี่ยนแปลง CHARACTER SET และ COLLATE ของตารางทั้งหมดในนั้น คำสั่งนี้จะสร้างคำสั่ง ALTER TABLE สำหรับทุกตารางในฐานข้อมูลที่กำหนดให้มี CHARACTER SET utf8mb4 และ COLLATE utf8mb4_general_ci โดยไม่ต้องเขียนคำสั่ง ALTER TABLE แยกแต่ละตารางเองโดยตรง หลังจากนั้นคุณสามารถคัดลอกและวางคำสั่งที่ได้มาเพื่อเรียกใช้การเปลี่ยนแปลงนี้ในฐานข้อมูลของคุณได้โดยตรง โดยต้องระวังในการใช้คำสั่งนี้ และแนะนำให้สร้างสำรองข้อมูลก่อนดำเนินการใดๆ ที่เป็นการเปลี่ยนแปลงที่สามารถกระทำได้ในฐานข้อมูลของคุณ

USE your_database_name;

SET foreign_key_checks = 0;

SELECT
  CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') AS sql_statements
FROM
  information_schema.tables
WHERE
  table_schema = 'your_database_name';

SET foreign_key_checks = 1;

ก็จะได้คำสั่ง ALTER TABLE ดังรูปแบบนี้

ALTER TABLE `amphures` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `arduino` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `ath_admidis` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `ath_appointment` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `ath_article` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `ath_banner` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

สร้างชุดคำสั่ง SQL สำหรับเปลี่ยนแปลง CHARACTER SET และ COLLATE ของ Column

โปรดแทนที่ your_database_name ด้วยชื่อฐานข้อมูลของคุณที่ต้องการเปลี่ยนแปลง CHARACTER SET และ COLLATE ของคอลัมน์ในทุกตาราง คำสั่งนี้จะสร้างคำสั่ง ALTER TABLE สำหรับปรับปรุงคอลัมน์ในทุกตารางในฐานข้อมูลที่กำหนดให้มี CHARACTER SET utf8mb4 และ COLLATE utf8mb4_general_ci โดยไม่ต้องเขียนคำสั่ง ALTER TABLE แยกแต่ละตารางเองโดยตรง หลังจากนั้นคุณสามารถคัดลอกและวางคำสั่งที่ได้มาเพื่อเรียกใช้การเปลี่ยนแปลงนี้ในฐานข้อมูลของคุณได้โดยตรง โดยต้องระวังในการใช้คำสั่งนี้ และแนะนำให้สร้างสำรองข้อมูลก่อนดำเนินการใดๆ ที่เป็นการเปลี่ยนแปลงที่สามารถกระทำได้ในฐานข้อมูลของคุณ

USE your_database_name;

SET foreign_key_checks = 0;

SELECT
    CONCAT( 'ALTER TABLE ', TABLE_Name, ' MODIFY ', COLUMN_NAME, ' ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;' ) command 
FROM
    information_schema.COLUMNS 
WHERE
    TABLE_SCHEMA = 'your_database_name' 
    AND CHARACTER_SET_NAME <> 'utf8mb4';

SET foreign_key_checks = 1;

ก็จะได้คำสั่ง ALTER TABLE ดังรูปแบบนี้

ALTER TABLE edc_transaction MODIFY personal_name varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE edc_transaction MODIFY ref_pid varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE edc_transaction MODIFY pid_cus varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE edc_transaction MODIFY approve_code varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE food_bed MODIFY bed_number varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

สรุป

ทดลองแล้วช่วยละระยะเวลาได้เยอะทีเดียว แต่ควรระมัดระวังการแปลงข้อมูลจาก TIS-620, WINDOWS-874 มาเป็น UTF-8 อาจจะทำให้ภาษาเพี้ยนไปเลย ในตารางที่มีข้อมูลอยู่จำนวนมาก จะใช้เวลาในการ MODIFY COLUMN นานไปด้วย