sql - 使用varchar类型代替char类型可以避免哪些比较问题？

Question

来自数据库系统概念

When comparing two values of type char, if they are of different lengths extra spaces are automatically added to the shorter one to make them the same size, before comparison.

When comparing a char type with a varchar type, one may expect extra spaces to be added to the varchar type to make the lengths equal, before comparison; however, this may or may not be done, depending on the database system. As a result, even if the same value “Avi” is stored in the attributes A and B above, a comparison A=B may return false.

We recommend you always use the varchar type instead of the char type to avoid these problems.

您能举一些例子来解释比较两个 char 类型的值，以及比较两个 varchar 类型的值吗？比较中使用了什么运算符，=？

使用varchar类型代替char类型可以避免什么问题？为什么？

这是关于一般的 SQL，我想它也适用于 PostgreSQL，因为它与 SQL 标准很好地兼容。

谢谢。

Answer 1

基本问题是char将用空格填充值，这可能会导致一些令人惊讶和不一致的结果。

在这里我们看到 Postgres 保留了尾随空格。

test=> create table foo ( c char(10), v varchar(10) );
CREATE TABLE

test=> insert into foo values ('foo', 'foo');
INSERT 0 1

test=> select * from foo;
     c      |  v  
------------+-----
 foo        | foo

test=> select concat(c, '>'), concat(v, '>') from foo where c = 'foo     ';
   concat    | concat 
-------------+--------
 foo       > | foo>

但 MySQL 不会，除非 PAD_CHAR_TO_FULL_LENGTH已设置。

mysql> create table foo ( c char(10), v varchar(10) );

mysql> insert into foo values ('foo', 'foo');

mysql> select * from foo;
+------+------+
| c    | v    |
+------+------+
| foo  | foo  |
+------+------+

mysql> select concat(c, '>'), concat(v, '>') from foo where c = 'foo     ';
+----------------+----------------+
| concat(c, '>') | concat(v, '>') |
+----------------+----------------+
| foo>           | foo>           |
+----------------+----------------+

mysql> set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

mysql> select concat(c, '>'), concat(v, '>') from foo where c = 'foo     ';
+----------------+----------------+
| concat(c, '>') | concat(v, '>') |
+----------------+----------------+
| foo       >    | foo>           |
+----------------+----------------+

The PostgreSQL documentation概述了几个问题。

Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way.

...trailing spaces are treated as semantically insignificant and disregarded when comparing two values of type character. In collations where whitespace is significant, this behavior can produce unexpected results; for example SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) returns true, even though C locale would consider a space to be greater than a newline

Trailing spaces are removed when converting a character value to one of the other string types.

存储引擎已经改进，因此几乎没有理由使用 char不再。

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying should be used instead.

一个案例 char 可能合理的做法是存储非常小的、固定大小的字符串。例如，ISO 2 character country codes可能存储为 char(2) .但是在如此小的字符串上不太可能注意到性能差异。

char最好避免头痛。