Oracle, PostgreSQL 字符串排序不一致及调整

calendar Nov 1, 2024   ---   · 2 min read · database Oracle PostgreSQL  ·
Share on: linkedin copy
有一个使用了不同数据库的应，Oracle 和 PostgreSQL，数据库中的记录完全相同，相同的查询语句(相同的排序，至少从字面上来说是的)取到的记录排序却不同，从而产生了 Bug。 简单演示一下默认排序各自在这两种数据库中的行为, 比如说表中有两条记录，'VFORX' 和 'ibbVA'。此处不创建物理表。

Oracle(Linux)
select * from (
    select 'VFORX' as value from dual
        union
    select 'ibbVA' from dual
) order by 1 desc;
返回的结果是
1VALUE|
2-----+
3ibbVA|
4VFORX|
PostgreSQL(Linux)
1select * from (
2    select 'VFORX'
3    union
4    select 'ibbVA'
5) a(c1) order by c1 desc

得到的不同的排序结果
1c1   |
2-----+
3VFORX|
4ibbVA|

程序中只是简单的使用第一条记录的值，当然程序是有 Bug 的，这是外话。

能立即让它们行为一致的方式是保证排序后结果一致，在 PostgreSQL 中排序时指定 collate "POSIX" 或 "C"
1select * from (
2    select 'VFORX'
3    union
4    select 'ibbVA'
5) a(c1) order by c1 collate "POXIS" desc

用 collate "C" 也能得到一样的效果
1c1   |
2-----+
3ibbVA|
4VFORX|

问题是解决了，从中也领悟了 collate 在数据库以及排序中的作用。以前隐约记得在创建数据库时不仅要指定数据库使用的字符集，也就是存储所采用的字符集，还会指定一个叫做 collate 的东西，当时并未太在意它意味着什么，而碰到问题后才想起它的重要。

collate 是定义字符集排序规则的关键字，如区不区分大小写，或按照某种语言的自然顺序。现在也回想起为什么 MySQL 的 varchar 类型默认不区分大小写，字段的值是 'AaB', 用 where a = 'aab' 也能查询出来的缘故。

PostgreSQL 中如何指定 collate

1SELECT datname, datcollate, datctype
2FROM pg_database
3WHERE datname = 'casdb';

看到
1datname|datcollate |datctype   |
2-------+-----------+-----------+
3casdb  |en_US.UTF-8|en_US.UTF-8|

datcollate 也是 en_US.UTF-9, 我们可以在创建数据库时候指定 LC_COLLATE = 'C'
1CREATE DATABASE mydb
2    LC_COLLATE = 'C'
3    LC_CTYPE = 'C'
4    TEMPLATE = template0;

执行上面语句时还必须指定 TEMPLATE = template0, 否则有错。数据库创建后再查看
1SELECT datname, datcollate, datctype
2FROM pg_database
3WHERE datname = 'mydb'

datcollate 和 datctype 就变成了
1datname|datcollate|datctype|
2-------+----------+--------+
3mydb   |C         |C       |

切换数据库到 mydb 后
1select * from (
2    select 'VFORX'
3    union
4    select 'ibbVA'
5) a(c1) order by c1 desc

就变成了
1c1   |
2-----+
3VFORX|
4ibbVA|

或者表的字段级指定 collate 类型
1CREATE TABLE my_table (
2    id serial PRIMARY KEY,
3    name VARCHAR(100) COLLATE "C"
4);
5
6
7ALTER TABLE my_table
8    ALTER COLUMN name TYPE VARCHAR(100) COLLATE "C";

Oracle 数据库如何指琮 collate

Oracle 12.2 也开始支持 Schema, 表，列级别的 collate
 1CREATE TABLE t1 (
 2  id          NUMBER,
 3  company     VARCHAR2(15 CHAR) COLLATE BINARY_CI,
 4  CONSTRAINT t1_pk PRIMARY KEY (id)
 5);
 6
 7CREATE TABLE t1 (
 8  id          NUMBER,
 9  company     VARCHAR2(15 CHAR),
10  CONSTRAINT t1_pk PRIMARY KEY (id)
11)
12DEFAULT COLLATION BINARY_CI;
13
14CREATE USER test2 IDENTIFIED BY test2
15  DEFAULT TABLESPACE users
16  QUOTA UNLIMITED ON users
17  DEFAULT COLLATION BINARY_CI;

执行上面的语句大概率会出现如下错语

SQL Error [43929] [99999]: ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set.

1SELECT value FROM v$parameter WHERE name = 'max_string_size';

看到的是 STANDARD

需修改成 extended 才行
1ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;

再重启数据库
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
Oracle 通常默认的 collate 是 BINARY, 由 NLS_COMP 和 NLS_SORT, NLS_LANGUAGE, NLS_TERRITORY 等决定的
1SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter IN ('NLS_SORT', 'NLS_COMP');
1Name     |Value   |
2---------+--------+
3PARAMETER|NLS_SORT|
4VALUE    |BINARY  |

假如
1SELECT table_name, column_name, collation
2FROM user_tab_columns 
3WHERE table_name = 'YOUR_TABLE_NAME';

返回 NULL 的话就是用了默认的 collation.

Oracle 的 collate 可应用到 VARCHAR2, CHAR, CLOB, 可选值有
  1. COLLATE BINARY:  基于二进制，排序最快，大小写敏感
  2. COLLATE BINARY_CI: Case Insensitive, 比较时(如 >, <, =) 时大小写不敏感，但排序时区分大小写
  3. COLLATE BINARY_AI: Accent Insensitive, 比较和排序时都不区分大小写
永久链接 https://yanbin.blog/oracle-postgresql-string-sorting-different-consistent/, 来自 隔叶黄莺 Yanbin's Blog
[版权声明] 本文采用 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 进行许可。