Oracle数据库字符数据类型全解析

### Oracle 数据库字符数据类型全解析 #### 1. 字符和二进制字符串类型概述 在 Oracle 数据库中，常见的字符数据类型有 CHAR、VARCHAR2 及其 “N” 变体（NCHAR 和 NVARCHAR2）。其中，CHAR 和 NCHAR 最多可存储 2000 字节的文本，而 VARCHAR2 和 NVARCHAR2 最多能存储 4000 字节的信息。不过从 Oracle 12c 开始，VARCHAR2、NVARCHAR2 和 RAW 数据类型可配置为最多存储 32767 字节的信息，但扩展数据类型默认是未启用的，所以若未明确配置，VARCHAR2 和 NVARCHAR2 数据类型的最大大小仍为 4000 字节，RAW 为 2000 字节。 数据库会根据需要在不同的字符集之间转换文本。字符集是用位和字节对单个字符的二进制表示，常见的字符集有： - US7ASCII 字符集：是 ASCII 标准的 128 个字符表示，使用字节的低 7 位来表示这些字符。 - WE8MSWIN1252 字符集：是西欧字符集，能表示 128 个 ASCII 字符以及 128 个扩展字符，使用字节的所有 8 位。 #### 2. NLS 概述 NLS 即国家语言支持（National Language Support），是数据库非常强大但常被低估的功能。它控制着数据的多个方面，如数据排序方式、数字中逗号和小数点的显示方式等。其最重要的功能有两个： - 文本数据在磁盘上的持久编码。 - 数据在不同字符集之间的透明转换。 例如，数据库以 WE8MSWIN1252 字符集存储 8 位数据，但有些客户端使用 US7ASCII 这样的 7 位字符集连接。客户端无法处理 8 位数据，所以数据库的数据需要转换。若未意识到这种转换，可能会发现数据随时间丢失字符，因为 US7ASCII 中没有的字符会被转换为其他字符，这是字符集转换导致的。字符集转换通常会改变数据，是一种有损转换，因为不可能表示所有字符，但为了让客户端应用程序能处理数据，这种转换又必须进行。 下面通过一个具体示例展示字符集转换： ```sql -- 查看数据库字符集 EODA@ORA12CR1> select * 2 from nls_database_parameters 3 where parameter = 'NLS_CHARACTERSET'; PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_CHARACTERSET WE8MSWIN1252 -- 确保 NLS_LANG 与数据库字符集相同 EODA@ORA12CR1> host echo $NLS_LANG AMERICAN_AMERICA.WE8MSWIN1252 -- 创建表并插入 8 位数据 EODA@ORA12CR1> create table t ( data varchar2(1) ); Table created. EODA@ORA12CR1> insert into t values ( chr(224) ); 1 row created. EODA@ORA12CR1> insert into t values ( chr(225) ); 1 row created. EODA@ORA12CR1> insert into t values ( chr(226) ); 1 row created. EODA@ORA12CR1> select data, dump(data) dump from t; D DUMP - -------------------- à Typ=1 Len=1: 224 á Typ=1 Len=1: 225 â Typ=1 Len=1: 226 EODA@ORA12CR1> commit; ``` 若在另一个窗口指定 7 位 ASCII 客户端，会得到不同结果： ```sql $ export NLS_LANG=AMERICAN_AMERICA.US7ASCII $ sqlplus eoda Enter password: EODA@ORA12CR1> select data, dump(data) dump from t; D DUMP - -------------------- a Typ=1 Len=1: 224 a Typ=1 Len=1: 225 a Typ=1 Len=1: 226 ``` 可以看到，在 7 位会话中，客户端接收到的是没有变音符号的字母 “a”，但 DUMP 函数显示数据库中实际上是三个不同的字符。若客户端将数据检索到主机变量中，再原样插回数据库，会发现原始字符丢失，被 7 位的 “a” 替代。 #### 3. SQL DUMP 函数 Oracle SQL 的 DUMP 函数可用于显示数据值的数据类型代码、字节长度和内部表示（还可选择显示字符集名称），其语法为： ```sql DUMP( expression [,return_format] [,start_position] [,length] ) ``` 默认的 return_format 是 10（十进制），还可以是 8（八进制）、16（十六进制）、17（单字符）、1008（带字符集名称的八进制）、1010（带字符集名称的十进制）、1016（带字符集名称的十六进制）或 1017（带字符集名称的单字符）。示例如下： ```sql EODA@ORA12CR1> select dump('a'), dump('a',8), dump('a',16) from dual; DUMP('A') DUMP('A',8) DUMP('A',16) ---------------- ----------------- ---------------- Typ=96 Len=1: 97 Typ=96 Len=1: 141 Typ=96 Len=1: 61 ``` 这里 97、141 和 61 分别是字符 “a” 在十进制、八进制和十六进制表示中的 ASCII 码，返回的数据类型代码 Typ = 96 表示 CHAR 数据类型。 不同工具受字符集转换的影响不同。例如，使用推荐的 Data Pump 导出/导入过程时，导出总是在包含数据的数据库的字符集下进行，与客户端的 NLS 设置无关；导入时会将文件中的数据从源数据库的字符集转换为目标数据库的字符集，虽然也可能发生字符集转换，但与传统的 EXP/IMP 工具不同。 #### 4. 字符字符串类型 Oracle 中有四种基本的字符字符串类型：CHAR、VARCHAR2、NCHAR 和 NVARCHAR2。所有字符串在 Oracle 中以相同格式存储，在数据库块上，它们前面有 1 到 3 字节的长度字段，后面跟着数据；当字符串为 NULL 时，