有一个使用了不同数据库的应，Oracle 和 PostgreSQL，数据库中的记录完全相同，相同的查询语句(相同的排序，至少从字面上来说是的)取到的记录排序却不同，从而产生了 Bug。 简单演示一下默认排序各自在这两种数据库中的行为, 比如说表中有两条记录，'VFORX' 和 'ibbVA'。此处不创建物理表。
Oracle(Linux)
select * from (
select 'VFORX' as value from dual
union
select 'ibbVA' from dual
) order by 1 desc;
返回的结果是
|
1 2 3 4 |
VALUE| -----+ ibbVA| VFORX| |
PostgreSQL(Linux)
|
1 2 3 4 5 |
select * from ( select 'VFORX' union select 'ibbVA' ) a(c1) order by c1 desc |
得到的不同的排序结果
|
1 2 3 4 |
c1 | -----+ VFORX| ibbVA| |
程序中只是简单的使用第一条记录的值，当然程序是有 Bug 的，这是外话。
能立即让它们行为一致的方式是保证排序后结果一致，在 PostgreSQL 中排序时指定 collate "POSIX" 或 "C"
|
1 2 3 4 5 |
select * from ( select 'VFORX' union select 'ibbVA' ) a(c1) order by c1 collate "POXIS" desc |
用 collate "C" 也能得到一样的效果
|
1 2 3 4 |
c1 | -----+ ibbVA| VFORX| |
问题是解决了，从中也领悟了 collate 在数据库以及排序中的作用。以前隐约记得在创建数据库时不仅要指定数据库使用的字符集，也就是存储所采用的字符集，还会指定一个叫做 collate 的东西，当时并未太在意它意味着什么，而碰到问题后才想起它的重要。
collate 是定义字符集排序规则的关键字，如区不区分大小写，或按照某种语言的自然顺序。现在也回想起为什么 MySQL 的 varchar 类型默认不区分大小写，字段的值是 'AaB', 用 where a = 'aab' 也能查询出来的缘故。
PostgreSQL 中如何指定 collate
|
1 2 3 |
SELECT datname, datcollate, datctype FROM pg_database WHERE datname = 'casdb'; |
看到
|
1 2 3 |
datname|datcollate |datctype | -------+-----------+-----------+ casdb |en_US.UTF-8|en_US.UTF-8| |
datcollate 也是 en_US.UTF-9, 我们可以在创建数据库时候指定 LC_COLLATE = 'C'
|
1 2 3 4 |
CREATE DATABASE mydb LC_COLLATE = 'C' LC_CTYPE = 'C' TEMPLATE = template0; |
执行上面语句时还必须指定 TEMPLATE = template0, 否则有错。数据库创建后再查看
|
1 2 3 |
SELECT datname, datcollate, datctype FROM pg_database WHERE datname = 'mydb' |
datcollate 和 datctype 就变成了
|
1 2 3 |
datname|datcollate|datctype| -------+----------+--------+ mydb |C |C | |
切换数据库到 mydb 后
|
1 2 3 4 5 |
select * from ( select 'VFORX' union select 'ibbVA' ) a(c1) order by c1 desc |
就变成了
|
1 2 3 4 |
c1 | -----+ VFORX| ibbVA| |
或者表的字段级指定 collate 类型
|
1 2 3 4 5 6 7 8 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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.
|
1 |
SELECT value FROM v$parameter WHERE name = 'max_string_size'; |
看到的是 STANDARD
需修改成 extended 才行
|
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 等决定的
|
1 |
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE parameter IN ('NLS_SORT', 'NLS_COMP'); |
|
1 2 3 4 |
Name |Value | ---------+--------+ PARAMETER|NLS_SORT| VALUE |BINARY | |
假如
|
1 2 3 |
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, 比较和排序时都不区分大小写
