问题[collation](dba)

我发现下面的查询在运行相同兼容性级别、相同设置选项等的两个 Azure 超大规模数据库上返回不同的结果。

IF CAST(ServerProperty('Edition') AS nvarchar(128)) = 'SQL Azure' BEGIN
    SELECT 1
END
IF ServerProperty('Edition') = 'SQL Azure' BEGIN
    SELECT 2
END
IF 'SQL Azure' = ServerProperty('Edition') BEGIN
    SELECT 3
END

在一个数据库上，它只返回 1，在另一个数据库上它返回 1,2 和 3。

我调查了根本原因，它似乎是由数据库的不同排序规则引起的。

对于以下查询：

SELECT SQL_VARIANT_PROPERTY(ServerProperty('Edition'), 'Collation')
SELECT name, collation_name, compatibility_level FROM sys.databases

只返回一行的数据库，结果是：

-----------------------------
SQL_Latin1_General_CP1_CI_AS

name         collation_name                   compatibility_level
------------ -------------------------------- -------------------
master       SQL_Latin1_General_CP1_CI_AS     140
my_database  SQL_Latin1_General_CP850_CI_AS   150

数据库返回 1,2,3 的结果是：

-----------------------------
SQL_Latin1_General_CP1_CI_AS

name         collation_name                   compatibility_level
------------ -------------------------------- -------------------
master       SQL_Latin1_General_CP1_CI_AS     140
my_database  SQL_Latin1_General_CP1_CI_AS     150

因此，没有强制转换的简单比较是sql_variant与varchar（当我使用时没有区别N'SQL Azure'）比较，其中底层nvarchar的sql_variant排序规则在一种情况下与我查询的数据库不同，在另一种情况下它是匹配的。

首先，我假设比较具有不同排序规则的两个字符串会失败，就像当您尝试加入具有不同排序规则的两列时它会失败一样，但这里显然不是这种情况。

无论如何，安全比较可能sql_variant与 a的函数的输出的最佳方法是varchar什么？

一个LIKE子句可以测试一个字符串是否出现在另一个字符串中，并且该CHARINDEX函数可以给出第一个匹配项的开始位置。

就我而言，我对结束位置感兴趣，因为排序规则的复杂性，不能从开始位置推导出来。例如，在德语归类 ( German_PhoneBook_100_CI_AS_SC_UTF8) 中，

• hä出现在位置 1 的“Häger”中并在位置 2 处结束，并且
• hä出现在位置 1 的 'Haeger' 中，并在位置 3 结束。

这样做的问题是为用户的利益标记搜索结果文本的匹配部分。

我一直在考虑反转字符串，但是我仍然只能得到第一个匹配项，CHARINDEX在这种反转的情况下，我需要最后一个匹配项。

有什么想法吗？

出于演示目的，我有一个示例表，其中有一varchar列名为numberstring：

-4
32
0
4
16
8
-8
1024

在选择 usingORDER BY numberstring时，我发现不同的用户得到不同的结果。

一些得到：

-4, -8, 0, 1024, 16, 32, 4, 8

而有些人得到：

0, 1024, 16, 32, -4, 4, -8, 8

不知何故，第二个结果似乎忽略了减号，即使数据以其他方式排序为字符串数据。

据我所知，底层服务器是 Microsoft SQL Server express 的标准安装，没有任何特殊选项。该版本是最近几年的版本，但使用相同版本的用户仍然报告不同的结果。

设置中是否存在影响结果的内容，如何从 SSMS 中查看？

据我所知，数据库服务器和 SSMS 是在澳大利亚设置的，只有默认值。数据类型是varchar(max)。

我正在尝试为 PostgreSQL 13 中的新数据库设置排序规则，但它似乎没有生效：

postgres=# CREATE DATABASE assets ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'en_US.UTF-8';
CREATE DATABASE
postgres=# \l
                                      List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 assets    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

如何让新数据库反映我的排序规则更改？

好吧，我有一个 MariaDB 数据库，我刚刚注意到一些奇怪的存储问题，如下所示：

