MySQL和Oracle字节,通过oracle类比MySQL中的字节字符问题

在几个月前写过一篇博文 MySQL数据类型 http://blog.itpub.net/23718752/viewspace-1371434/

当时写完以后有同事朋友就提出了一些疑问，对于汉字在MySQL和Oracle中的存放情况希望我能够详细的说说。

关于MySQL中的varchar字符类型，自己的操作都是基于字符集UTF-8。

对于存放汉字，涉及到字符，字节，编码的一些知识，我查了一下，自己先补补，发现有一个帖子已经描述的很详细了。直接引用过来。

从编码的发展来看，大致可以分为三个阶段。系统内码  说明                          系统

阶段一     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函数，但是我们可以通过类似相通的部分来互相印证。