数据库数据类型详解

# 数据库数据类型详解 ## 1. 字符数据类型相关 ### 1.1 VARCHAR2 存储限制 在数据库中，一个 4000 字符、实际 8000 字节长的字符串不能永久存储在 `VARCHAR2(4000 CHAR)` 字段中。在 PL/SQL 变量里，`VARCHAR2` 允许最大 32KB 的大小，所以能容纳该字符串；但存储在表中时，硬限制是 4000 字节。不过，我们可以成功存储 2000 个这样的字符，示例代码如下： ```sql EODA@ORA12CR1> declare 2 l_data varchar2(4000 char); 3 l_ch varchar2(1 char) := unistr( '\00d6' ); 4 begin 5 l_data := rpad( l_ch, 2000, l_ch ); 6 insert into t ( c ) values ( l_data ); 7 end; 8 / ``` 执行上述代码后，可通过以下查询查看存储的字符长度和字节长度： ```sql EODA@ORA12CR1> select length( c ), lengthb( c ) 2 from t 3 where c is not null; ``` 查询结果如下： | LENGTH(C) | LENGTHB(C) | | --------- | ---------- | | 2000 | 4000 | 从结果可知，这些字符占用了 4000 字节的存储空间。 ### 1.2 “N”变体数据类型（NVARCHAR2 和 NCHAR） `NVARCHAR2` 和 `NCHAR` 用于需要管理和存储多种字符集的系统。通常在以单字节固定宽度字符集（如 WE8MSWIN1252）为主，但又需要存储多字节数据的数据库中使用。很多系统既有遗留数据，又要为新应用支持多字节数据；还有些系统希望在大多数操作中使用单字节字符集以提高效率（对使用固定宽度字符的字符串进行操作比每个字符使用不同字节数的字符串更高效），但在某些时候又需要多字节数据的灵活性。 `NVARCHAR2` 和 `NCHAR` 与 `VARCHAR2` 和 `CHAR` 类似，但有以下区别： - 文本存储和管理使用数据库的国家字符集，而非默认字符集。 - 长度始终以字符为单位，而 `CHAR/VARCHAR2` 可以指定字节或字符。 在 Oracle 9i 及以上版本，数据库的国家字符集可以是 UTF8 或 AL16UTF16（9i 中为 UTF - 16，10g 中为 AL16UTF16），这使得 `NCHAR` 和 `NVARCHAR` 类型仅适用于存储多字节数据，这与早期版本不同（Oracle 8i 及更早版本允许为国家字符集选择任何字符集）。 ## 2. 二进制字符串数据类型（RAW 类型） ### 2.1 RAW 类型概述 Oracle 支持存储二进制数据和文本。二进制数据不受之前讨论的 `CHAR` 和 `VARCHAR2` 类型的字符集转换影响。因此，二进制数据类型不适合存储用户提供的文本，但适合存储加密信息（加密数据不是“文本”，而是原始文本的二进制表示）、包含二进制标记信息的文字处理文档等。任何不应被数据库视为“文本”（或其他基本数据类型，如数字、日期等）且不应进行字符集转换的字节串都应存储在二进制数据类型中。 Oracle 支持三种用于存储二进制数据的数据类型： - **RAW 类型**：适用于存储最大 2000 字节的 RAW 数据。从 12c 开始，可以配置 `RAW` 存储最多 32767 字节的信息。 - **BLOB 类型**：支持更大尺寸的二进制数据，后续会详细介绍。 - **LONG RAW 类型**：为向后兼容而支持，不建议用于新应用。 ### 2.2 RAW 类型语法和使用 `RAW` 类型的语法很简单： ```sql RAW( <size> ) ``` 例如，以下代码创建了一个每行能够存储 16 字节二进制信息的表： ```sql EODA@ORA12CR1> create table t ( raw_data raw(16) ); ``` `RAW` 类型在磁盘存储方面与 `VARCHAR2` 类型类似，是可变长度的二进制字符串。例如，刚创建的表 `T` 可以存储 0 到 16 字节的二进制数据，不像 `CHAR` 类型那样进行填充。 当处理 `RAW` 数据时，它可能会被隐式转换为 `VARCHAR2` 类型。例如，很多工具（如 SQL*Plus）不会直接显示 `RAW` 数据，而是将其转换为十六进制格式显示。以下示例使用 `SYS_GUID()` 函数在表中创建一些二进制数据： ```sql EODA@ORA12CR1> insert into t values ( sys_guid() ); EODA@ORA12CR1> select * from t; ``` 查询结果如下： ``` RAW_DATA -------------------------------- EEF18AA30B563AF0E043B7D04F0A4A30 ``` 这里需要注意两点： - `RAW` 数据看起来像字符串，这只是 SQL*Plus 检索和打印的方式，并非其在磁盘上的存储方式。因为二进制数据可能包含控制字符（如回车、换行或会使终端发出蜂鸣声的 Ctrl - G 字符），SQL*Plus 不能在屏幕上直接打印任意二进制数据。 - `RAW` 数据看起来比 16 字节大，实际上在这个例子中可以看到 32 个字符。这是因为每个二进制字节需要两个十六进制字符来显示（如果前导字符是零，则不显示零）。可以使用 Oracle SQL `DUMP` 函数查看存储的 `RAW` 数据实际长度： ```sql EODA@ORA12CR1> select dump(raw_data,16) from t; ``` 查询结果如下： ``` DUMP(RAW_DATA,16) ------------------------------------------------------------------------------- Typ=23 Len=16: ee,f1,8a,a3,b,56,3a,f0,e0,43,b7,d0,4f,a,4a,30 ``` 从结果可知，该二进制字符串实际长度为 16 字节。 ### 2.3 RAW 类型的隐式和显式转换 隐式转换也会反向进行，例如以下插入操作： ```sql EODA@ORA12CR1> insert into t values ( 'abcdef' ); ``` 这并不是插入字符串 `abcdef`，而是插入一个 3 字节的 `RAW` 数据，其字节为 `AB`、`CD`、`EF`，十进制表示为 171、205、239。如果尝试使用不包含有效十六进制字符的字符串，会收到错误消息： ```sql EODA@ORA12CR1> insert into t values ( 'abcdefgh' ); ``` 错误信息如下： ``` insert into t values ( 'abcdefgh' ) * ERROR at line 1: ORA-01465: invalid hex number ``` `RAW` 类型可以被索引并用于谓词，功能与其他数据类型一样。但要注意避免不必要的隐式转换，建议在所有情况下使用显式转换，可使用以下内置函数： - `HEXTORAW`：将十六进制字符字符串转换为 `RAW` 类型。 - `RAWTOHEX`：将 `RAW` 字符串转换为十六进制字符串。 以下是显式转换的示例： ```sql EODA@ORA12CR1> select rawtohex(raw_data) from t; EODA@ORA12CR1> insert into t values ( hextoraw('abcdef') ); ``` ## 3. 扩展数据类型 ### 3.1 扩展数据类型概述 在 Oracle 12c 之前，`VARCHAR2` 和 `NVARCHAR2` 数据类型的最大长度为 4000 字节，`RAW` 数据类型为 2000 字节。从 Oracle 12c 开始，这些数据类型可以配置为存储最多 32767 字节。 ### 3.2 启用扩展数据类型的步骤 对于非容器、单实例数据库，启用扩展数据类型的步骤如下（必须以 `SYS` 用户执行）： 1. 立即关闭数据库： ```sql SYS@O12CE> shutdown immediate; ``` 2. 以升级模式启动数据库： ```sql SYS@O12CE> startup upgrade; ``` 3. 设置系统参数： ```sql SYS@O12CE> alter system set max_string_size=extended; ``` 4. 执行脚本： ```sql SYS@O12CE> @?/rdbms/admin/utl32k.sql ``` 5. 再次立即关闭数据库： ```sql SYS@O12CE> shutdown immediate; ``` 6. 正常启动数据库： ```sql SYS@O12CE> startup; ``` 需要注意的是，一旦将 `MAX_STRING_SIZE` 修改为 `EXTENDED`，就不能再将其改回默认值（`STANDARD`）。如果需要切换回去，必须恢复到更改之前的时间点，这意味着需要 RMAN 备份（在更改之前进行的备份）或启用闪回数据库。也可以从启用了扩展数据类型的数据库进行数据泵导出，然后导入到未启用扩展数据类型的数据库，但包含扩展列的表在导入时会失败。 ### 3.3 使用扩展数据类型 启用扩展数据类型后，可以创建包含扩展列的表，示例代码如下： ```sql EODA@O12CE> create table t(et varchar2(32727)) tablespace users; ``` 可以使用 `desc` 命令查看表结构： ```sql EODA@O12CE> desc t ``` 结果如下： | Name | Null? | Type | | ---- | ---- | ---- | | ET | | VARCHAR2(32727) | 可以像操作非扩展列一样通过 SQL 操作扩展的 `VARCHAR2` 列，示例如下： ```sql EODA@O12CE> insert into t values(rpad('abc',10000,'abc')); EODA@O12CE> select substr(et,9500,10) from t where UPPER(et) like 'ABC%'; ``` ### 3.4 扩展数据类型的内部实现 扩展数据类型在内部实现为 LOB。假设 `T` 表在不包含任何其他对象的模式中创建，可以通过以下查询查看相关对象： ```sql EODA@O12CE> select object_name, object_type from user_objects; ``` 查询结果如下： | OBJECT_NAME |