通過oracle類比MySQL中的位元組字元問題

jeanron100發表於2015-02-12
OracleMySql字元
在幾個月前寫過一篇博文 MySQL資料型別 http://blog.itpub.net/23718752/viewspace-1371434/
 當時寫完以後有同事朋友就提出了一些疑問，對於漢字在MySQL和Oracle中的存放情況希望我能夠詳細的說說。
 關於MySQL中的varchar字元型別，自己的操作都是基於字符集UTF-8。
 對於存放漢字，涉及到字元，位元組，編碼的一些知識，我查了一下，自己先補補，發現有一個帖子已經描述的很詳細了。直接引用過來。
 http://www.regexlab.com/zh/encoding.htm
從編碼的發展來看，大致可以分為三個階段。系統內碼  說明                          系統
階段一     ASCII                       計算機剛開始只支援英語，其它語言不能夠在計算機上儲存和顯示。
階段二     ASCII(本地化)         為使計算機支援更多語言，通常使用 0x80~0xFF 範圍的 2 個位元組來表示 1 個字元。比如：漢字 '中' 在中文作業系統中，使用 [0xD6,0xD0] 這兩個位元組儲存。
                                              然後不同的國家和地區制定了不同的標準，由此產生了 GB2312, BIG5, JIS 等各自的編碼標準。這些使用 2 個位元組來代表一個字元的各種漢字延伸編碼方式，稱為 ANSI 編碼。在簡體中文系統下，ANSI 編碼代表 GB2312                                               編碼，在日文作業系統下，ANSI 編碼代表 JIS 編碼。
階段三    UNICODE(國際化)    為了使國際間資訊交流更加方便，國際組織制定了 UNICODE 字符集，為各種語言中的每一個字元設定了統一併且唯一的數字編號，以滿足跨語言、跨平臺進行文字轉換、處理的要求。
 因為對Oracle中的一些細節略為熟悉，所以能夠旁敲側擊出MySQL中的一些相通的地方。
在編碼的基礎上，字元，位元組的關係就很重要了。
字元是一個抽象意義的符號，一個漢字或一個字母都是一個字元。
而位元組是計算機中儲存資料的單元，一個8位的二進位制數
如果對Oracle接觸長了，再用MySQL，一個很糾結的地方就是漢字的存放，在MySQL中，漢字和字母都是平等對待的，都是按照字元來存放的。
但是Oracle中卻不然，可以聚個簡單的例子。
SQL> create table test (name varchar2(6));
Table created.
SQL> insert into test values('123456');
1 row created.
SQL> insert into test values('一二三');
insert into test values('一二三')
                        *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."TEST"."NAME" (actual: 9, maximum:6)
SQL> insert into test values('一二');
1 row created.
在Oracle中有一個dump函式能夠很清晰的檢視出資料的儲存情況。
比如下面的情況，檢視漢字和字母，每個漢字是按照3個位元組來存放的，每個字母則是一個位元組。
SQL> select dump('你好') from dual;
DUMP('你好')
-------------------------------------
Typ=96 Len=6: 228,189,160,229,165,189
SQL> select dump('a') from dual;
DUMP('A')
----------------
Typ=96 Len=1: 97
這一點和MySQL存在著明顯的差別，Oracle中其實也可以得到和MySQL同樣的效果。
這就涉及到一個資料庫引數NLS_LENGTH_SEMANTICS，這個引數用於指定CHAR列或VARCHAR2列的長度定義方式，預設值為BYTE。當設定該引數為BYTE時，定義CHAR列或VARCHAR2列採用位元組長度方式；當設定該引數為CHAR時，定義CHAR列或VARCHAR2列採用字元個數方式。
為了不傷筋動骨，我就在session級別做一些變更來說明這個問題。首先把它從byte變更為char（注意這個引數在oracle中是作為基本的初始化引數，一般不需要修改)
SQL>ALTER SESSION SET nls_length_semantics=char;
建立一個測試表，指定欄位長度為varchar2(6)
SQL> create table test_char (name varchar2(6));
Table created.
SQL> insert into test_char values('北京');
1 row created.
SQL> insert into test_char values('北京歡迎你'); --插入5個字元也沒有問題，情況和之前明顯不同。
1 row created.
SQL> insert into test_char values('北京歡迎你啊'); --插入6個字元也沒有問題。
1 row created.
SQL> insert into test_char values('北京歡迎你哈哈');   
insert into test_char values('北京歡迎你哈')
                             *
ERROR at line 1:
ORA-12899: value too large for column "N1"."TEST_CHAR"."NAME" (actual: 8, maximum: 6)
然後我們為了對別插入兩組數字看看效果。
SQL> insert into test_char values('1234');
1 row created.
SQL> insert into test_char values(123456);
1 row created.
SQL> select name,dump(name) text from test_char;
NAME                     TEXT
------------------------ ----------------------------------------------------------------------------------------------------
北京                     Typ=1 Len=6: 229,140,151,228,186,172
北京歡迎你               Typ=1 Len=15: 229,140,151,228,186,172,230,172,162,232,191,142,228,189,160
北京歡迎你啊             Typ=1 Len=18: 229,140,151,228,186,172,230,172,162,232,191,142,228,189,160,229,149,138
1234                     Typ=1 Len=4: 49,50,51,52
123456                   Typ=1 Len=6: 49,50,51,52,53,54

這樣來檢視好像和最開始的情況沒有任何的變化。可以看到還是按照三個位元組來存放，但是代表的意義已經發生了變化。
在MySQL中的情況和在oracle 引數nls_length_semantics=char;的情況是類似的。
SQL>create table test(name varchar(10));
SQL>insert into test values('1234567890');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values('一二三四五六七八九十');
Query OK, 1 row affected (0.00 sec)
SQL>insert into test values(null,12345678901);
ERROR 1406 (22001): Data too long for column 'name' at row 1
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 | --最後這個地方直接給truncate掉了多餘的部分。
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

這一點在資料遷移的時候如果不注意就是很嚴重的問題。不知道MySQL中是否也有和oracle中類似的dump函式，但是我們可以通過類似相通的部分來互相印證。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1434762/，如需轉載，請註明出處，否則將追究法律責任。

相關文章