使用 System.Data.SQLite 对 UTF8 数据进行不区分大小写的排序

• 下载源文件 - 440.95 KB

引言

如果您在 .NET 中使用 SQLite，可能会遇到 UTF8 字符串不区分大小写排序的问题。

背景

如果您使用 System.Data.SQLite，此代码将非常有用，您可以从此处下载：下载 System.Data.SQLite。我使用 SQLite 已经很长时间了，并在其中存储了俄语字符串。正如 SQLite FAQ 中所述，Unicode 字符不区分大小写的匹配不起作用。随着存储数据的量不断增长，这个问题变得非常严重。System.Data.SQLite 提供了一个很棒的解决方案，但实现起来并不清楚。

Using the Code

使用此代码非常简单：只需在应用程序的开头添加这一行

SQLiteFunction.RegisterFunction(typeof(SQLiteCaseInsensitiveCollation));

数据库

好的，我们开始吧。首先，SQLite 有三种内置的排序规则（如果您需要更多信息，可以在 SQLite 网站上阅读）

• BINARY – 所有字符都视为字节
• NOCASE – 以不区分大小写的方式比较 ASCII 字符；其他字符则视为字节
• RTRIM – 仅忽略尾随空格

我们要创建如下表

ID	名称	Val（表中存储的某个值）	注释
1	Foo	123
2	foo	234
3	Bar	345
4	bar	456
5	Буква	567	一个以大写字母开头的俄语示例文本
6	буква	678	与上面相同的俄语文本，但以小写字母开头
7	Тест	789	另一个以大写字母开头的俄语文本
8	тест	890	与上面相同的俄语文本，但以小写字母开头

这是创建它的 SQL 语句

CREATE TABLE `testtbl` (
    `ID` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `Name` TEXT NOT NULL COLLATE NOCASE, 
    `Val` REAL)   

现在我们将运行一个简单的 SELECT

SELECT * FROM `testtbl` ORDER BY `Name`

我们将得到结果

ID	名称	Val（表中存储的某个值）	注释
2	Bar	345
3	bar	456
1	Foo	123
2	foo	234
5	Буква	567	排序不正确！
7	Тест	789
6	буква	678
8	тест	890

正如您所见，俄语数据排序不正确。正确的顺序应该是：Буква, буква, Тест, тест。

现在我们将修复这个问题。如果 SQLite 可以按此表达式（在 C# 中）排序，那就太好了

string.Compare(x, y, CultureInfo.CreateSpecificCulture("ru-RU"), 
	CompareOptions.IgnoreCase);

在 C# 代码中定义不区分大小写的排序规则

我创建了一个名为 SQLiteCaseInsensitiveCollation 的简单类，它为 SQLite 引擎添加了排序规则

using System.Data.SQLite;
using System.Globalization;

namespace SQLiteUTF8CIComparison {
    /// 

    /// This function adds case-insensitive sort feature to SQLite engine 
    /// To initialize, use SQLiteFunction.RegisterFunction() 
    /// before all connections are open 
    /// 
    [SQLiteFunction(FuncType = FunctionType.Collation, Name = "UTF8CI")]
    public class SQLiteCaseInsensitiveCollation : SQLiteFunction {
        /// 

        /// CultureInfo for comparing strings in case insensitive manner 
        /// 
        private static readonly CultureInfo _cultureInfo = 
			CultureInfo.CreateSpecificCulture("ru-RU");

        /// 

        /// Does case-insensitive comparison using _cultureInfo 
        /// 
        /// Left string
        /// Right string
        /// The result of a comparison
        public override int Compare(string x, string y) {
            return string.Compare(x, y, _cultureInfo, CompareOptions.IgnoreCase);
        }
    }
}   

请注意这个属性

[SQLiteFunction(FuncType = FunctionType.Collation, Name = "UTF8CI")]

在这里我们定义

• 这是一个必须添加到 SQLite 引擎的函数
• 它是一个排序函数
• 它的名字是 UTF8CI，意思是 UTF8 不区分大小写

要激活排序规则，您需要在 SQLite 引擎中注册它——在您的应用程序中创建所有 SQLite 连接之前，添加这一行代码

SQLiteFunction.RegisterFunction(typeof(SQLiteCaseInsensitiveCollation));

请注意，该函数只能调用一次，例如在应用程序启动时。

在 SQLite 中使用 UTF8CI 排序规则

如何在 SQLite 中使用此排序规则？

CREATE TABLE `testtbl` (
    `ID` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    `Name` TEXT NOT NULL COLLATE UTF8CI, 
    `Val` REAL)

正如您所见，我们所需要做的就是替换排序规则。现在我们还需要使用此排序规则创建一个索引

CREATE INDEX `IDX_testtbl_Name` ON `testtbl` (`Name` COLLATE UTF8CI)

执行测试

现在 SELECT 将给我们结果

ID	名称	Val（表中存储的某个值）	注释
2	Bar	345
3	bar	456
1	Foo	123
2	foo	234
5	Буква	567	就是这样！
6	буква	678
7	Тест	789
8	тест	890

是的！俄语数据已按我们想要的区分大小写方式排序。我们的索引被使用了，因为它的排序规则也是不区分大小写的。

兼容性 

如果您在不添加自定义编码的情况下使用 UTF8CI 编码打开 SQLite 文件，会发生什么？让我们看看 SELECT 的结果

SELECT * FROM `testtbl`

它正常执行——排序规则和索引均未被使用。

SELECT * FROM `testtbl` ORDER BY `Name`

糟糕。我们得到一个错误：No such collation: UTF8CI。为了避免这种情况，您可以使用以下 SQL

SELECT * FROM `testtbl` ORDER BY `Name` COLLATE BINARY

这正常执行，并按通常的区分大小写（二进制）排序给出结果。我们使用 UTF8CI 编码的索引无法使用：SQLite 对该索引的排序规则一无所知。

关注点

我们学到了几点

• 在 SQLite 中，可以使用不区分大小写的排序规则对 UTF8 数据进行排序。
• 您还可以创建不区分大小写的 UTF8 索引。
• 您可以使用我的函数（根据您的区域设置）在 SQLite 中创建 UTF8CI 排序规则。
• 请注意：如果您忘记注册此函数，则在读取数据时会收到异常。
• 数据库在没有函数代码的情况下仍然可用；但仅使用内置编码进行字段排序将不可用。
• 使用 UTF8CI 编码创建的任何索引都将失效。

修订历史

• 2009 年 11 月 - 初次修订