含非日文字符时SQL日语排序异常问题及解决方案咨询

你遇到的这个现象是SQL Server的非日语排序规则对混合字符（日语假名+数字）的排序逻辑不符合日语预期导致的，具体细节如下：

• 你的数据库默认排序规则是SQL_Latin1_General_CP1_CI_AS，这个规则完全是为拉丁字符设计的，对日语假名的排序仅基于Unicode代码点的原始值，根本没有实现日语特有的「假名等价（平假名/片假名视为同一字符）」排序逻辑。
• 当字符串中混合了非日语字符（比如数字）时，整个字符串的排序权重计算会遵循该规则的优先级：拉丁字符（包括数字）的权重高于日语假名，直接干扰了假名的自然排序顺序；而手动拆分字符排序时，是逐个字符单独比较，相当于绕开了整个字符串的权重计算逻辑，反而更接近你预期的逐字符假名排序效果。

当移除数字后，整个字符串都是日语假名，此时即使使用SQL_Latin1_General_CP1_CI_AS，排序也会按假名的Unicode代码点顺序排列，刚好和你预期的顺序一致，但这只是巧合，并非该规则支持日语排序的证明。

根据你的需求：排序时假名类型不敏感、大小写敏感；搜索时假名类型和大小写均不敏感，可以通过以下几种方式解决：

1. 使用日语专用排序规则（推荐） #

SQL Server提供了针对日语的排序规则，你可以在排序和搜索场景分别指定合适的规则：

排序场景（假名不敏感、大小写敏感） #

使用Japanese_CS_AS_KI_WS排序规则：

• CS：大小写敏感
• KI：假名不敏感（平假名和片假名视为同一字符）
• AS：重音敏感（如果不需要区分重音可换成AI）
• WS：宽度敏感（半角/全角视为不同，符合日语使用习惯）

修改你的测试用例排序语句：

-- 修正后的排序语句，符合预期顺序
select unicode(left(title,1)) 'bin', * from @temp 
order by title COLLATE Japanese_CS_AS_KI_WS

搜索场景（假名、大小写均不敏感） #

使用Japanese_CI_AI_KI_WS排序规则：

• CI：大小写不敏感
• AI：重音不敏感
• KI：假名不敏感

示例查询语句：

-- 搜索时自动匹配平假名/片假名、大小写变体
select * from @temp 
where title COLLATE Japanese_CI_AI_KI_WS = N'かか' -- 会匹配かか、カカ、かか（大小写变体）等

2. 手动统一假名类型后排序（兼容旧版本） #

如果你的SQL Server版本不支持KI后缀的排序规则（比如较旧的SQL Server 2008及更早版本），可以手动将所有假名转换为同一种类型（比如统一转成平假名），再进行排序，同时保留大小写敏感：

首先创建一个自定义转换函数：

CREATE FUNCTION dbo.ConvertToHiragana(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- 扩展此部分覆盖所有需要转换的片假名
    RETURN REPLACE(REPLACE(REPLACE(REPLACE(@input, N'カ', N'か'), N'ガ', N'が'), N'イ', N'い'), N'キ', N'き')
END

然后排序时使用该函数：

select unicode(left(title,1)) 'bin', * from @temp 
order by dbo.ConvertToHiragana(title), title -- 先按统一后的假名排序，再按原字段保证大小写敏感

搜索时同样可以用该函数统一转换后比较：

select * from @temp 
where dbo.ConvertToHiragana(LCASE(title)) = dbo.ConvertToHiragana(LCASE(N'かか'))

3. 数据库级别修改排序规则（全局生效） #

如果你的整个数据库都以日语数据为主，可以考虑修改数据库的默认排序规则为Japanese_CS_AS_KI_WS（让排序默认符合需求），搜索时再临时指定不敏感的规则。注意：修改数据库排序规则需要重建所有索引，操作前务必做好数据备份。

用你提供的带数字的测试用例，使用第一种方案的排序语句，会得到和手动拆分字符排序完全一致的结果，完全符合你的预期顺序。

内容的提问来源于stack exchange，提问作者Gideon