印刻数据库

外观

Oracle 字符集配置 ​

字符集基础知识 ​

字符集概念 ​

  1. 什么是字符集

    • 字符集是一组字符的集合，以及每个字符的编码方式
    • 它定义了如何将字符转换为二进制数据存储在数据库中，以及如何将二进制数据转换回字符显示

  2. 编码方案

    • 单字节编码：每个字符使用一个字节（8位）表示，如 ASCII、ISO-8859-1
    • 多字节编码：每个字符使用多个字节表示，如 UTF-8、GB2312、GBK
    • 变长编码：不同字符使用不同长度的字节表示，如 UTF-8

  3. 字符集组成

    • 字符集名称：如 AL32UTF8、ZHS16GBK 等
    • 编码方案：如何将字符映射到二进制值
    • 排序规则：字符的比较和排序方式

NLS 参数 ​

  1. NLS 概述

    • NLS (National Language Support) 是 Oracle 提供的国际化支持功能
    • 允许数据库存储、处理和检索不同语言的数据
    • 提供日期、时间、数字和货币等的本地化格式

  2. 关键 NLS 参数

    • NLS_CHARACTERSET：数据库字符集，用于存储 CHAR、VARCHAR2、CLOB 等数据类型
    • NLS_NCHAR_CHARACTERSET：国家字符集，用于存储 NCHAR、NVARCHAR2、NCLOB 等数据类型
    • NLS_LANGUAGE：会话语言，影响错误消息、日期格式等
    • NLS_TERRITORY：地区设置，影响数字格式、货币符号等
    • NLS_DATE_FORMAT：日期显示格式
    • NLS_NUMERIC_CHARACTERS：数字格式（小数点和千位分隔符）

  3. 参数级别

    • 数据库级别：在数据库创建时设置，影响整个数据库
    • 实例级别：在参数文件中设置，影响整个实例
    • 会话级别：通过 ALTER SESSION 设置，仅影响当前会话
    • 语句级别：在 SQL 语句中设置，仅影响当前语句

Oracle 支持的字符集 ​

数据库字符集 ​

  1. 常用字符集

    • AL32UTF8：Oracle 推荐的 Unicode 字符集，支持所有语言
    • ZHS16GBK：简体中文字符集，支持 GBK 编码
    • ZHS16CGB231280：简体中文字符集，支持 GB2312 编码
    • WE8ISO8859P1：西欧字符集，支持英语及大多数西欧语言
    • WE8MSWIN1252：Windows 西欧字符集
    • JA16EUC：日语 EUC 字符集
    • KO16MSWIN949：韩语 Windows 字符集

  2. Unicode 字符集

    • AL32UTF8：UTF-8 编码，变长多字节字符集
    • UTF8：旧版 UTF-8 编码，有一些限制
    • AL16UTF16：UTF-16 编码，固定长度双字节字符集

  3. 字符集兼容性

    • 超集/子集关系：某些字符集是其他字符集的超集，如 AL32UTF8 是大多数字符集的超集
    • 二进制兼容性：某些字符集在二进制表示上兼容，如 WE8ISO8859P1 和 US7ASCII

国家字符集 ​

  1. 支持的国家字符集

    • AL16UTF16：UTF-16 编码，固定长度双字节字符集
    • UTF8：UTF-8 编码，变长多字节字符集

  2. 国家字符集用途

    • 用于存储需要支持多种语言的数据
    • 提供与数据库字符集无关的 Unicode 支持
    • 适用于存储多语言应用的数据

字符集选择策略 ​

数据库设计阶段 ​

  1. 考虑因素

    • 应用程序语言需求：需要支持哪些语言
    • 数据存储需求：字符集对存储大小的影响
    • 性能考虑：某些字符集处理速度更快
    • 兼容性考虑：与其他系统的集成需求
    • 未来扩展性：是否需要支持更多语言

  2. 推荐字符集

    • 国际应用：AL32UTF8（Unicode）
    • 仅简体中文：ZHS16GBK 或 AL32UTF8
    • 多语言环境：AL32UTF8
    • 兼容性要求：根据现有系统选择兼容字符集

  3. 国家字符集选择

    • 建议使用 AL16UTF16，提供最佳的 Unicode 支持
    • 对于存储大量 ASCII 字符的场景，UTF8 可能更节省空间

迁移场景 ​

  1. 从非 Unicode 到 Unicode

    • 优点：支持更多语言，国际化能力强
    • 缺点：可能增加存储需求
    • 迁移方法：使用 Oracle 数据泵或其他迁移工具

  2. 字符集兼容性检查

    • 使用 CSALTER 脚本检查字符集兼容性
    • 使用 DMU (Database Migration Assistant for Unicode) 工具进行迁移

  3. 迁移注意事项

    • 充分测试迁移过程
    • 备份原数据库
    • 考虑应用程序兼容性
    • 评估存储影响

字符集配置方法 ​

