ปัญหา NLS Character Set ของหมวยอินเตอร์

ปัญหา NLS Character Set ของหมวยอินเตอร์

คุณอาดีคะ หนูกุ้มใจจิง ๆ ค่ะ ไม่รู้จะไปปรึกษาใครได้แล้ว คือหนูทำงานเป็นโปรแกรมเมอร์ของบริษัทอินเตอร์ ฯ แห่งหนึ่ง Boss ใหญ่หนูเป็นอเมริกันน่ะค่ะ วันหนึ่ง Boss ก็เรียกหนูไปพบ แล้วเขาก้อสั่งมาว่าต้องการให้ฐานข้อมูลของเราเก็บข้อมูลได้หลาย ๆ ภาษาให้ไปหาวิธีมา หนูไปถามพี่แอ๊ด(มิน)ดูแล้ว พี่แอ๊ดบอกว่าฐานข้อมูลของเรามีคาร์ ๆ อะไรเซ็ท ๆ นี่ล่ะค่ะเห็นบอกว่าเป็น US แล้วเขายังพึมพำแกมบ่นอีกด้วยค่ะว่า ไผสิท๊ามมม..ด้าย หนูจะทำยังไงดีคะคุณอาดี..อิ๊ก..อิ๊ก.. คริสตีน หว่อง (T T) คุณอาดี(บีเซอร์ทิฟาย): โจทย์ที่หนูคริสตีนได้รับมานั้นเป็นที่พบเห็นได้ไม่น้อยในบริษัทต่างชาติที่มีสาขาอยู่ในต่างประเทศ โดยเฉพาะบริษัทที่ใช้ภาษาอังกฤษเป็นภาษาหลัก ระบบเดิมของหนูคริสตีนใช้ฐานข้อมูล Oracle โดยมี US7ASCII เป็น Character set หลัก (Database Character Set) ที่ใช้กับคอลัมน์ที่มี Data Type เป็น CHAR หรือ VARCHAR2 และมี UTF8 เป็น National Character Set ซึ่งใช้กับคอลัมน์ที่มี Data Type เป็น NCHAR หรือ NVARCHAR2 (Listing 1) (Listing 1) ************************************************************** SQL> column parameter format a30 SQL> column value format a30 SQ>> select parameter ,value from nls_database_parameters; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET US7ASCII NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM PARAMETER VALUE ------------------------------ ------------------------------ NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET UTF8 NLS_RDBMS_VERSION 10.1.0.5.0 20 rows selected. แต่เดิมไม่ได้มีการใช้ column type ที่เป็น NCHAR (หรือ NVARCHAR2) แต่อย่างใด requirement ใหม่กำหนดให้ฐานข้อมูลสามารถรองรับข้อมูลที่เกี่ยวกับที่อยู่อาศัยที่เป็นภาษาอื่นๆ ที่นอกเหนือจากภาษาปัจจุบันที่ใช้อยู่ เช่นให้รองรับ ภาษาจีน เป็นต้น จริงๆแล้วเดิมระบบของหนูคริสตีนมีการใช้ภาษาอื่น ๆ นอกเหนือจากภาษาอังกฤษในฐานข้อมูลอยู่แล้ว เช่นภาษาในยุโรปบางภาษาเป็นต้น แต่ก็เป็นตัวอักษรแบบ single byte โดยใช้ Database Character Set รวมๆกัน คือ US7ASCII วิธีแก้ปัญหา: การเซ็ทอัพให้ฐานข้อมูลรองรับได้ทุกๆภาษาโดยใช้ UNICODE ได้ มีสองวิธี  วิธีแรกคือเซ็ทอัพให้ทั้งฐานข้อมูลเป็นแบบ UNICODE วิธีนี้เหมาะกับกรณีที่ข้อมูลเป็นแบบหลายภาษาเฉลี่ยๆ กัน ไม่สามารถบอกได้ว่าภาษาใดมากกว่า และมีภาษาทั้งภาษายุโรป และเอเชียคละกันอยู่ เนื่องจากตัวอักษรที่ encode แบบ UNICODE จะมีขนาดของตัวอักษรใหญ่กว่าแบบ single-byte จึงทำให้เปลืองเนื้อที่บนดิสก์และหน่วยความจำมากกว่า และประสิทธิภาพจะด้อยกว่าฐานข้อมูลแบบ single-byte  วิธีที่สองคือเซ็ทเป็นฐานข้อมูลแบบ single-byte แล้วเซ็ทแค่บางคอลัมน์ให้รองรับได้หลายภาษา วิธีนี้จะเหมาะกับฐานข้อมูลที่มีภาษาที่ใช้ตัวอักษรแบบ single-byte เป็นหลัก (เช่นภาษาอังกฤษ) แล้วมีบางคอลัมน์เป็นภาษานานาชาติ วิธีนี้จะได้ performance ของระบบที่ดีกว่าและประหยัดทรัพยากรระบบมากกว่า การเซ็ทอัพนี้ทำได้เมื่อตอน create database เท่านั้น หากต้องการเปลี่ยนจะต้อง recreate database ใหม่หรือใช้ CSALTER script ร่วมกับ exp/imp utilities เนื่องจากระบบเดิมใช้ Database Character Set เป็นแบบ single-byte Character Set (US7ASCII) ซึ่งไม่สามารถรองรับภาษาทางเอเชียได้ และเนื่องจากความต้องการใช้ภาษาเพิ่มเติมเหล่านี้ในเฉพาะบางคอลัมน์เท่านั้น จึงใช้วิธีการกำหนดให้บางคอลัมน์เป็น NCHAR (,NVARCHAR2) เพื่อรองรับภาษาที่เป็น multi-byte (UNICODE) โดยคอลัมน์เหล่านี้จะใช้ National Character Set ที่เป็น UTF8 ตามที่ได้กำหนดไว้เมื่อตอน create database ผลข้างเคียงที่อาจเกิดขึ้นได้: การเปลี่ยนคอลัมน์จากเดิมเป็น CHAR (, VARCHAR2) ไปเป็น NCHAR (,NVARCHAR2) จะไม่มีผลกระทบอะไร เนื่องจากเป็นการเปลี่ยนไปเป็น Character Set ที่ใหญ่กว่า (ดู Listing 2) (Listing 2) ************************************************************** SQL> create table test_char (cname char(10), vname varchar2(10)); Table created. SQL> insert into test_char values ('SCOTT','SCOTT'); 1 row created. SQL> select dump(cname,1010), dump(vname,1010) from test_char2; DUMP(CNAME,1010) ---------------------------------------------------------------- DUMP(VNAME,1010) ---------------------------------------------------------------- Typ=96 Len=10 CharacterSet=US7ASCII: 83,67,79,84,84,32,32,32,32,32 Typ=1 Len=5 CharacterSet=US7ASCII: 83,67,79,84,84 --<< สังเกต CharacterSet เป็น US7ASCII SQL> alter table test_char modify (cname nchar(10), vname nvarchar2(10)); Table altered. SQL> select dump(cname,1010) , dump(vname,1010) from test_char; DUMP(CNAME,1010) ---------------------------------------------------------------- DUMP(VNAME,1010) ---------------------------------------------------------------- Typ=96 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32 Typ=1 Len=5 CharacterSet=UTF8: 83,67,79,84,84 --<< สังเกต CharacterSet เป็น UTF8 เมื่อแปลงมาเป็น NCHAR (,NVARCHAR2) *** DUMP เป็นฟังก์ชันของ Oracle ที่จะแสดงรหัสของตัวอักษรออกมาเป็นไบท์ แต่ในทางกลับกันเราไม่สามารถที่จะเปลี่ยน NCHAR (,NVARCHAR2) ไปเป็น CHAR (,VARCHAR2) โดยไม่ลบข้อมูลออกจากคอลัมน์นั้นก่อนได้ ด้วยเหตุผลที่กล่าวมาแล้วคือ NCHAR (,NVARCHAR2) มีขนาดของ Character Set ที่ใหญ่กว่า (ดู Listing 3) (Listing 3) ************************************************************** SQL> desc test_char Name Null? Type ----------------------- -------- ---------------------- CNAME NCHAR(10) VNAME NVARCHAR2(10) SQL> alter table test_char modify (cname char(10), vname varchar2(10)); alter table test_char modify (cname char(10), vname varchar2(10)) * ERROR at line 1: ORA-01439: column to be modified must be empty to change datatype{/xtypo_code} นอกจากนี้เราสามารถเปลี่ยนจาก NCHAR มาเป็น NVARCHAR2 หรือในทางกลับกันจาก NVARCHAR2 มาเป็น NCHAR ได้ อย่างไรก็ดีให้สังเกตดู space ที่ถูก pad เข้าไปเมื่อ data type ถูกเปลี่ยนเป็น NCHAR ด้วย (ดู Listing 4) (Listing 4) ************************************************************** SQL> alter table test_char modify (cname nvarchar2(10), vname nchar(10)); Table altered. SQL> select dump(cname,1010), dump(vname,1010) from test_char; DUMP(CNAME,1010) ----------------------------------------------------------------- DUMP(VNAME,1010) ----------------------------------------------------------------- Typ=1 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32 Typ=96 Len=10 CharacterSet=UTF8: 83,67,79,84,84,32,32,32,32,32 ผลกระทบที่อาจจะเกิดกับ application: NCHAR (, NVARCHAR2) กำหนดความกว้างของคอลัมน์เป็นจำนวนตัวอักษร ในขณะที่ CHAR (,VARCHAR2) กำหนดเป็น byte ดังนั้นหากเดิม กำหนดเป็น CHAR(10) ซึ่งกินพื้นที่ 10 bytes เมื่อแปลงเป็น NCHAR(10) อาจจะกินพื้นที่ได้ตั้งแต่ 10 - 40 ไบท์ ขึ้นอยู่กับว่าข้อมูลที่เก็บเป็นภาษาอะไรเช่นถ้าเป็นภาษาเอเชียใช้ 3 bytes ต่อตัวอักษรก็จะกินพื้นที่ถึง 30 bytes เป็นต้น