• 我的JSON列（在 MariaDB 中是LONGTEXT）有一个自动设置的排序规则utf8mb4_bin。我刚刚注意到这实际上弄乱了我所有的撇号，将它们存储为'，也存储é为\u00e9等。

• 其他一些列包含法语/西班牙语/葡萄牙语等字母，并且é确实é显示。它使用的排序规则是utf8mb4_unicode_ci.

• 另一列包含可能包含 HTML 标记的字符串，utf8mb4_unicode_ci并且标记没有任何问题。但是，由于某种原因，它转义了单引号，因此它存储'为\'，必须避免。

所以我的问题是，在 MariaDB 中，您最好使用哪种排序规则来避免在存储 HTML 标记、特殊字符如 é、ö、ä、è 以及不转义撇号时遇到任何问题？

更新

实际上，我真的不明白为什么会这样：

• 在我的示例数据库表中，我有两列，A 和 B。表的字符集是utf8mb4，并且没有一列有专门分配的字符集。

• 该表使用默认排序规则utf8mb4_unicode_ci

• A 列使用排序规则utf8mb4_unicode_ci

• B 列使用了排序规则utf8mb4_bin

• A 列正确地将字母存储为é、ä等。

• B 列使用它们的 unicode 存储它们，例如\u00e9for é。

我现在使用以下方法更改了 B 列的排序规则：

ALTER TABLE sample_table MODIFY COLUMN column_b LONGTEXT COLLATE utf8mb4_unicode_ci.

因此，A 列和 B 列现在使用完全相同的字符集 + 排序规则。插入column_b的数据始终是JSON_OBJECT.

尽管如此，无论更改如何，仅在 B 列中而不是在 A 列中，字母 likeé仍以其 unicode 编码格式存储，撇号也存储为'. 查询数据时，任何特殊字母 like 都会é被正确检索（如果您查询数据持有 sth like l\u00e9ger，则正确得到léger）。

但是，如果你查询 sth like l'\u00e9l\u00e9phant，你不会得到l'éléphant, 但是l'éléphant。

我在 PHP 中获取数据，并确保连接的字符集也是utf8mb4, 使用mysqli_set_charset( $connection, 'utf8mb4' );.

我知道理论上我可以简单地对检索到的数据进行搜索和替换；但是为什么这种撇号转换还在发生呢？

更新 2

找到了'问题的解决方案（请参阅我发布的答案），但现在我正试图找出解决另一个提到的问题的方法：当存储"Hello, I'm James"在LONGTEXT数据字段中时，MariaDB 存储Hello, I\'m James（它转义了其中的单引号，猜出于安全原因）。目前，当我检索数据时，我得到

Hello, I\'m James

但我想得到

Hello, I'm James

即使数据存储为

Hello, I\'m James

当然，您可以再次在 PHP 中进行搜索替换，但我只是觉得 MariaDB 中必须有一个标准方法，在数据库方面，为此..？

更新 3

感谢@Rick James 的提示，在这种情况下，在数据库服务器端不应该发生数据字符集转换，我一直在检查我的服务器端代码，确实发现了关于 unicode 字符问题的第一个问题（\u00e9而不是é, ETC。）。原因是，在将 JSON 插入我的数据库之前，我已经使用json_encode从 PHP 数组转换为 JSON 字符串。问题是\uXXXX默认情况下这会转义我的 unicode 字符，在这种情况下不应该这样做。为了避免这种情况，而不是：

json_encode( $data )

利用：

json_encode( $data, JSON_UNESCAPED_UNICODE )

这样就解决了与 unicode 编码错误相关的所有问题。关于奇怪的撇号编码到 '/转义到的\'问题仍未解决。

更新 4

好的，也找到了问题的根源'，\'它们是由同一个问题引起的。这是因为我使用以下方法清理了用于插入的字符串数据：

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING
);

代替：

filter_var(
  $my_string,
  FILTER_SANITIZE_STRING,
  FILTER_FLAG_NO_ENCODE_QUOTES
);

