--- title: VARCHAR2 类型和 NVARCHAR2 类型的转换限制及绕过方法-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 VARCHAR2 类型和 NVARCHAR2 类型的转换限制及绕过方法相关的常见问题和使用技巧，帮助您快速解决 VARCHAR2 类型和 NVARCHAR2 类型的转换限制及绕过方法的难题。 image: https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*OSPzQ6GUQF4AAAAAQHAAAAgAeiGDAQ/original --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # VARCHAR2 类型和 NVARCHAR2 类型的转换限制及绕过方法 更新时间：2026-05-18 09:11 适用版本： V1.4.x、V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x 内容类型：TechNote 本文介绍通过 `VARCHAR2` 类型和 `NVARCHAR2` 类型的转换限制及相关的手工绕过方法。 ## 详细说明 ### `NVARCHAR2` 是 OceanBase 数据库 Oracle 模式/原生 Oracle 中的一种基本字符数据类型，OceanBase 数据库 MySQL 模式/原生 MySQL 无此类型 `NVARCHAR2` 是 `UNICODE` 字符数据类型。 ```shell -- 定义语法 NVARCHAR2(size) ``` 其中： | **参数** | **说明** | | --- | --- | | size | 表示列的长度，可变长度。 `size` 是字符数量，默认采用 CHAR 为计量单位，不可以手动指定其他单位。 `size` 个字符的总大小不能超过 32767 字节。 必须为 `NVARCHAR2` 指定大小，对于 AL16UTF16 编码，字节数最大为两倍，对于 UTF8 编码，字节数最大为三倍。字节数大小由国家字符集定义，上限为 32767 个字节。 | **注意：** OceanBase 数据库 Oracle 模式/原生 Oracle 中只有 `NVARCHAR2 类型`，没有 `NVARCHAR` 类型。 - OceanBase 数据库 V4.3.5 BP3 版本 Oracle 模式中。 ```shell $ obclient -hxx.xxx.80.111 -P2881 -usys@oraclet -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221521453 Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. obclient(SYS@oracle)[SYS]> create table t1 (id int, name nvarchar(100)); ORA-00900: You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'nvarchar(100))' at line 1 obclient(SYS@oracle)[SYS]> create table t1 (id int, name nvarchar2(100)); Query OK, 0 rows affected (0.130 sec) ``` - 原生 Oracle 数据库 19.3 版本中。 ```shell [oracle@xxxxxx ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 26 15:14:52 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> create table t1 (id int, name nvarchar(100)); create table t1 (id int, name nvarchar(100)) * ERROR at line 1: ORA-00907: missing right parenthesis SQL> create table t1 (id int, name nvarchar2(100)); Table created. ``` ### OceanBase 数据库 MySQL 模式/原生 MySQL 中有 NVARCHAR 类型，无 NVARCHAR2 类型 从 OceanBase 数据库 V3.2.4 BP4 版本 MySQL 模式开始支持 `NCHAR`/`NVARCHAR` 数据类型，但 OceanBase 数据库 MySQL 模式中一直不支持 `NVARCHAR2` 类型，因为 `NVARCHAR2` 是 OceanBase 数据库 Oracle 模式才有的，而 OceanBase 数据库 MySQL 模式中的 `NVARCHAR = VARCHAR CHARSET UTF8`。 - OceanBase 数据库 V4.3.5 BP3 版本 MySQL 模式中。 ```shell $ mysql -hxx.xxx.80.111 -P2881 -uroot@mysqlt -pxxx -A -c test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 3221549390 Server version: 5.7.25 OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. MySQL [test]> create table t1 (id int, name nvarchar2(100)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your OceanBase version for the right syntax to use near 'nvarchar2(100))' at line 1 MySQL [test]> create table t1 (id int, name nvarchar(100)); Query OK, 0 rows affected (0.09 sec) MySQL [test]> desc t1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(100) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) MySQL [test]> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(100) DEFAULT NULL ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) ``` - 原生 MySQL 数据库 8.1.0 版本中。 ```shell bash-4.4# mysql -h127.1 -uroot -P3306 -pxxx test mysql: [Warning] Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2607 Server version: 8.1.0 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. mysql> create table t1 (id int, name nvarchar2(100)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nvarchar2(100))' at line 1 mysql> create table t1 (id int, name nvarchar(100)); Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show warnings; +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 3720 | NATIONAL/NCHAR/NVARCHAR implies the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using CHAR(x) CHARACTER SET UTF8MB4 in order to be unambiguous. | +---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> desc t1; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(100) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> show create table t1; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` int DEFAULT NULL, `name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ``` ### OceanBase 数据库 Oracle 模式中 VARCHAR2 类型和 NVARCHAR2 类型不支持互转，原生 Oracle 则是支持的 OceanBase 数据库 Oracle 模式中的 `VARCHAR2` 和 `NVARCHAR2` 类型不支持互转，应该是 `VARCHAR2` 与 `NVARCHAR2` 使用的字符集不同，限制较多，转换时会报错。 - OceanBase 数据库 V4.2.5 BP3 版本 Oracle 模式中。 ```shell $ obclient -hxx.xxx.80.111 -P2881 -usys@oraclet -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221624987 Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) obclient(SYS@oracle)[SYS]> create table test(id int, a varchar2(20), b char(10), c nchar(10), d nvarchar2(10)); Query OK, 0 rows affected (0.172 sec) obclient(SYS@oracle)[SYS]> alter table test modify a nvarchar2(40); ORA-00600: internal error code, arguments: -4007, Can not increase precision or scale, src column type VARCHAR2,dst column type NVARCHAR2 not supported obclient(SYS@oracle)[SYS]> alter table test modify d varchar2(40); ORA-00600: internal error code, arguments: -4007, Can not increase precision or scale, src column type NVARCHAR2,dst column type VARCHAR2 not supported ``` - 原生 Oracle 数据库 11.2 版本中则是支持的。 ```shell [oracle@8cca4bd0a6de /]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Mon May 26 23:31:22 2025 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> create table test(id int, a varchar2(20), b char(10), c nchar(10), d nvarchar2(10)); SQL> alter table test modify a nvarchar2(40); Table altered. SQL> alter table test modify d varchar2(40); SQL> desc test; Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(38) A NVARCHAR2(40) B CHAR(10) C NCHAR(10) D VARCHAR2(40) ``` ### OceanBase 数据库 Oracle 模式中可以通过增删列以及重名了列的方式来手工实现 VARCHAR2 到 NVARCHAR2 的互转 以将 `VARCHAR2(100)` 转换成 `NVARCHAR2(100)` 为例： 1）表原始字段为 old_col，字段类型 `VARCHAR2`，`size` 为 100。 2）表增加新字段 new_col，字段类型 `NVARCHAR2`，`size` 为 100：`ALTER TABLE t1 ADD (new_col NVARCHAR2(100));`。 3）更新旧字段数据 old_col1 到新的字段 new_col：`UPDATE t1 SET new_col = old_col;`。 4）删除原字段 old_col：`ALTER TABLE t1 DROP COLUMN old_col;`。 5）更改新字段名称为原字段：`ALTER TABLE t1 RENAME COLUMN new_col TO old_col;`。 示例如下： ```shell $ obclient -hxx.xxx.80.111 -P2881 -usys@oraclet -pxxx -A -c Welcome to the OceanBase. Commands end with ; or \g. Your OceanBase connection id is 3221736009 Server version: OceanBase 4.2.5.3 (r103000142025033110-f5b88cd987f23383677f2eb53cf76ed38a48979b) (Built Mar 31 2025 11:04:29) obclient(SYS@oracle)[SYS]> create table t1 (id int primary key, old_col varchar2(100), c2 int, c3 date); Query OK, 0 rows affected (0.138 sec) obclient(SYS@oracle)[SYS]> desc t1; +---------+---------------+------+------+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +---------+---------------+------+------+---------+-------+ | ID | NUMBER(38) | NO | PRI | NULL | NULL | | OLD_COL | VARCHAR2(100) | YES | NULL | NULL | NULL | | C2 | NUMBER(38) | YES | NULL | NULL | NULL | | C3 | DATE | YES | NULL | NULL | NULL | +---------+---------------+------+------+---------+-------+ 4 rows in set (0.016 sec) obclient(SYS@oracle)[SYS]> alter table t1 add new_col nvarchar2(100); Query OK, 0 rows affected (0.186 sec) obclient(SYS@oracle)[SYS]> desc t1; +---------+----------------+------+------+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +---------+----------------+------+------+---------+-------+ | ID | NUMBER(38) | NO | PRI | NULL | NULL | | OLD_COL | VARCHAR2(100) | YES | NULL | NULL | NULL | | C2 | NUMBER(38) | YES | NULL | NULL | NULL | | C3 | DATE | YES | NULL | NULL | NULL | | NEW_COL | NVARCHAR2(100) | YES | NULL | NULL | NULL | +---------+----------------+------+------+---------+-------+ 5 rows in set (0.003 sec) obclient(SYS@oracle)[SYS]> update t1 set new_col = old_col; Query OK, 0 rows affected (0.004 sec) Rows matched: 0 Changed: 0 Warnings: 0 obclient(SYS@oracle)[SYS]> alter table t1 drop column old_col; Query OK, 0 rows affected (0.101 sec) obclient(SYS@oracle)[SYS]> desc t1; +---------+----------------+------+------+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +---------+----------------+------+------+---------+-------+ | ID | NUMBER(38) | NO | PRI | NULL | NULL | | C2 | NUMBER(38) | YES | NULL | NULL | NULL | | C3 | DATE | YES | NULL | NULL | NULL | | NEW_COL | NVARCHAR2(100) | YES | NULL | NULL | NULL | +---------+----------------+------+------+---------+-------+ 4 rows in set (0.003 sec) obclient(SYS@oracle)[SYS]> alter table t1 rename column new_col to old_col; Query OK, 0 rows affected (0.098 sec) obclient(SYS@oracle)[SYS]> desc t1; +---------+----------------+------+------+---------+-------+ | FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA | +---------+----------------+------+------+---------+-------+ | ID | NUMBER(38) | NO | PRI | NULL | NULL | | C2 | NUMBER(38) | YES | NULL | NULL | NULL | | C3 | DATE | YES | NULL | NULL | NULL | | OLD_COL | NVARCHAR2(100) | YES | NULL | NULL | NULL | +---------+----------------+------+------+---------+-------+ 4 rows in set (0.003 sec) obclient(SYS@oracle)[SYS]> show create table t1; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | TABLE | CREATE TABLE | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | T1 | CREATE TABLE "T1" ( "ID" NUMBER(*,0), "C2" NUMBER(*,0), "C3" DATE, "OLD_COL" NVARCHAR2(100), PRIMARY KEY ("ID") ) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.004 sec) ``` #### 注意 - OceanBase 数据库 V4.x 版本中末尾加列和 rename 列均为 online DDL。 - OceanBase 数据库 V4.x 版本中删列为 offline DDL，需要重整数据，耗时长短与数据量有关。 - OceanBase 数据库 Oracle 模式中 `VARCHAR2` 类型 `size` 的长度语义由租户变量 `NLS_LENGTH_SEMANTICS`（默认为 `BYTE`）决定，而 `NVARCHAR2` 类型 `size` 的长度语义为字符数量。因此将 `VARCHAR2` 类型和 `NVARCHAR2` 类型进行互转时，需要仔细设计好目标列类型的 `size` 大小，避免出现目标列长度不够导致字符被截断的报错。 ## 适用版本 OceanBase 数据库所有版本。 Previous [OceanBase 数据库预留关键字相关问题总结](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003187661) Next [OBClient - source 导入数据报错：ERROR 1366 (HY000): Incorrect string value](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001146738) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线