NLS_LANG คือตัวแปร Environment บนฝั่ง Client ไม่ใช่บน Database Server

อย่างที่ผมได้ให้หัวเรื่องไว้เกี่ยวกับ NLS_LANG คือตัวแปรตัวนี้เป็นตัวแปรบนฝั่ง Client หรืออย่างน้อยก็เป็นตัวแปรของ Client Application (ซึ่งจริง ๆ แล้วอาจจะ Install ไว้บน Database Server ก็ได้) ที่จะติดต่อกับฐานข้อมูล เพื่อให้เห็นภาพลองดูวิธีการตั้งค่าตัวแปรตัวนี้บน Windows ดูกันหน่อยนะครับ

C:\>set NLS_LANG="ARABIC_UNITED ARAB EMIRATES.AR8MSAWIN"
C:\>echo %NLS_LANG%
"ARABIC_UNITED ARAB EMIRATES.AR8MSAWIN"

เรา set NLS_LANG บน client เพื่อบอกว่าขณะนี้เราจะ connect ด้วย environment แบบไหน โดยตอน select ถ้า Character Set ในตัวอย่างคือ (AR8MSAWIN) มีขนาดเล็กกว่า Database Character Set เราจะเห็นเป็น question mark หมายถึงด้วย environment ของเรา ไม่รู้จักตัวอักษรที่ database ส่งมาให้ เช่นถ้า Database Character Set เป็น UTF8 แต่เรา set NLS_LANG ที่เครื่องเป็น US7ASCII ตัวอักษรที่ส่งมาจาก Database ทีไม่อยู่ใน range ที่ US7ASCII รู้จักจะกลายเป็น ???

