SQLite多语言自定义排序失效：icu_load_collation用法错误如何解决？

你使用icu_load_collation的方式有误，该函数第一个参数仅支持传入单个合法的ICU区域标识符，不支持直接拼接多个语言代码定义多语言优先级，因此你创建的排序规则实际无效，才会出现排序没有变化的情况。

该函数用于将ICU提供的单语言排序规则注册到SQLite中，语法如下：
icu_load_collation('ICU区域代码', '自定义排序规则名称');

常用的单语言排序规则注册示例：

• 注册英文（美国）排序规则：SELECT icu_load_collation('en_US', 'en_sort');
• 注册简体中文排序规则：SELECT icu_load_collation('zh_CN', 'zh_sort');
• 注册韩文排序规则：SELECT icu_load_collation('ko_KR', 'ko_sort');

注册完成后，可直接在排序时调用对应规则，例如：
SELECT Name FROM Songs ORDER BY Name COLLATE en_sort;

你需要的多语言层级排序逻辑无法通过单个ICU排序规则实现，可选用以下三种方案：

方案1：ORDER BY 分支判断排序（无需修改表结构） #

原理是先通过字符UNICODE范围判断每条记录的语言类别，按你需要的优先级给类别赋值，先按类别优先级排序，同类别下再调用对应语言的ICU排序规则排序。

以英文>韩文>中文优先级为例，完整SQL示例如下：

-- 先注册三类语言的排序规则
SELECT icu_load_collation('en_US', 'en_sort');
SELECT icu_load_collation('ko_KR', 'ko_sort');
SELECT icu_load_collation('zh_CN', 'zh_sort');

-- 执行排序查询
SELECT Name FROM Songs
ORDER BY
  -- 定义语言优先级，数值越小优先级越高
  CASE
    -- 匹配英文：仅包含字母、数字、标点、空格
    WHEN Name REGEXP '^[A-Za-z0-9[:punct:][:space:]]+$' THEN 1
    -- 匹配韩文：unicode范围 U+AC00 ~ U+D7A3
    WHEN UNICODE(SUBSTR(Name, 1, 1)) BETWEEN 44032 AND 55203 THEN 2
    -- 剩下的归为中文（含简繁）
    ELSE 3
  END,
  -- 同语言类别下用对应排序规则排序
  CASE
    WHEN Name REGEXP '^[A-Za-z0-9[:punct:][:space:]]+$' THEN Name COLLATE en_sort
    WHEN UNICODE(SUBSTR(Name, 1, 1)) BETWEEN 44032 AND 55203 THEN Name COLLATE ko_sort
    ELSE Name COLLATE zh_sort
  END;

如果需要调整优先级，只要修改第一个CASE分支中的数值即可，比如中文优先的场景，把中文分支的数值设为1，英文设为2，韩文设为3即可。

方案2：预存语言分类字段（性能更优） #

如果数据量较大，每次查询时实时判断语言效率较低，可以提前给Songs表新增lang_type整型字段，写入数据时提前判断每条内容的语言类别并赋值（比如1=英文，2=韩文，3=中文）。
查询时直接按该字段排序即可，示例：

SELECT Name FROM Songs
ORDER BY
  lang_type,
  CASE lang_type
    WHEN 1 THEN Name COLLATE en_sort
    WHEN 2 THEN Name COLLATE ko_sort
    WHEN 3 THEN Name COLLATE zh_sort
  END;

方案3：注册自定义排序函数（适合编程语言调用SQLite的场景） #

如果是在Python、Node.js等编程语言环境中调用SQLite，可以通过SQLite提供的自定义排序规则接口实现排序逻辑，以Python为例：

import sqlite3
import icu

# 初始化三类语言的ICU比较器
en_collator = icu.Collator.createInstance(icu.Locale('en_US'))
ko_collator = icu.Collator.createInstance(icu.Locale('ko_KR'))
zh_collator = icu.Collator.createInstance(icu.Locale('zh_CN'))

# 判断内容所属语言类别
def get_lang_type(s):
    first_char = s[0]
    # 韩文范围
    if '\uAC00' <= first_char <= '\uD7A3':
        return 2
    # 中文范围
    if '\u4e00' <= first_char <= '\u9fff' or '\u3400' <= first_char <= '\u4dbf':
        return 3
    # 默认归为英文
    return 1

# 自定义英文优先排序逻辑
def en_first_compare(s1, s2):
    t1 = get_lang_type(s1)
    t2 = get_lang_type(s2)
    # 不同语言按优先级排序
    if t1 != t2:
        return -1 if t1 < t2 else 1
    # 同语言用ICU比较器排序
    if t1 == 1:
        return en_collator.compare(s1, s2)
    elif t1 == 2:
        return ko_collator.compare(s1, s2)
    else:
        return zh_collator.compare(s1, s2)

# 把自定义规则注册到SQLite
conn = sqlite3.connect('你的数据库文件路径.db')
conn.create_collation('en_first', en_first_compare)

# 调用自定义规则排序
cursor = conn.cursor()
cursor.execute('SELECT Name FROM Songs ORDER BY Name COLLATE en_first')

内容的提问来源于stack exchange，提问作者Tu Dao Anh Tu