SQLite与JavaScript排序顺序不匹配问题的技术问询

问题场景 #

使用better-sqlite-3的SQL语句对商品列表排序，规则为：先按relevance降序（本次场景下值均为0），再按preferito（收藏状态）降序，最后按des_art（商品名称）升序：

ORDER BY
    relevance DESC,
    preferito DESC,
    des_art   ASC

SQL返回的排序结果为：

"ACCIUGA RIPIENA CREUZA"
"ACCIUGA RIPIENA baccalà"

但通过JavaScript函数恢复排序时，默认的localeCompare会忽略大小写导致顺序反转：

allProducts.sort((a, b) => {
    if (b.relevance !== a.relevance) {
        return b.relevance - a.relevance;
    }
    if (b.preferito !== a.preferito) {
        return b.preferito - a.preferito;
    }
    return a.des_art.localeCompare(b.des_art);
});

此时结果变为：

"ACCIUGA RIPIENA baccalà"
"ACCIUGA RIPIENA CREUZA"

尝试使用localeCompare的sensitivity: 'case'和caseFirst: 'upper'参数后仍无效果，需要让JavaScript排序完全匹配SQLite的行为。

解决方案 #

SQLite默认采用二进制ASCII排序：大写字母的ASCII编码值（如'A'=65）小于小写字母（如'a'=97），因此大写开头的字符串会排在小写开头的字符串前面。而localeCompare默认遵循语言环境排序规则，无法完全对齐这种二进制排序逻辑，因此需要改用字符编码值直接比较。

方法1：自定义二进制比较函数（最可靠） #

实现一个逐字符比较Unicode编码值的函数，完全匹配SQLite的排序逻辑：

function binaryStringCompare(strA, strB) {
    const minLength = Math.min(strA.length, strB.length);
    for (let i = 0; i < minLength; i++) {
        const codeA = strA.charCodeAt(i);
        const codeB = strB.charCodeAt(i);
        if (codeA !== codeB) {
            return codeA - codeB;
        }
    }
    // 前面字符都相同时，短字符串排在前面
    return strA.length - strB.length;
}

// 应用到排序逻辑中
allProducts.sort((a, b) => {
    if (b.relevance !== a.relevance) {
        return b.relevance - a.relevance;
    }
    if (b.preferito !== a.preferito) {
        return b.preferito - a.preferito;
    }
    return binaryStringCompare(a.des_art, b.des_art);
});

方法2：调整localeCompare参数（部分场景有效） #

如果必须使用localeCompare，可以指定明确的区域设置并调整参数，但该方法依赖于运行环境的locale支持，兼容性不如方法1：

allProducts.sort((a, b) => {
    if (b.relevance !== a.relevance) {
        return b.relevance - a.relevance;
    }
    if (b.preferito !== a.preferito) {
        return b.preferito - a.preferito;
    }
    // 指定en-US locale，强制大写优先并启用大小写敏感
    return a.des_art.localeCompare(b.des_art, 'en-US', {
        caseFirst: 'upper',
        sensitivity: 'case'
    });
});

为什么之前的参数无效？ #

sensitivity: 'case'仅启用大小写区分，但不同locale的排序规则可能仍会按照语言习惯调整大小写的优先级，而非严格遵循ASCII编码顺序。只有直接比较字符的编码值，才能完全对齐SQLite的二进制排序行为。

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