ในทางกลับกันตอน insert ถ้า database มี Character Set ที่เล็กกว่าของเครื่อง Client ที่ insert เช่น database เป็น US7ASCII แต่เครื่อง client set NLS_LANG=american_america.TH8TISASCII ข้อมูลภาษาไทยที่เรา insert เข้าไปจะกลายเป็น ??? แต่ถ้า database เป็น TH8TISASCII เหมือนกับเครื่อง Client (หรือเป็น Character Set ที่เป็น superset ของ TH8TISASCII จะสามารถ insert ได้ ดังตัวอย่างข้างล่าง เราConnect เข้ากับ orcl3 ซึ่งมี Character Set เป็น US7ASCII (พารามิเตอร์ชื่อ NLS_CHARACTER_SET) โดยใช้ sqlplus บน DOS Command Window

C:\>set nls_lang=american_america.th8tisascii
C:\>sqlplus oe@orcl3
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 15:04:40 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII

1 rows selected.

SQL> desc test_char;
Name Null? Type
----------------------------------------- -------- ----------------------------
CNAME NCHAR(10)
VNAME NVARCHAR2(10)

SQL> insert into test_char(cname) values ('ธนากร');
1 row created.

SQL> select * from test_char;
CNAME VNAME
---------- ----------
?????

จากตัวอย่างเรา Insert เข้าไปในตารางในฐานข้อมูลที่มี Character Set ที่เป็น US7ASCII ในขณะที่เครื่อง Client ที่ใช้ในการ Insert มี Character Set (ที่ีตั้งค่าโดย NLS_LANG) ที่มีขนาดใหญ่กว่า US7ASCII (TH8TISASCII มีขนาด 8 บิท และมีจำนวนตัวอักษรมากกว่า US7ASCII ซึ่งมีขนาด 7 บิท) ค่าที่ได้จากการ Insert ตัวอักษรที่ไม่อยู่ในชุดตัวอักษร US7ASCII เลยจึงกลายเป็น '?' ทุกตัว

คราวนี้เราลองมาทดสอบกับฐานข้อมูลที่มี Character Set ที่เป็น TH8TISASCII บ้าง (NLS_LANG ยังคงเป็น TH8TISASCII)

SQL> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET TH8TISASCII

1 rows selected.
SQL> create table test_char (cname nchar(10), vname nvarchar2(10));
Table created.

SQL> insert into test_char (cname) values ('ธนากร');
1 row created.

SQL> select * from test_char;
CNAME VNAME
---------- ----------
ธนากร
1 row selected.

ดังนั้นเพื่อให้แน่ใจว่าจะไม่เกิดการสูญเสียข้อมูลมีข้อควรระลึกถึงเกี่ยวกับ NLS_LANG ดังนี้
1. ตั้งค่า NLS_LANG ให้เป็นตัวเดียวกับ Database Character Set เสมอ
2. ถ้าทำอย่างกรณีข้อ 1 ไม่ได้ และต้องทำ DML กับฐานข้อมูล(เช่น Insert, Update, Delete) ให้ตั้งค่า NLS_LANG ให้เป็น Subset ของ Database Character Set
3. ถ้าทำอย่างกรณีข้อ 1 ไม่ได้ และต้องทำการ Select ข้อมูลอย่างเดียว ให้ตั้งค่า NLS_LANG ให้เป็น Superset ของ Database Character Set

หมายเหตุ
1. Database Character Set สามารถดูได้จากตัวแปร 'NLS_CHARACTERSET'
2. คำสั่ง Set NLS_LANG จะมีผลต่อ Session นั้น ๆ เท่านั้น ถ้าเราปิด Command Window แล้วเปิดใหม่จะต้อง Set ค่าตัวนี้ใหม่ ถ้าต้องการให้มีผลถาวรอาจจะเข้าไปตั้งค่า Environment Variables (คลิ๊กขวาที่ My Computer เลือก Properties => คลิ๊กเลือก Advanced Tab แล้วคลิ๊ก Environment Variables)
3. โดยปกติหากเราไม่ได้ตั้งค่า NLS_LANG ค่าดีฟอลต์จะเก็บอยู่ที่ Registry ของเครื่องใน HKEY_LOCAL_MACHINE => SOFTWARE => ORACLE => KEY_OraDb10g_home1 ให้ดูที่ Registry ทางขวามือชื่อ NLS_LANG ซึ่งส่วนที่อยู่หลังจุดจะเป็น Character Set เช่น AMERICAN_AMERICA.TH8TISASCII ก็หมายความว่าเครื่อง Client นี้ (ถ้าไม่ได้ตั้งค่า NLS_LANG ดังวิธีการอื่น ๆ ที่กล่าวมา) มี Character Set เป็น TH8TISASCII

บทความที่เกี่ยวเนื่องกัน
1. การใช้ NCHAR และการกำหนด Character Sets