VARCHAR2 类型和 NVARCHAR2 类型的转换限制及绕过方法

本文介绍通过 VARCHAR2 类型和 NVARCHAR2 类型的转换限制及相关的手工绕过方法。

详细说明

NVARCHAR2 是 OceanBase 数据库 Oracle 模式/原生 Oracle 中的一种基本字符数据类型，OceanBase 数据库 MySQL 模式/原生 MySQL 无此类型

NVARCHAR2 是 UNICODE 字符数据类型。

-- 定义语法
NVARCHAR2(size)

其中：

注意： OceanBase 数据库 Oracle 模式/原生 Oracle 中只有 NVARCHAR2 类型，没有 NVARCHAR 类型。

• OceanBase 数据库 V4.3.5 BP3 版本 Oracle 模式中。

  $ 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 版本中。

  [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 模式中。

  $ 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.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  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 版本中。

  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.
  
  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  
  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 模式中。

  $ 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)
  
  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 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 版本中则是支持的。

  [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));
  
  Table created.
  
  SQL> alter table test modify a nvarchar2(40);
  
  Table altered.
  
  SQL> alter table test modify d varchar2(40);
  
  Table altered.
  
  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;。

示例如下：

$ 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)

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 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 数据库所有版本。