SQLite 3：按记录排序时出现字符问题 [英] SQLite 3: Character Issue While Ordering By Records

问题描述

在我的SQLite 3数据库中，我有一些带有土耳其语字符的记录，例如Ö，Ü，İ等。当我使用 SELECT * FROM TABLE ORDER BY COLUMN_NAME选择值时查询，以这些字符开头的记录在末尾。

In my SQLite 3 Database, I have some records with Turkish characters such as "Ö", "Ü", "İ" etc. When I select my values with SELECT * FROM TABLE ORDER BY COLUMN_NAME query, the records that begin with these characters are coming at the end.

通常，它们应该位于每个字母的无点版本之后。就像Ö在 O之后一样，Ü在 U之后。

Normally, they should've come after the letter that is dot-less version of each. Like "Ö" is after "O", "Ü" is after "U".

关于区域设置吗？有没有办法控制这些设置？

Is it something about regional settings? Is there a way to control these settings?

我在Firefox中使用SQLite Manager来管理数据库。

I use SQLite Manager in Firefox to manage my DB.

预先感谢。

P.S。我知道这不是SQLite的解决方案，但对于那些需要在Objective-C中使用SQLite DB的人来说，他们可以在从SQLite DB获取数据后对数据数组进行排序。这是一个很好的解决方案：如何使用自定义对NSMutableArray进行排序

P.S. I know it's not a solution for SQLite but for those who need to use SQLite DB in Objective-C, they can sort the data array after getting from SQLite DB. Here's a good solution: How to sort an NSMutableArray with custom objects in it?

推荐答案

不幸的是，似乎没有直接解决方案。至少适用于iOS。但是有很多方法可以解决。

Unfortunately, it seems there's no direct solution for this. For iOS at least. But there are ways to follow.

在我订阅了SQLite的邮件列表之后，名为Jean-Christophe Deschamps的用户收到了以下答复：

After I subscribed to mailing list of SQLite, user Named Jean-Christophe Deschamps came with this reply:

在我的SQLite 3数据库中，我有一些带有土耳其语字符
的记录，例如Ö，Ü，İ等。当我选择我在 SELECT * FROM
TABLE ORDER BY COLUMN_NAME查询中得到的值以这些
字符开头的记录将在末尾。

"In my SQLite 3 Database, I have some records with Turkish characters such as "Ö", "Ü", "İ" etc. When I select my values with 'SELECT * FROM TABLE ORDER BY COLUMN_NAME' query, the records that begin with these characters are coming at the end."

裸骨SQLite仅在较低的ASCII字符集上正确排序。
虽然这对普通英语来说很好，但对我们大多数人来说却不起作用。

Bare bone SQLite only collates correctly on the lower ASCII charset. While that's fine for plain english, it doesn't work for most of us.

通常，他们应该在带点号的字母之后-less
的每个版本。像Ö在 O之后，Ü在 U之后。
是否与区域设置有关？是否有一种方法可以控制这些
设置？

"Normally, they should've come after the letter that is dot-less version of each. Like "Ö" is after "O", "Ü" is after "U". Is it something about regional settings? Is there a way to control these settings?"

您可以选择几种正确或接近正确的语言
来选择您的语言：

You have the choice among some ways to get it right or close to right for your language(s):

o）将ICU用作扩展程序（适用于第三方管理器）或与应用程序
链接的
。
的优点：在每个
操作中，一次针对一种给定的语言，它可以100％正确地工作。
的缺点：它庞大而又缓慢，需要您为每种处理的语言为
注册一个整理。同样，对于包含几种非英语语言的列
来说，它也不能很好地工作。

o) use ICU either as an extension (for third-party managers) or linked to your application. Advantages: it works 100% correctly for a given language at a time in each operation. Drawbacks: it's huge and slow and it requires you register a collation for every language you deal with. Also it won't work well for columns containing several non-english languages.

o）编写自己的排序规则，以调用操作系统的ICU例程到
整理
个字符串。
优点：庞大的库不会使您的代码膨胀。
缺点：要求您编写此扩展名（用C或类似语言编写），与ICU一样，具有
的其他缺点。

o) write your own collation(s) invoking your OS' ICU routines to collate strings. Advantages: doesn't bloat your code with huge libraries. Drawbacks: requires you write this extension (in C or something), same other drawbacks as ICU.

o）如果使用Windows，下载并使用
扩展名
中的函数，以获得接近正确的结果。
的优点：它很小，相当快并且可以使用，它是语言-
是独立的，但同时对于
的许多语言来说也表现不错。它还提供了许多支持Unicode的
字符串操作函数（不带重音），
模糊搜索功能等等。作为C源代码和
x86 DLL提供，可免费使用。
的缺点：对于任何使用
的语言，它都不能比普通英语字母正确地100％正确地工作：例如，您的无点i会沿点划线i整理
。这是
一种语言的绝对正确性与
大多数语言（包括某些使用变音符号的亚洲语言）的公平正确性之间的一个很好的折衷方案。
下载： http://dl.dropbox.com/u/26433628/unifuzz.zip

o) If you use Windows, download and use the functions in the extension I wrote for a close-to-correct result. Advantages: it's small, fairly fast and ready to use, it is language- independant yet works decently well for many languages at the same time; it also offers a number of Unicode-aware string manipulation functions (unaccenting or not) functions, a fuzzy search function and much more. Comes as a C source and x86 DLL, free for any purpose. Drawback: it probably doesn't work 100% correctly for any language using more than "vanilla english letters": your dotless i will collate along dotted i, for instance. It's a good compromise between absolute correctness for ONE language and "fair" correctness for most languages (including some asian languages using diacritics) Download: http://dl.dropbox.com/u/26433628/unifuzz.zip

我在Firefox中使用SQLite Manager来管理我的数据库。

"I use SQLite Manager in Firefox to manage my DB."

我的小扩展程序可以与此数据库一起使用。您可能还需要
试试SQLite Expert，它内置了ICU（至少在Pro版本中）
和很多更多。

My little extension will work with this one. You might also want to try SQLite Expert which has ICU built-in (at least in its Pro version) and much more.

这篇关于SQLite 3：按记录排序时出现字符问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！