PostgreSQL DBA(113) - pgAdmin(Don't do this：Don't use char(n))

no zuo no die系列，来自于pg的wiki。
这一节的内容是：不要使用char(n) 。
理由是:

Any string you insert into a char(n) field will be padded with spaces to the declared width. That’s probably not what you actually want.
The manual says:
Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way. However, 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. Note that trailing spaces are semantically significant in character varying and text values, and when using pattern matching, that is LIKE and regular expressions.
That should scare you off it.
The space-padding does waste space, but doesn’t make operations on it any faster; in fact the reverse, thanks to the need to strip spaces in many contexts.
It’s important to note that from a storage point of view char(n) is not a fixed-width type. The actual number of bytes varies since characters may take more than one byte, and the stored values are therefore treated as variable-length anyway (even though the space padding is included in the storage).

原因是期望指定n长，但由于字符编码（如中文字符，GB2312是2个字节，而UTF8是3个字节）的原因，实际跟预想的不符，而且会出现影响排序等其他副作用。

testdb=# drop table if exists t_char;
DROP TABLE
testdb=# create table t_char(id int,c1 char(10),c2 varchar(10));
CREATE TABLE
testdb=# 
testdb=# insert into t_char values(1,'测试123','123123');
INSERT 0 1
testdb=# insert into t_char values(2,'abc123','123123');
INSERT 0 1
testdb=# 
testdb=# insert into t_char values(3,'a','a ');
INSERT 0 1
testdb=# insert into t_char values(4,E'a\n',E'a\n');
INSERT 0 1
testdb=#

使用length函数获取长度

testdb=# select id,length(c1),length(c2) from t_char order by id;
 id | length | length 
----+--------+--------
  1 |      5 |      6
  2 |      6 |      6
  3 |      1 |      2
  4 |      2 |      2
(4 rows)

如上所述，使用length函数获取的实际是字符个数而不是实际的字节数，如“测试123”实际的字节数是9+5=14字节。

testdb=# select id,length(c1),octet_length(c1),length(c2),octet_length(c2) from t_char order by id;
 id | length | octet_length | length | octet_length 
----+--------+--------------+--------+--------------
  1 |      5 |           14 |      6 |            6
  2 |      6 |           10 |      6 |            6
  3 |      1 |           10 |      2 |            2
  4 |      2 |           10 |      2 |            2
(4 rows)

在字符串比较上面，虽然空格的ascii码值(0x20)比’\n’(0x0a)要大，但查询的实际效果看起来却是char(10)定义的’a’比’a\n’要小:

testdb=# select E'a\n'::bytea;
 bytea  
--------
 \x610a
(1 row)
testdb=# select E'a '::bytea;
 bytea  
--------
 \x6120
(1 row)
testdb=# select * from t_char where c1 < E'a\n';
 id |     c1     | c2 
----+------------+----
  3 | a          | a 
(1 row)

参考资料
Don’t Do This

PostgreSQL DBA(113) - pgAdmin(Don't do this：Don't use char(n))

相关推荐