数据库创建时配置 ​

  1. 使用 DBCA 创建数据库

    • 在 "字符集" 步骤中选择合适的字符集
    • 设置 "数据库字符集" 和 "国家字符集"
    • 可选择 "使用 Unicode (AL32UTF8)" 或 "从字符集列表中选择"

  2. 使用 CREATE DATABASE 语句

    • 指定 CHARACTER SET 和 NATIONAL CHARACTER SET 参数
    • 示例：
      sql
      CREATE DATABASE orcl
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
...
;

  3. 使用 RMAN 复制数据库

    • 可选择在复制过程中更改字符集
    • 需要确保目标字符集是源字符集的超集

实例级配置 ​

  1. 修改 NLS 参数

    • 在参数文件中设置 NLS 参数
    • 示例：
      txt
      nls_language=AMERICAN
nls_territory=AMERICA
nls_date_format=DD-MON-RR

  2. 动态修改参数

    • 使用 ALTER SYSTEM 命令修改实例级参数
    • 示例：
      sql
      ALTER SYSTEM SET nls_date_format = 'YYYY-MM-DD' SCOPE = spfile;

会话级配置 ​

  1. 修改会话参数

    • 使用 ALTER SESSION 命令修改会话级参数
    • 示例：
      sql
      ALTER SESSION SET nls_language = 'SIMPLIFIED CHINESE';
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

  2. 环境变量设置

    • 在客户端设置 NLS 环境变量
    • 示例（Linux）：
      bash
      export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
    • 示例（Windows）：
      cmd
      set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

  3. SQL*Plus 配置

    • 在 glogin.sql 文件中设置默认会话参数
    • 或在 SQL*Plus 会话中直接执行 ALTER SESSION 命令

字符集转换 ​

数据迁移中的字符集转换 ​

  1. 使用数据泵

    • 导出时使用源字符集
    • 导入时指定目标字符集
    • 示例：
      bash
      # 导出
expdp system/password DIRECTORY=dpump DUMPFILE=expdat.dmp FULL=Y

# 导入（转换字符集）
impdp system/password DIRECTORY=dpump DUMPFILE=expdat.dmp FULL=Y 
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

  2. 使用 SQL*Loader

    • 在控制文件中指定字符集
    • 示例：
      txt
      LOAD DATA
INFILE 'data.csv'
CHARACTERSET AL32UTF8
INTO TABLE employees
FIELDS TERMINATED BY ','
(...)

  3. 使用 CREATE TABLE AS SELECT

    • 在不同字符集的数据库之间复制数据
    • Oracle 会自动进行字符集转换

应用程序中的字符集转换 ​

  1. JDBC 连接

    • 在连接字符串中指定字符集
    • 示例：
      java
      String url = "jdbc:oracle:thin:@localhost:1521:orcl?characterEncoding=UTF-8";

  2. ODBC 连接

    • 在 ODBC 数据源配置中设置字符集
    • 或在连接字符串中指定

  3. PHP OCI8 连接

    • 使用 oci_connect 函数时设置字符集
    • 示例：
      php
      $conn = oci_connect('username', 'password', 'localhost/orcl', 'AL32UTF8');

字符集转换注意事项 ​

  1. 数据丢失风险

    • 从大字符集转换到小字符集时可能会丢失数据
    • 例如，从 AL32UTF8 转换到 ZHS16GBK 时，某些 Unicode 字符可能无法表示

  2. 性能影响

    • 频繁的字符集转换会影响性能
    • 尽量在应用程序和数据库之间使用相同的字符集

  3. 验证转换结果

    • 转换后验证数据的正确性
    • 特别注意特殊字符和多语言数据

字符集问题诊断 ​

常见字符集问题 ​

  1. 乱码问题

    • 症状：查询结果显示乱码或问号
    • 原因：客户端字符集与数据库字符集不匹配
    • 解决方法：确保客户端 NLS_LANG 设置与数据库字符集一致

  2. 字符截断

    • 症状：插入或更新数据时出现字符截断错误
    • 原因：目标列长度不足，或字符集转换导致字节长度增加
    • 解决方法：增加列长度，或使用合适的字符集

  3. Ora-12705 错误

    • 症状："ORA-12705: Cannot access NLS data files or invalid environment specified"
    • 原因：NLS_LANG 环境变量设置错误
    • 解决方法：正确设置 NLS_LANG 环境变量

  4. Ora-12899 错误

    • 症状："ORA-12899: value too large for column"
    • 原因：插入的数据在目标字符集中占用更多字节
    • 解决方法：增加列长度，或使用合适的字符集

诊断工具 ​

  1. 数据库字符集查询

    • 查询数据库字符集：
      sql
      SELECT * FROM nls_database_parameters 
WHERE parameter IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

  2. 实例字符集查询

    • 查询实例字符集：
      sql
      SELECT * FROM nls_instance_parameters 
WHERE parameter LIKE 'NLS%';

  3. 会话字符集查询

    • 查询会话字符集：
      sql
      SELECT * FROM nls_session_parameters 
