mysql - order by 的 SQL 规范中的区分大小写 - oracle 与 mysql

Question

介绍

过去几年我一直在使用新的数据库，例如 mysql 和 mariaDB。

对于一个项目，多年后我现在使用 oracle，我惊讶地发现 Oracle order by 区分大小写...例如在 ASC 中，大写字符高于小写字符。

此行为是否符合 SQL 规范？一般的 SQL 语言规范是否概述了行为应该是什么，还是取决于每个供应商？

我问这个问题是因为多年使用 mysql/mariaDB 让我认为 order by 不区分大小写。

Mysql

例如，我在 mysql 的文档中看到:

On character type columns, sorting—like all other comparison operations—is normally performed in a case-insensitive fashion. This means that the order is undefined for columns that are identical except for their case. You can force a case-sensitive sort for a column by using BINARY like so: ORDER BY BINARY col_name.

发件人:http://dev.mysql.com/doc/refman/5.7/en/sorting-rows.html

Answer 1

来自 Oracle 数据库 SQL 语言引用，12c 第 1 版 (12.1):

When character values are compared linguistically for the ORDER BY clause, they are first transformed to collation keys and then compared like RAW values. The collation keys are generated either explicitly as specified in NLSSORT or implicitly using the same method that NLSSORT uses. Both explicitly and implicitly generated collation keys are subject to the same restrictions that are described in "NLSSORT" on page 7-207. As a result of these restrictions, two values may compare as linguistically equal if they do not differ in the prefix that was used to produce the collation key, even if they differ in the rest of the value.

希望这对您有所帮助!

祝你有美好的一天!