SQL Developer 用空格填充 char 中的剩余空间

Question

我有一个数据类型为 char(256) 的属性。我通过 SQL Developer 从 csv 文件导入值当属性获得一个 10 个字符的值时，剩余的空间被空格填充。

我知道 char 是静态分配空间的，但这是否也意味着我得到了一个格式为“abc”的字符串？

因为这使得具有相等运算符的 sql 语句变得困难。

Answer 1

您是在误解下经营的；它与 SQL Developer 无关。

CHAR 数据类型是固定长度的字符串；如果您没有提供完整长度的字符串，那么 Oracle 将用空格 (ASCII 32) 字符填充该字符串，直到它具有正确的长度。

来自 documentation :

CHAR Datatype

The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000 bytes for the CHAR column width. The default is 1 byte. Oracle then guarantees that:

• When you insert or update a row in the table, the value for the CHAR column has the fixed length.
• If you give a shorter value, then the value is blank-padded to the fixed length.
• If a value is too large, Oracle Database returns an error.

Oracle Database compares CHAR values using blank-padded comparison semantics.

要解决此问题，请不要将 CHAR 用于可变长度字符串，而应使用 VARCHAR2。

VARCHAR2 and VARCHAR Datatypes

The VARCHAR2 datatype stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, Oracle Database stores each value in the column as a variable-length field unless a value exceeds the column's maximum length, in which case Oracle Database returns an error. Using VARCHAR2 and VARCHAR saves on space used by the table.