WHERE parameter LIKE 'NLS%';

  4. 字符集转换测试

    • 测试字符在不同字符集中的表示：
      sql
      SELECT dump('测试', 1016) FROM dual;

  5. DMU 工具

    • Oracle Database Migration Assistant for Unicode
    • 用于评估和执行字符集迁移

字符集最佳实践 ​

数据库设计阶段 ​

  1. 选择合适的字符集

    • 优先选择 AL32UTF8 以支持国际化
    • 考虑未来业务扩展需求
    • 评估存储和性能影响

  2. 合理使用国家字符集

    • 对于需要支持多语言的数据，使用 NCHAR 数据类型
    • 国家字符集建议使用 AL16UTF16

  3. 统一字符集策略

    • 整个系统使用一致的字符集
    • 确保应用程序、数据库和客户端使用相同的字符集

应用程序开发 ​

  1. 设置正确的 NLS_LANG

    • 在客户端设置与数据库匹配的 NLS_LANG
    • 避免在应用程序中硬编码字符集

  2. 使用绑定变量

    • 减少 SQL 注入风险
    • 避免字符集转换问题

  3. 处理特殊字符

    • 测试应用程序对特殊字符的处理
    • 确保所有输入输出都经过正确的字符集处理

数据库维护 ​

  1. 定期检查字符集使用情况

    • 监控字符集相关错误
    • 检查数据完整性

  2. 备份字符集信息

    • 记录数据库和实例的字符集设置
    • 在备份和恢复文档中包含字符集信息

  3. 字符集迁移规划

    • 如需更改字符集，制定详细的迁移计划
    • 进行充分的测试

常见问题（FAQ） ​

Q1: 数据库创建后可以修改字符集吗？ ​

A1: 数据库创建后修改字符集比较复杂，建议在创建时选择正确的字符集。如果确实需要修改：

  • 从小字符集到大字符集：可以使用 ALTER DATABASE CHARACTER SET 命令，但需要确保新字符集是旧字符集的超集
  • 从大字符集到小字符集：不建议，可能会丢失数据
  • 推荐方法：使用数据泵导出/导入，或使用 DMU 工具进行迁移

Q2: AL32UTF8 和 UTF8 字符集有什么区别？ ​

A2: 主要区别：

  • AL32UTF8：Oracle 推荐的 UTF-8 实现，完全支持 Unicode 标准，支持补充字符
  • UTF8：Oracle 早期的 UTF-8 实现，有一些限制，不支持补充字符
  • 建议：使用 AL32UTF8 而非 UTF8

Q3: 如何解决客户端显示乱码问题？ ​

A3: 解决步骤：

  1. 检查数据库字符集：SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
  2. 检查客户端 NLS_LANG 设置：echo $NLS_LANG（Linux）或 echo %NLS_LANG%（Windows）
  3. 确保客户端 NLS_LANG 设置与数据库字符集一致
  4. 例如，如果数据库使用 AL32UTF8，客户端应设置：export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Q4: 国家字符集和数据库字符集有什么区别？ ​

A4: 区别：

  • 数据库字符集：用于存储 CHAR、VARCHAR2、CLOB 等数据类型
  • 国家字符集：用于存储 NCHAR、NVARCHAR2、NCLOB 等数据类型
  • 使用场景：国家字符集主要用于需要支持多语言的应用，提供与数据库字符集无关的 Unicode 支持

Q5: 字符集对性能有什么影响？ ​

A5: 影响：

  • 存储：UTF-8 等变长字符集可能需要更多存储空间
  • 查询性能：字符集转换会增加 CPU 开销
  • 索引大小：多字节字符集的索引可能更大
  • 建议：在性能和功能之间找到平衡，优先考虑业务需求

Q6: 如何在不同字符集的数据库之间迁移数据？ ​

A6: 迁移方法：

  1. 使用数据泵：Oracle 会自动处理字符集转换
  2. 使用 SQL*Loader：在控制文件中指定字符集
  3. 使用 GoldenGate：支持不同字符集之间的实时复制
  4. 注意事项：确保目标字符集能够表示源字符集的所有字符，避免数据丢失

Q7: 如何处理 Excel 导入导出时的字符集问题？ ​

A7: 处理方法：

  1. 导出到 CSV：使用 UTF-8 编码保存 CSV 文件
  2. 导入时：确保数据库字符集与 CSV 文件编码一致
  3. 使用 SQL*Loader：在控制文件中指定正确的字符集
  4. 使用外部表：在创建外部表时指定字符集

Q8: 如何测试字符集是否正确配置？ ​

A8: 测试方法：

  1. 插入多语言数据：
    sql
    INSERT INTO test_table VALUES ('测试', 'Test', '테스트', '試験');
  2. 查询数据：确保所有语言的字符都能正确显示
  3. 检查字符长度：
    sql
    SELECT LENGTH(col), LENGTHB(col) FROM test_table;
  4. 检查字符集转换：使用 DUMP 函数查看字符的二进制表示