[MySQL] เมื่อ DataBase ผมเป็นยำสามช่า(Charset เป็น latin1, tis620, utf8) เย้ยยยย

จะเขียนบทความนี้มานาาาาาาาาาาาาาาาานมาก แต่จนแล้วจนรอดก็ติดปัญหา โน่นๆ นี่ๆ มาตลอด ลองจินตนาการว่ากำลังจะเปิด CS:GO ขึ้นมาเล่นแล้วก็มีโทรศัพท์ดังขึ้น "แก้งานหน่อย" ... เดี๋ยวนะๆ นี่มันตรงกับชีวิตจริงนี่หว่า ... มาเกริ่นเข้าเรื่องกันก่อนดีกว่า คือทีแรกได้โจทย์มาครับว่า ให้ทำระบบ Backup ข้อมูล เพราะว่าเซิฟเวอร์อีกตัวที่ทำระบบ Sync เอาไว้ มีบริษัทที่เค้ากำลังเข้ามาพัฒนาระบบ รพ. เค้าเอาไปใช้ทำเซิฟเวอร์สำหรับ 43แฟ้ม แล้วบริษัทเค้าก็อยากได้ข้อมูลก้อนนี้ด้วยซึ่งต้องเป็น utf8 ด้วยนะเออ ไอ่ชิพหาย!!! ไม่ได้ด่าเค้านะครับคือผมอุทานในใจ จะแปลงยังไงล่ะฮึ DB ก้อนที่มีอยู่นี่แกงโฮ๊ะดีๆนี่เลยนะเฮ้ย! มันมีหมดเลยครับ ไล่เริ่มต้นมาตั้งแต่ latin1, tis620 แล้วก็ utf8

หัว ไหล่ ตูด

โจทย์แรก Export ฐานข้อมูล
จากที่เคย Export แล้วก็ Import มาไม่น่าจะมีปัญหาเท่าไร เพราะเคยทำมาแล้ว แต่ปัญหามันอยู่ที่ปริมาณข้อมูลขนาดเกือบ 10GB นี่สิ Export มาทีมันจะติด Read lock ในตารางที่เรากำลัง Export นี่ล่ะ(ไม่แน่ใจว่าเป็นเพราะ Engine เป็น MyISAM ด้วยรึป่าว) กลายเป็นว่าช่วง 8โมง ถึง เที่ยงคืน แทบจะทำอะไรไม่ได้เลย เพราะหน้างาน ไม่ว่าจะเป็นทะเบียน ห้องฉุกเฉิน ห้องยา ฯลฯ เค้ากำลังใช้งานอยู่ งานนี้เลยต้องแก้ปัญหาเฉพาะหน้าไปก่อน โดย หาระยะเวลาของผู้ใช้งานที่น้อยที่สุดเท่าที่จะเป็นไปได้ แล้วก็ได้เวลาออกมาเป็นตอนประมาณ ตี3
A: ตี3
B: ใช่ครับ ตี3
A: น้องควบมอไซค์มา Export นะครับ
B: แฮร่~~~ แบบนี้ก็ได้หรอ
ใครมันจะมาตอนตี3 ไอ่บ้า! งานนี้เลยต้องมีตัวช่วยครับ นั่นคือออออออ crontab นั่นเองงงงง แต่เวลาเราเขียนใน crontab ผมว่าใส่ path เต็มเพื่อความชัวร์ดีกว่า เช่น /usr/bin/mysqldump อะไรก็ว่าไป ตำแหน่งไฟล์ที่ Export ออกมาก็ต้องชัดเจน เช่น /home/testuser/testdb.sql เป็นต้น เนื่องจากตัว DB มันเริ่มสร้างมาจาก Latin1 เลยต้องกำหนด charset เข้าไปด้วย เลยกลายมาเป็นโกโก้ครั้นช์ ถรุ๊ย! ตัวอย่างในบทความนี้ขอรวบรัดคำสั่งหน่อยนะครับ

mysqldump -u root -p1234 --default-character-set=latin1 testdb > /home/testuser/testdb.sql 

ปล. mysqldump ตรงจุดที่เป็นคำสั่งการ insert มันจะติดกันยาวเหยียด ทำให้ยากต่อการอ่านเป็นอย่างมาก เราสามารถสร้างท่าเสริมได้โดยเพิ่มคำสั่ง --extended-insert=FALSE แค่นี้มันก็จะตัด insert เป็นบรรทัดใหม่ให้แล้ว :)

