为什么'2'> '10'？ [英] Why is ' 2' > '10'?

问题描述

为什么'2'的初始空格比'10'大？

Why is ' 2' with an initial space bigger than '10'?

select ' 2' > '10';
 ?column? 
----------
 t
(1 row)

我同时使用了latin1和utf8英语排序规则：

I tried it with both latin1 and utf8 english collations:

                                    List of databases
   Name    |   Owner    | Encoding  |    Collation     |      Ctype       |   Access privileges   
-----------+------------+-----------+------------------+------------------+-----------------------
 cpn       | cpn        | UTF8      | en_US.UTF-8      | en_US.UTF-8      | 
 teste     | cpn        | LATIN1    | en_US.ISO-8859-1 | en_US.ISO-8859-1 | 

我知道它与类型有关，因为在强制转换时，它可以按预期工作：

I know it has to do with the type because when it is cast it works as expected:

teste=> select ' 2'::char > '10';
 ?column? 
----------
 f
(1 row)

这里到底发生了什么？

编辑：

以上所有操作都是在Fedora 13中使用8.4.8完成的。但是我只是在Centos 6中使用9.04进行了测试，结果相同：

All the above was done with 8.4.8 in Fedora 13. But I just tested with 9.04 in Centos 6 with the same result:

select ' 2' > '10';
 ?column? 
----------
 t
(1 row)

数据库列表

   Name    |   Owner    | Encoding  |  Collation  |    Ctype    |   Access privileges   
-----------+------------+-----------+-------------+-------------+-----------------------
 cpn       | postgres   | UTF8      | en_US.UTF-8 | en_US.UTF-8 | 

新编辑：

这是为了进一步造成混淆：

This is to further confuse:

select ' ' > '1';
 ?column? 
----------
 f
(1 row)

推荐答案

我认为PostgreSQL自动尝试找出幕后类型，在Linux中它试图摆脱''，其中一些比较也基于语言环境

I think PostgreSQL automatically tries to figure out the type behind the scenes and in Linux it tries to get rid of the ' ', some of the comparisons are also based on locale.

• 因此，'2'>。 '10'变为'2'>>'10'并且比较为'2'>>'1' ;它们不相等，因此无需继续字符串的其余部分，并且 ascii（'2'）大于 ascii（'1 '），因此计算结果为true。

• Thus, ' 2' > '10' becomes '2'>'10' and the comparison is '2'>'1'; they are not equal, so no need to continue with the rest of the string, and ascii('2') is greater than ascii('1'), so it evaluates to true.

如果它是一个相等运算（例如'22'= '22'）由于Postgres会逐字节进行比较，因此结果为false。这很重要，因为引擎在进行比较时会使用两种不同的算法。

If it were an equality operation (e.g. ' 22' = '22 ') it would result to false because Postgres does a byte by byte comparison. This is important because the engine uses two different algorithms when doing comparisons.

如果您通过类型转换指定类型，则不会覆盖空间规则（ ''=>''）。

If you specify the type via typecasting, then it won't override the space rules (' '=>'').

也要归功于： RhodiumToad和Peerce在#postgresql

Also credit goes to: RhodiumToad and Peerce in #postgresql

这篇关于为什么'2'> '10'？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！