不区分大小写的UTF-8选择查询

Question

在SQLite中，我想要不区分大小写地使用"SELECT LIKE name"。对于常见的拉丁文名称，这种方法可以很好地工作，但是当名字包含非拉丁字符的UTF-8编码时，查询将变成区分大小写的。那么如何使其像处理拉丁字符一样不区分大小写呢？

注：我的SQLite版本为v3，并且使用PHP PDO进行连接。

- Timo Huovinen

Answer 1

针对SQLite，您有两个选项：

1. 使用ICU进行编译： 编译方法，编译选项
2. 覆盖LIKE函数，这里提供了一个完整的解决方案（来自http://blog.amartynov.ru/?p=675）

$pdo = new PDO("sqlite::memory:");

# BEGIN

function lexa_ci_utf8_like($mask, $value) {
    $mask = str_replace(
        array("%", "_"),
        array(".*?", "."),
        preg_quote($mask, "/")
    );
    $mask = "/^$mask$/ui";
    return preg_match($mask, $value);
}

$pdo->sqliteCreateFunction('like', "lexa_ci_utf8_like", 2);

# END

$pdo->exec("create table t1 (x)");
$pdo->exec("insert into t1 (x) values ('[Привет España Dvořák]')");

header("Content-Type: text/plain; charset=utf8");
$q = $pdo->query("select x from t1 where x like '[_РИ%Ñ%ŘÁ_]'");
print $q->fetchColumn();

- amartynov

---

谷歌不喜欢我询问如何使用ICU进行编译，请提供链接。否则，你将成为第一个给出正确答案的人！ - Timo Huovinen

@YuriKolovsky 关于编译ICU，没有权威指南，我也没有尝试过这种方式，所以你还是需要谷歌一下 :) - amartynov

提供的示例似乎无法处理俄语字母，LIKE '%ОЛЬ%'。我做错了什么？如果我能解决这个问题，你将成为我的新编程榜样 :) - Timo Huovinen

@YuriKolovsky，我的函数在使用西里尔字母时出现了问题。我更新了博客文章-请检查新版本并让我知道是否正常工作。感谢您的测试 :) - amartynov

Answer 2

使用不区分大小写的排序规则，例如： LIKE name COLLATE NOCASE。

如果您需要比较特定的非 ASCII 字符并进行大小写折叠，NOCASE 将无法工作，因为 SQLite 不支持此类折叠 - 您将需要使用您选择的 Unicode 库提供自己的排序规则函数，并使用 sqlite3_create_collation()。 编辑： 另外，这可能会有所帮助：

如何在指定区域设置下对 sqlite3 中的文本进行排序？

- Victor Nicollet

---

根据 NOCASE 的文档，它仅支持 ASCII；它明确表示排除了 UTF-8。 - borrible

文档中说明的是，UTF-8 字符 不属于 ASCII 的部分 将区分大小写。因此 A = a，但 Œ != œ。编辑以澄清。 - Victor Nicollet

Answer 3

一种通过UDF改进的LIKE重载版本：

$db->sqliteCreateFunction('like',
    function ($pattern, $data, $escape = null) use ($db)
    {
        static $modifiers = null;

        if (isset($modifiers) !== true)
        {
            $modifiers = ((strncmp($db->query('PRAGMA case_sensitive_like;')->fetchColumn(), '1', 1) === 0) ? '' : 'i') . 'suS';
        }

        if (isset($data) === true)
        {
            if (strpbrk($pattern = preg_quote($pattern, '~'), '%_') !== false)
            {
                $regex = array
                (
                    '~%+~S' => '.*',
                    '~_~S' => '.',
                );

                if (strlen($escape = preg_quote($escape, '~')) > 0)
                {
                    $regex = array
                    (
                        '~(?<!' . $escape . ')%+~S' => '.*',
                        '~(?<!' . $escape . ')_~S' => '.',
                        '~(?:' . preg_quote($escape, '~') . ')([%_])~S' => '$1',
                    );
                }

                $pattern = preg_replace(array_keys($regex), $regex, $pattern);
            }

            return (preg_match(sprintf('~^%s$~%s', $pattern, $modifiers), $data) > 0);
        }

        return false;
    }
);

尊重case_sensitive_like PRAGMA，并正确处理x LIKE y ESCAPE z syntax语法。

我还编写了另一个版本，对x和y值进行基本和扩展罗马化，因此重音字符将与其未带重音的对应字符匹配，例如：SELECT 'Á' LIKE 'à%';。

您可以收藏这个gist以便随时关注更新。

- Alix Axel