ios - Phonegap Sqlite Order by Not Correct utf8 问题

Question

我正在尝试在 Phonegap 上使用 Sqlite 对数据进行 SQL 排序，但结果不正确!它将是:abcçdeéě

我的代码:

db.transaction(function(tx){

        tx.executeSql('SELECT id,keyword FROM Glossary ORDER BY lower(keyword) COLLATE NOCASE',[] , function(tx, results){
            var lastAlpha = "*";
            for(i=0;i<results.rows.length;i++){
                var firstChr = results.rows.item(i).keyword.substring(0,1).toUpperCase();
                if(firstChr!=lastAlpha){
                    console.log(firstChr);
                    lastAlpha = firstChr;
                }
            }
        },errorCB2);
    });

结果:

2012-11-22 17:14:14.641[15032:c07] [LOG] A
2012-11-22 17:14:14.641[15032:c07] [LOG] B
2012-11-22 17:14:14.642[15032:c07] [LOG] C
2012-11-22 17:14:14.644[15032:c07] [LOG] D
2012-11-22 17:14:14.645[15032:c07] [LOG] E
2012-11-22 17:14:14.646[15032:c07] [LOG] F
2012-11-22 17:14:14.647[15032:c07] [LOG] G
2012-11-22 17:14:14.648[15032:c07] [LOG] H
2012-11-22 17:14:14.649[15032:c07] [LOG] I
2012-11-22 17:14:14.650[15032:c07] [LOG] J
2012-11-22 17:14:14.651[15032:c07] [LOG] K
2012-11-22 17:14:14.652[15032:c07] [LOG] L
2012-11-22 17:14:14.654[15032:c07] [LOG] M
2012-11-22 17:14:14.655[15032:c07] [LOG] N
2012-11-22 17:14:14.656[15032:c07] [LOG] O
2012-11-22 17:14:14.657[15032:c07] [LOG] P
2012-11-22 17:14:14.658[15032:c07] [LOG] Q
2012-11-22 17:14:14.659[15032:c07] [LOG] R
2012-11-22 17:14:14.660[15032:c07] [LOG] S
2012-11-22 17:14:14.661[15032:c07] [LOG] T
2012-11-22 17:14:14.662[15032:c07] [LOG] U
2012-11-22 17:14:14.664[15032:c07] [LOG] V
2012-11-22 17:14:14.665[15032:c07] [LOG] W
2012-11-22 17:14:14.666[15032:c07] [LOG] X
2012-11-22 17:14:14.668[15032:c07] [LOG] Y
2012-11-22 17:14:14.669[15032:c07] [LOG] Z
2012-11-22 17:14:14.671[15032:c07] [LOG] Ç
2012-11-22 17:14:14.672[15032:c07] [LOG] É
2012-11-22 17:14:14.673[15032:c07] [LOG] Ö
2012-11-22 17:14:14.674[15032:c07] [LOG] Ü
2012-11-22 17:14:14.674[15032:c07] [LOG] İ
2012-11-22 17:14:14.676[15032:c07] [LOG] Ş

我在 The Last 看过类似“拉丁字符”的内容。 !你有什么想法吗？

Answer 1

http://www.sqlite.org/datatype3.html#collation

NOCASE - The same as binary, except the 26 upper case characters of ASCII are folded to their lower case equivalents before the comparison is performed. Note that only ASCII characters are case folded. SQLite does not attempt to do full UTF case folding due to the size of the tables required.

我相信这是在说 SQLite 不会提供那种排序，因为它会花费太多的处理时间来实现它。 SQLite 确实提供了一个接口(interface)来定义您自己的排序规则序列 (http://www.sqlite.org/c3ref/create_collat​​ion.html)，但我认为这在 PhoneGap 中不容易使用。

如果您下定决心要进行这种排序，则可以对其进行一些修改并执行多个查询，这些查询将基于“开头为”类型的运算符进行查询，然后按所需顺序将它们全部链接在一起。但一想到要实际尝试这样做，我就感到畏缩。