oracle 不同数据库的 Varchar 和 Varchar2 的最大大小/长度

Max size/ length of Varchar and Varchar2 for different databases

提问by user1716079

Is the size limit of VARCHAR and VARCHAR2 different across the various SQL RDBMS implementations or is it the same? Thanks for help.

VARCHAR 和 VARCHAR2 的大小限制在各种 SQL RDBMS 实现中是不同的还是相同的？感谢帮助。

回答by jsist

First of all varcharis used in MySQL and varchar2is used in Oracle. Moreover, size and limits of varchar and varchar2 differs across databases.

首先varchar是在MySQLvarchar2中使用，在Oracle中使用。此外，varchar 和 varchar2 的大小和限制因数据库而异。

Reference from: http://docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm

参考自：http: //docs.oracle.com/cd/E12151_01/doc.150/e12155/oracle_mysql_compared.htm

MySQL and Oracle have some differences in the character types that they support and in the way they store and retrieve the character type values.

MySQL supports the CHAR and VARCHAR type for character type with a length that is less than 65,535 bytes. The CHAR type can have a maximum length of 255 bytes, and as of MySQL 3.23 it may also be declared with a length of 0 byte. Before MySQL 5.0.3, the length specification for the VARCHAR type is the same as the CHAR type. From MySQL 5.0.3 on, the maximum length for the VARCHAR type is 65,535 bytes. Oracle supports four character types: CHAR, NCHAR, NVARCHAR2 and VARCHAR2. The minimum length that can be declared for all Oracle character types is 1 byte. The maximum size allowed for CHAR and NCHAR is 2,000 bytes, and for NVARCHAR2 and VARCHAR2 it is 4,000 bytes.

MySQL CHAR values are right-padded with spaces to the specified length when they are stored, and trailing spaces are removed when the values are retrieved. On the other hand, VARCHAR values are stored using as many characters as are given, but before MySQL 5.0.3 trailing spaces are removed when the values are stored and retrieved. Oracle blank-pads the value for its CHAR and NCHAR type to the column length if the value is shorter than the column length, and trailing spaces are not removed on retrieval. For NVARCHAR2 and VARVHAR2 data type columns, Oracle stores and retrieves the value exactly as is given, including trailing spaces.

If a value is assigned to a character type column that exceeds its specified length, MySQL truncates the value and does not generate an error unless the STRICT SQL mode is set. Oracle generates an error if the value assigned to a character type column exceeds its specified length.

In MySQL, every character type (CHAR, VARCHAR, and TEXT) column has a column character set and collation. If the character set or collation is not explicitly defined in the column definition, the table character set or collation is implied if specified; otherwise, the database character or collation is chosen. In Oracle, the character set for CHAR and VARCHAR2 types is defined by the database character set, and for the character set for NCHAR and NVARCHAR types is defined the national character set.

When declaring a CHAR or VARCHAR type in MySQL, the default length semantics is characters instead of bytes for MySQL 4.1 and later. In Oracle, the default length semantics is bytes for CHAR and VARCHAR2 types and characters for NCHAR and NVARCHAR2 types.

SQL Developer will map MySQL CHAR and VARCHAR types to Oracle CHAR and VARCHAR2 types, respectively. SQL Developer will determine the maximum number of bytes for the Oracle CHAR and VARCHAR2 data type columns from the number of bytes required to hold the maximum length specified for the corresponding MySQL CHAR and VARCHAR data type columns. If the MySQL VARCHAR2 column is such that the data exceeds 4000 bytes, convert the column to an Oracle CLOB data type column.

MySQL 和 Oracle 在支持的字符类型以及存储和检索字符类型值的方式上存在一些差异。

对于长度小于 65,535 字节的字符类型，MySQL 支持 CHAR 和 VARCHAR 类型。CHAR 类型的最大长度为 255 字节，从 MySQL 3.23 开始，它也可以声明为长度为 0 字节。在 MySQL 5.0.3 之前，VARCHAR 类型的长度规范与 CHAR 类型相同。从 MySQL 5.0.3 开始，VARCHAR 类型的最大长度为 65,535 字节。Oracle 支持四种字符类型：CHAR、NCHAR、NVARCHAR2 和 VARCHAR2。可为所有 Oracle 字符类型声明的最小长度为 1 个字节。CHAR 和 NCHAR 允许的最大大小为 2,000 字节，而 NVARCHAR2 和 VARCHAR2 允许的最大大小为 4,000 字节。

MySQL CHAR 值在存储时用空格填充到指定的长度，并在检索值时删除尾随空格。另一方面，VARCHAR 值使用给定的字符存储，但在 MySQL 5.0.3 之前，当存储和检索值时删除尾随空格。如果值短于列长度，Oracle 会将其 CHAR 和 NCHAR 类型的值填充为列长度，并且在检索时不会删除尾随空格。对于 NVARCHAR2 和 VARVHAR2 数据类型列，Oracle 完全按照给定的方式存储和检索值，包括尾随空格。

如果给字符类型列赋值的值超过其指定长度，MySQL 会截断该值并且不会产生错误，除非设置了 STRICT SQL 模式。如果分配给字符类型列的值超过其指定长度，则 Oracle 会生成错误。

在 MySQL 中，每个字符类型（CHAR、VARCHAR 和 TEXT）列都有一个列字符集和排序规则。如果字符集或排序规则未在列定义中明确定义，则如果指定，则隐含表字符集或排序规则；否则，选择数据库字符或排序规则。在 Oracle 中，CHAR 和 VARCHAR2 类型的字符集由数据库字符集定义，NCHAR 和 NVARCHAR 类型的字符集由国家字符集定义。

在 MySQL 中声明 CHAR 或 VARCHAR 类型时，默认长度语义是字符而不是 MySQL 4.1 及更高版本的字节。在 Oracle 中，CHAR 和 VARCHAR2 类型的默认长度语义是字节，NCHAR 和 NVARCHAR2 类型的默认长度语义是字符。

SQL Developer 将分别将 MySQL CHAR 和 VARCHAR 类型映射到 Oracle CHAR 和 VARCHAR2 类型。SQL Developer 将根据保存为相应 MySQL CHAR 和 VARCHAR 数据类型列指定的最大长度所需的字节数来确定 Oracle CHAR 和 VARCHAR2 数据类型列的最大字节数。如果 MySQL VARCHAR2 列的数据超过 4000 字节，则将该列转换为 Oracle CLOB 数据类型列。