โจทย์ที่สอง ตัดComment Header ออกไป
ต่อเนื่องมาจากปัญหาแรก ก็คือตอนเราสั่ง mysqldump มันจะมี Comment ด้านบนไฟล์ติดมาด้วยซึ่งในนั้นเองมันมีคำสั่งพวก character_set_client เป็น utf8 ทำให้เวลาเรา Import ข้อมูลกลับเข้ามาภาษามันเพี้ยนตามไปด้วย หลังจากลองถามพี่กู แล้ว พี่กู บอกว่าแก้ตามนี้ไอ่น้องเอ้ย เลยต้องปรับใหม่เป็น

mysqldump -u root -p1234 --default-character-set=latin1 testdb | egrep -v \"(^SET|^/\*\!)\" > /home/testuser/testdb.sql 

ลำบากกรูวจริงๆ เห้เอ้ย

โจทย์ที่สาม เปลี่ยน Charset ทุกตัวให้เป็น utf8
ไฟล์ขนาดเกือบๆ 10GB คงไม่มี TextEditor ตัวไหนเปิดได้ละนะ ก็เอาล่ะวะคงต้องอาศัยบารมีในคำสั่งของ Linux นี่ล่ะ พี่กู ก็แนะมาอีกว่าดูนี่สินายจ๋า ผมล่ะอยากจะกราบงามๆ จริงๆ ครับพี่ไม่ได้พี่ผมคงตายไปนานแล้ว จัดไปสิครับ

sed -i 's/latin1/utf8/g; s/tis620/utf8/g; s/InnoDB/MyISAM/g' /home/testuser/testdb.sql 

แต่จริงๆ มันยังไม่จบมันจะไปมีปัญหาตอนเราทำ iconv อีกทีเพราะมันดันทะลึ่งเก็บรายละเอียดโครงสร้างของตัว DB เป็น utf8 (ถ้าเราลองเข้าไปดูใน information_schema) แต่ตัว data เป็นพวก latin1 พอตอนที่เรา Export โดยกำหนด charset เป็น latin1 Comment ในตัวโครงสร้างภาษาเลยเพี้ยนตามไปด้วยแล้วพอมาพบกับ iconv อีกเลยกลายเป็นว่ามันอ่านไอ่ภาษาที่เพี้ยนไม่ได้อีก เจ้งเลยฮะคราวนี้ เลยต้องแก้เกมด้วยการลบมันออกให้หมด ช่างแม่ง ไปอ่านใน Data dictionary เอาเองละกัน เลยมาจบด้วยท่านี้ครับ(มั่วเท่าที่จะมั่วได้ Regex มันเขียนแปลกๆ)

sed -i -r 's/latin1/utf8/g; s/tis620/utf8/g; s/InnoDB/MyISAM/g; s/COMMENT '.\+'/,/g; s/COMMENT='.\+'/;/g' /home/testuser/testdb.sql

จบเหอะ

ตบท้ายด้วยการเปลี่ยน Encoding ของไฟล์
ปัญหาสุดท้ายนี่ด้วยความที่ไม่ค่อยจะเข้าใจเท่าไรเลยต้องไปอ่านใน thaicert มาสอบรอบสามรอบเลยพอจะเดาๆ ได้ว่าบน Linux ส่วนใหญ่จะรองรับ TIS620, CP874 แล้วก็ ISO-8859-11 ประมาณนี้ เลยจบด้วยท่านี้ครับ

iconv -f CP874 -t UTF8 /home/testuser/testdb.sql > testdb_iconv.sql

หลังจากนั้นก็ลองเอาไป Import กลับเข้าไปผ่าน MySQL Workbench ก็ผ่าาาาาน เฮ้

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

Ref.

1. How do I use mysqldump to export only the CREATE TABLE commands?
2. How to replace multiple patterns at once with sed?
3. how to get list of supported encodings by iconv library in php?
4. sed, a stream editor
5. MySQL Comment Syntax
6. ข้อควรระวังในการใช้เครื่องมือตรวจพิสูจน์พยานหลักฐานดิจิทัลในการค้นหาคำหรือข้อความภาษาไทย
7. MySQLDump one INSERT statement for each data row