Oracle, PostgreSQL 字符串排序不一致及调整 | 隔叶黄莺 Yanbin Blog

2024-11-01 | 阅读(69)

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

Oracle(Linux)

select * from (
select 'VFORX' as value from dual
union
select 'ibbVA' from dual
) order by 1 desc;

返回的结果是

VALUE|
-----+
ibbVA|
VFORX|

VALUE|

-----+

ibbVA|

VFORX|

PostgreSQL(Linux)

select * from (
    select 'VFORX'
    union
    select 'ibbVA'
) a(c1) order by c1 desc

select * from (

select 'VFORX'

union

select 'ibbVA'

) a(c1) order by c1 desc

得到的不同的排序结果

c1   |
-----+
VFORX|
ibbVA|

c1 |

-----+

VFORX|

ibbVA|

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

能立即让它们行为一致的方式是保证排序后结果一致，在 PostgreSQL 中排序时指定 collate "POSIX" 或 "C"

select * from (
    select 'VFORX'
    union
    select 'ibbVA'
) a(c1) order by c1 collate "POXIS" desc

select * from (

select 'VFORX'

union

select 'ibbVA'

) a(c1) order by c1 collate "POXIS" desc

用 collate "C" 也能得到一样的效果

c1   |
-----+
ibbVA|
VFORX|

c1 |

-----+

ibbVA|

VFORX|

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

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

PostgreSQL 中如何指定 collate

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

SELECT datname, datcollate, datctype

FROM pg_database

WHERE datname = 'casdb';

看到

datname|datcollate |datctype   |
-------+-----------+-----------+
casdb  |en_US.UTF-8|en_US.UTF-8|

datname|datcollate |datctype |

-------+-----------+-----------+

casdb |en_US.UTF-8|en_US.UTF-8|

datcollate 也是 en_US.UTF-9, 我们可以在创建数据库时候指定 LC_COLLATE = 'C'

CREATE DATABASE mydb
    LC_COLLATE = 'C'
    LC_CTYPE = 'C'
    TEMPLATE = template0;

CREATE DATABASE mydb

LC_COLLATE = 'C'

LC_CTYPE = 'C'

TEMPLATE = template0;

执行上面语句时还必须指定 TEMPLATE = template0, 否则有错。数据库创建后再查看

SELECT datname, datcollate, datctype
FROM pg_database
WHERE datname = 'mydb'

SELECT datname, datcollate, datctype

FROM pg_database

WHERE datname = 'mydb'

datcollate 和 datctype 就变成了

datname|datcollate|datctype|
-------+----------+--------+
mydb   |C         |C       |

datname|datcollate|datctype|

-------+----------+--------+

mydb |C |C |

切换数据库到 mydb 后

select * from (
    select 'VFORX'
    union
    select 'ibbVA'
) a(c1) order by c1 desc

select * from (

select 'VFORX'

union

select 'ibbVA'

) a(c1) order by c1 desc

就变成了

c1   |
-----+
VFORX|
ibbVA|

c1 |

-----+

VFORX|

ibbVA|

或者表的字段级指定 collate 类型

CREATE TABLE my_table (
    id serial PRIMARY KEY,
    name VARCHAR(100) COLLATE "C"
);


ALTER TABLE my_table
    ALTER COLUMN name TYPE VARCHAR(100) COLLATE "C";

CREATE TABLE my_table (

id serial PRIMARY KEY,

name VARCHAR(100) COLLATE "C"

);

ALTER TABLE my_table

ALTER COLUMN name TYPE VARCHAR(100) COLLATE "C";

Oracle 数据库如何指琮 collate

Oracle 12.2 也开始支持 Schema, 表，列级别的 collate

CREATE TABLE t1 (
  id          NUMBER,
  company     VARCHAR2(15 CHAR) COLLATE BINARY_CI,
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE TABLE t1 (
  id          NUMBER,
  company     VARCHAR2(15 CHAR),
  CONSTRAINT t1_pk PRIMARY KEY (id)
)
DEFAULT COLLATION BINARY_CI;

CREATE USER test2 IDENTIFIED BY test2
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users
  DEFAULT COLLATION BINARY_CI;

CREATE TABLE t1 (

id NUMBER,

company VARCHAR2(15 CHAR) COLLATE BINARY_CI,

CONSTRAINT t1_pk PRIMARY KEY (id)

);

CREATE TABLE t1 (

id NUMBER,

company VARCHAR2(15 CHAR),

CONSTRAINT t1_pk PRIMARY KEY (id)

)

DEFAULT COLLATION BINARY_CI;

CREATE USER test2 IDENTIFIED BY test2

DEFAULT TABLESPACE users

QUOTA UNLIMITED ON users

DEFAULT COLLATION BINARY_CI;

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

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

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

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

看到的是 STANDARD

需修改成 extended 才行

ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;

1	ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;

再重启数据库

SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;

Oracle 通常默认的 collate 是 BINARY, 由 NLS_COMP 和 NLS_SORT, NLS_LANGUAGE, NLS_TERRITORY 等决定的

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter IN ('NLS_SORT', 'NLS_COMP');

1	SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter IN ('NLS_SORT', 'NLS_COMP');

Name     |Value   |
---------+--------+
PARAMETER|NLS_SORT|
VALUE    |BINARY  |

Name |Value |

---------+--------+

PARAMETER|NLS_SORT|

VALUE |BINARY |

假如

SELECT table_name, column_name, collation 
FROM user_tab_columns 
WHERE table_name = 'YOUR_TABLE_NAME';

SELECT table_name, column_name, collation

FROM user_tab_columns

WHERE table_name = 'YOUR_TABLE_NAME';

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

Oracle 的 collate 可应用到 VARCHAR2, CHAR, CLOB, 可选值有

COLLATE BINARY: 基于二进制，排序最快，大小写敏感
COLLATE BINARY_CI: Case Insensitive, 比较时(如 >, <, =) 时大小写不敏感，但排序时区分大小写
COLLATE BINARY_AI: Accent Insensitive, 比较和排序时都不区分大小写

本文链接 https://yanbin.blog/oracle-postgresql-string-sorting-different-consistent/, 来自隔叶黄莺 Yanbin Blog

best coffee on SciPy 最优化之最小化I wanted to take a moment to commend you on the outstanding quality of...
seetimee on 体验 Python FastAPI 的并发能力及线, 进程模型感谢
Yanbin on Mockito 3.4.0 开始可 Mock 静态方法有一个补救，新写了一篇 https://yanbin.blog/mockito-mock-static-method-in-multiple...
Yanbin on 升级到 Spring Boot 3 后 javax.inject.Named 不可用怎么，被抄袭了！算是被机器翻译引用的？
oleksi on 升级到 Spring Boot 3 后 javax.inject.Named 不可用https://www.springcloud.io/post/2023-02/springboot-3-javax-inject-name...

M	T	W	T	F	S	S
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31