带有重音字符的 SQLite 排序列 [英] SQLite ordering column with accented characters

问题描述

我正在使用 SQLite 数据库和 PHP 开发一个网站.我运行的是 Windows (dev)，我的生产环境应该是 *nix 平台.这是我的表的架构:

I'm developping a website using SQLite databases with PHP. I'm running Windows (dev) and my production environment should be a *nix platform. Here is the schema of my table :

CREATE TABLE [animals](
    [id] INTEGER NOT NULL UNIQUE, 
    [name] VARCHAR(50) NOT NULL
);

我想按 name(包含重音字符)对 animals 进行排序.我的 SQL 查询是:

I want to sort the animals by name (contains accented characters). My SQL query is:

SELECT * FROM animals ORDER BY name DESC

我得到:

éléphant
tigre
renard
chien

代替，

tigre
renard
éléphant
chien

我在网上搜索过.我试过了，

I've searched on the web. I tried,

SELECT * FROM animals ORDER BY name COLLATE *binary|nocase|rtrim* DESC

但我也有同样的问题.我也试过了，

but I have the same problem. I also tried,

SELECT * FROM animals ORDER BY name COLLATE *localized|unicode* DESC

但我收到一个错误(要么我的 SQLite 客户端崩溃，要么 PHP 返回以下错误:no such collat​​ion sequence: UNICODE).

But I get an error (either my SQLite client crashes, either PHP returns the following error: no such collation sequence: UNICODE).

似乎有适用于 Android 的解决方案，但我运行的是 Windows，我的生产环境应该是 *nix 平台.

It seems there's a solution for Android, but I'm running Windows and my production environment should be a *nix platform.

如何正确分类我的动物?

How can I get my animals sorted the right way?

推荐答案

默认情况下，SQLite 只有 ASCII 排序规则.

By default, SQLite has only ASCII collations.

可以使用 ICU 扩展来获得 Unicode 支持，但 PHP 没有启用此功能.

It would be possible to use the ICU extension to get Unicode support, but PHP did not enable this.

但是您可以创建自己的排序规则并在 PHP 中实现比较.

But you can create your own collation and implement the comparison in PHP.

这篇关于带有重音字符的 SQLite 排序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！