การ dump ข้อมูลใน MySQL ให้ถูกต้องตาม Character-set

สำหรับบันทึกนี้ขอกล่าวถึงเคล็ดลับในการแปลงฐานข้อมูลจากเดิมที่เป็น latin1 หรือ tis620 ให้เป็น utf8 นะครับ

มี เงื่อนไขเบื้องต้นว่า หาก character-set ของฐานข้อมูลเป็น tis620 หรือ latin1 (ความจริงเป็น tis620 ครับ เพราะ latin1 ไม่ใช่ character-set ของภาษาไทย) ต้องไม่กำหนดค่า

default-character-set=utf8

ใน my.cnf (สำหรับ Linux อยู่ที่ /etc/my.cnf หรือ /etc/mysql/my.cnf)

หลาย คนอาจจะชินกับการใช้ phpMyAdmin หากใช้แล้วไม่มีปัญหาก็ใช้ต่อไปครับ โดยปกติ เวลาจะ dump ข้อมูลโดยใช้ phpMyAdmin ก็ใช้วิธี export ออกมาเป็น SQL จะดีที่สุด วิธีที่จะดูว่า มีปัญหาหรือไม่ ก็คือ ลองเปิดไฟล์ที่ Export ออกมานั้นด้วย Text Editor ดู หรือใช้ command อื่นๆ เปิดดูก็ได้ครับ หากสามารถอ่านออกเป็นภาษาไทยได้ ก็แสดงว่า ไม่มีปัญหาครับ

ใน ที่นี้อยากแนะให้ใช้คำสั่ง mysqldump ซึ่งเป็น command line tool ที่นิยมใช้ใน Linux ครับ เข้าใจว่า ใน MS WIndows ก็มีเหมือนกัน วิธีใช้ก็ไม่น่าจะต่างกันมากครับ

ในการ dump ข้อมูล สิ่งที่ต้องทราบคือ character-set ของข้อมูลคือ อะไร ส่วนใหญ่จะไม่หนีจาก 3 ตัวนี้ครับ คือ latin1, tis620 และ utf8

หากไม่ทราบ ก็ลองด้วยการกำหนด character-set ไปเรื่อยๆ

วิธีการใช้คำสั่งก็ คือ

mysqldump --default-character-set=latin1 -h MySQLserver -u username -p DBName >DBName.sql

ตัว สีแดงคือ ค่าที่ต้องกำหนดให้ถูกต้องครับ หากเครื่องที่ทำการเรียกคำสั่ง mysqldump เป็นเครื่องเดียวกับ MySQL Server ก็ไม่ต้องมี "-h MySQLserver" ก็ได้ครับ

username คือ ชื่อ user ที่มีสิทธิ์ในการ access ฐานข้อมูล DBName

หาก โปรแกรมจัดการฐานข้อมูลที่ใช้อยู่แสดงผลเป็น character-set 8 bits (พวก tis-620, windows-874, iso-8859-11) การกำหนด --default-character-set=latin1 มักจะไม่มีปัญหาครับ ซึ่งหมายถึงอ่านออกเป็นภาษาที่ถูกต้องแน่นอนครับ

แต่สิ่งที่ต้องทราบต่อไปก็คือ ภาษาไทยที่แสดงนั้น มี character-set ที่แท้จริงเป็น tis620 ครับ ไม่ใช่ latin1 ครับ

หากเปิดไฟล์ DBName.sql ดู จะพบว่า ที่หัวไฟล์มีหลายบรรทัดเป็น

-- MySQL dump 10.9
--
-- Host: localhost Database: moodle
-- ------------------------------------------------------
-- Server version 4.1.20

ส่วน 5 บรรทัดข้างบนนี้ เป็น comment จะไม่สนใจก็ได้ครับ

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES latin1 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 *
/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

หลาย บรรทัดเหล่านี้ดูเหมือนจะเป็น comment แต่ไม่ใช่ครับ มันมีความหมายครับ เช่น 40101 หมายถึง MySQL รุ่น 4.1.1 และ 40014 หมายถึง MySQL รุ่น 4.0.14 และในแต่ละบรรทัดก็มีความหมายว่า หากเป็น MySQL รุ่นเท่ากับหรือสูงกว่าที่บอกไว้ต้นบรรทัด ก็จะ run คำสั่งที่ตามมา เช่น

/*!40101 SET NAMES latin1 */; ก็หมายถึง หากเรา import ข้อมูล DBName.sql เข้า MySQL Server รุ่นที่เท่ากับหรือสูงกว่า 4.1.1 ก็จะมีการสั่งให้ SET NAMES latin1 ซึ่งหมายถึงการตั้งค่าการสื่อสารข้อมูลด้วย character-set lantin1 แต่ถ้าเป็นการ import ข้อมูลเข้า MySQL Server รุ่นที่ต่ำกว่า 4.1.1 บรรทัดนั้นก็จะถูกข้ามไปครับ

สิ่งที่สำคัญในที่นี้ก็คือ การกำหนดให้เป็น latin1 ในที่นี้ อาจจะทำให้ import ข้อมูลผิดพลาด หากไม่แน่ใจก็ให้ลบบรรทัดนี้ทิ้งไป

ส่วนที่จะเป็นปัญหาต่อมา คือ ในส่วนของไฟล์ที่เกี่ยวกับการสร้างตาราง เช่น

CREATE TABLE `mdl_assignment` (
`id` int(10) unsigned NOT NULL auto_increment,
`course` int(10) unsigned NOT NULL default '0',
`description` text NOT NULL,
`format` tinyint(2) unsigned NOT NULL default '0',
`resubmit` tinyint(2) unsigned NOT NULL default '0',
`type` int(10) unsigned NOT NULL default '1',
`maxbytes` int(10) unsigned NOT NULL default '100000',
`timedue` int(10) unsigned NOT NULL default '0',
`grade` int(10) NOT NULL default '0',
`timemodified` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `course` (`course`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ให้สังเกตที่บรรทัดสุดท้ายครับ ตรงนี้ ให้ทำการลบ DEFAULT CHARSET=latin1 ออกให้หมด

ตรวจสอบไฟล์ให้แน่ใจว่า สามารถอ่านได้เป็นปกติ โดยที่เป็น tis-620

ขั้นตอนต่อมา คือ การ import ข้อมูลเข้า MySQL Server ที่กำหนด default-character-set=utf8 ใน my.cnf แล้ว

โดยปกติ ก็ใช้คำสั่ง

mysql --default-character-set=tis620 -h MySQLserver -u username -p DBName < DBName.sql

สังเกต ว่า การกำหนด --default-character-set=tis620 ในคำสั่งนี้ ต้องระบบ character-set ของไฟล์ให้ตรงกัน โดยไม่จำเป็นต้องแปลงไฟล์ให้เป็น utf8 ครับ เพราะ MySQL Server จะทำการแปลงให้เอง

สิ่งสำคัญต่อไป ก็คือ โปรแกรมที่ใช้จัดการฐานข้อมูลครับ ต้องใช้ character-set เหมือนกับของฐานข้อมูลเท่านั้นครับ และก็มีเคล็ดลับอีกเล็กน้อย ซึ่งจะได้กล่าวในบันทึกอื่นต่อไปครับ

ที่มา: http://share.psu.ac.th/blog/myopensource/5425