当您回显字符串时，这从未显示'编码，可能是因为它在回显时转换回单引号，但这只是一个假设。是的，做同样的事情\'而不是'存储也解决了这个问题。所以我想就是这样。

在 PG 13 文档中，有几个用于特殊目的的 ICU 归类示例。还提到存在允许创建排序规则以忽略重音符号的 ICU 语言环境，并且可以在https://github.com/unicode-org/cldr/blob/master/common/bcp47/collat​​ion.xml上找到它们

但是，在阅读了该文档之后，我仍然不清楚应该使用什么语言环境来创建 ICU 排序规则，以便在西班牙语中进行不区分重音的比较。

这种 ICU 语言环境的名称是什么？有没有 ICU 西班牙语排序规则的列表？

我有一个应用程序，每个用户帐户都可以设置他们首选的应用程序语言。我将这些语言首选项存储为IETF BCP47 语言标签（例如“de”、“en-US”）。现在，对于该用户的查询，我想根据他们的语言偏好对结果进行排序。

想象一下，我在一个带有 utf8mb4_general_ci 排序规则的表中拥有数据 AAA、TTT、OOO、ÖÖÖ。

如果我想让它们为德国用户排序，我可以这样做

SELECT word FROM `words` order by word collate utf8mb4_german2_ci

这会给我命令

AAA, ÖÖÖ, OOO, TTT

然而，对于冰岛用户，使用utf8mb4_icelandic_ci我会正确得到的排序规则

AAA、OOO、TTT、ÖÖÖ

现在，在我的应用程序中，我不为该用户保存utf8mb4_icelandic_cior ，但我保存or 。utf8mb4_german2_ciisde-DE

有没有办法可以将此is或de-DEBCP47 语言标签直接发送到 MariaDB 以指定排序规则而不是排序规则名称？或者我是否必须在我的应用程序中的语言标记和排序规则名称之间进行映射才能为用户发送正确的排序规则信息？

越南语整理中的“tR”似乎有一些特别之处。了解它的人是否可以简单地解释一下。这个问题是在我们的产品安装在“越南语”整理的 SQL Server 上时发现的。架构中的一个表的名称中包含“tR”，但存储过程正在引用所有小写“tr”的表。这个参考失败了。

我猜这种情况类似于其他排序规则中的'阝'匹配'ss'。

这是一个复制品：

select  case when 'tr' = 'tR' COLLATE SQL_Latin1_General_CP1_CI_AS   then 'match' else 'no match' end 
select  case when 'tr' = 'tR' COLLATE Vietnamese_CI_AI   then 'match' else 'no match' end 
select  case when 'tr' = 'TR' COLLATE Vietnamese_CI_AI   then 'match' else 'no match' end 

结果：

-----
match


--------
no match


-----
match

第二个 T-SQL 产生不匹配。't' 和 'R' 的其他组合没有。

从 SQL Server 2019 开始，它支持 UTF-8 作为排序规则。但是，根据以下查询：

SELECT COLLATIONPROPERTY('Arabic_100_CS_AS_KS_WS_SC_UTF8', 'CodePage')
SELECT COLLATIONPROPERTY('Latin1_General_100_CS_AS_KS_WS_SC_UTF8', 'CodePage');

65001两者都返回Windows 中的 Unicode代码页。此外，所有新的_UTF8排序规则都使用代码页65001：

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE '%_UTF8';

Arabic_100_CS_AS_KS_WS_SC_UTF8using和Latin1_General_100_CS_AS_KS_WS_SC_UTF8as collat​​ion之间有什么区别吗？

我的 select case when 语句有问题，我想在 select case when 语句中添加一个新列，我得到了结果，但是?????因为我已将该列设置为阿拉伯语单词，所以我尝试将内容转换为不幸nvarchar(55)的是，我得到了相同的结果。

我怎样才能得到正确的结果？

SELECT case when ( CAST(o.startTime as time(7)) > cast(Start as time(7))) 
          then  cast('قدوم' as nvarchar(55))
          else cast('رجوع' as nvarchar(55)) end as stat,
       userId, FirstName
from   Users, TimeTable