oracle 12.2+支持mysql与postgresql中的collate（排序规则）特性

　　sql server, mysql, postgresql都支持针对字符串类型定义排序规则的概念（collate），一般来说，排序规则分为三种：基于二进制，是否区分大小写，是否区分重音以及特定编码格式（典型的就是汉字在GBK和UTF-8中的编码值不同，会导致排序结果也不同）。

例如sql server中：

SELECT * FROM MyTable
WHERE MyField = 'BobDillon' COLLATE Latin1_General_CI_AI

oracle可以使用：

SELECT *
FROM MyTable
WHERE NLSSORT(MyField, 'NLS_SORT = Latin_CI') = NLSSORT('BobDillon', 'NLS_SORT = Latin_CI')

postgresql中：

CREATE TABLE test1 (
    a text COLLATE "de_DE",
    b text COLLATE "es_ES",
    ...
);
SELECT a < ('foo' COLLATE "fr_FR") FROM test1;

https://www.postgresql.org/docs/13/collation.html

mysql中，可以在创建表的时候指定默认字符集以及排序规则，如下：

-- abc.abc definition

CREATE TABLE `abc` (
  `Id` int DEFAULT NULL,
  `Name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='测试DDL';

select name from(
select '王五' as name 
union
select '张三' as name 
union
select '李四' as name 
union
select '赵六' as name )a
order by name asc;    -- 使用utf-8二进制排序

select name from(
select '王五' as name 
union
select '张三' as name 
union
select '李四' as name 
union
select '赵六' as name )a
order by convert(name using gbk) asc;   -- 使用GBK排序

oracle 12.2开始支持collate的概念，使用也类似：

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

INSERT INTO t1 VALUES (1, 'Löwenbrauerei');
INSERT INTO t1 VALUES (2, 'LÖwenbrauerei');
INSERT INTO t1 VALUES (3, 'Lowenbrauerei');
INSERT INTO t1 VALUES (4, 'LOwenbrauerei');
COMMIT;


ALTER TABLE t1 ADD (
  location  VARCHAR2(15 CHAR) COLLATE BINARY_AI
);


UPDATE t1 SET location = 'Bräunlingen' WHERE id = 1;
UPDATE t1 SET location = 'BrÄunlingen' WHERE id = 2;
UPDATE t1 SET location = 'Braunlingen' WHERE id = 3;
UPDATE t1 SET location = 'BrAunlingen' WHERE id = 4;
COMMIT;


SELECT *
FROM   t1
WHERE  location LIKE '%ä%';

    ID COMPANY       LOCATION
---------- --------------- ---------------
         1 Löwenbrauerei   Bräunlingen
         2 LÖwenbrauerei   BrÄunlingen
     3 Lowenbrauerei   Braunlingen
     4 LOwenbrauerei   BrAunlingen


-- 也支持表级别默认的COLLATE
CREATE TABLE t1 (
  id          NUMBER,
  company     VARCHAR2(15 CHAR),
  CONSTRAINT t1_pk PRIMARY KEY (id)
)
DEFAULT COLLATION BINARY_CI;

-- schema级别也支持默认的COLLATE
CREATE USER test2 IDENTIFIED BY test2
  DEFAULT TABLESPACE users
  QUOTA UNLIMITED ON users
  DEFAULT COLLATION BINARY_CI;

-- 还支持语句级别、会话级别

注：在PG内核实现中，collate是个编译时特性，和Domain、数据类型转换的实现逻辑类似，会被具体的函数重载（如GBK编码），在运行时并没有collate的概念。

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/refrn/ALL_TAB_COLS.html#GUID-85036F42-140A-406B-BE11-0AC49A00DBA3

https://blog.csdn.net/weixin_29944865/article/details/114773194 mysql排序规则

https://wenku.baidu.com/view/f1203fc81ae8b8f67c1cfad6195f312b3169eb9b.html mysql支持GBK排序

https://www.cnblogs.com/zhenren001/p/16578813.html、https://blog.csdn.net/qq_32935175/article/details/116002048 pg支持GBK排序

mysql字符集、collate排序规则、GBK各种bin/general/unicode排序规则：https://zhuanlan.zhihu.com/p/147207165、https://www.cnblogs.com/mydriverc/p/8308929.html、https://www.jianshu.com/p/de575dc67f4c、https://new.qq.com/rain/a/20210322A0BB3L00、https://ishare.ifeng.com/c/s/7pHoh3cgGpP

posted @ 2021-08-31 09:02 zhjh256 阅读(937) 评论(0) 编辑收藏举报

刷新页面返回顶部

oracle 12.2+支持mysql与postgresql中的collate（排序规则）特性

公告