mysql - 选择不同的 : control priority when there are accents?

Question

例子:

> SELECT name, culture FROM city_i18n WHERE ID = 2745;
+-------+---------+
| name  | culture |
+-------+---------+
| Paris | en_GB   |
| París | es_ES   |
| Paris | pt_BR   |
| Paris | pt_PT   |
+-------+---------+
4 rows in set (0.00 sec)

> SELECT DISTINCT name FROM city_i18n WHERE ID = 2745 AND culture IN ('es_ES', 'en_GB');
+-------+
| name  |
+-------+
| Paris |
+-------+
1 row in set (0.00 sec)

> SELECT DISTINCT name FROM city_i18n WHERE ID = 2745 AND culture IN ('es_ES', 'pt_PT');
+-------+
| name  |
+-------+
| París |
+-------+
1 row in set (0.00 sec)

注意两个 SELECT DISTINCT 的不同输出:Paris、París(带重音)

现在，总是给予 es_ES 优先级(即总是带口音的 París)的简单方法是什么？

由于存在问题，需要澄清:仅口音不同(根据整理)的城市名称应该只出现一次。在这种情况下，显示的名称应该具有文化重音 es_ES。

Answer 1

问题是您的数据库中使用的归类序列。根据您的 RDBMS，有大量关于 Collat​​ion Sequences 的信息。更改排序规则顺序时需要非常小心，因为它可能会产生意想不到的后果。

Collat​​ion Sequence 用于测试列的相等或不相等。根据使用的排序规则，以下语句可能全部评估为 true 或 false:

'TexT' = 'text'
'TEXT' = 'text'
'áéíóú' = 'aeiou'

在您的情况下，您需要选择一个归类序列，它将重音字母和常规字母视为不同的值。目前您的数据库正在确定它们是否相等。一旦准备就绪，您就可以开发自己的逻辑，根据您喜欢的 culture

选择合适的值

只是补充一下，您的数据库目前正在确定 Paris = París，我怀疑无法保证将选择哪个不同的值，类似于没有确定的事实除非使用 ORDER BY 子句，否则结果集的顺序。

我想了很久。我认为最好的办法是创建一个 CultureRank

CREATE TABLE CultureRank
(
    Rank INTEGER,
    Culture VARCHAR(5)
);

INSERT INTO CultureRank VALUES (1, 'es_ES');
INSERT INTO CultureRank VALUES (2, 'en_GB');
INSERT INTO CultureRank VALUES (3, 'pt_BR');
INSERT INTO CultureRank VALUES (4, 'pt_PT');

SELECT
    Name
FROM
    City_i18n
    LEFT JOIN CultureRank ON City_i18n.Culture = CultureRank.Culture
WHERE
    ID = 2745
AND City_i18n.Culture IN ('es_ES', 'pt_PT') -- If Required
ORDER BY
    IF(ISNULL(CultureRank.Culture),1,0), 
    CultureRank.Rank
LIMIT 1;

如果 city_i18n 中存在不在 CultureRank 中的文化，则需要 ORDER BY ISNULL。这里的 IsNull 函数将确保 CultureRank 表中的文化被赋予最高优先级，然后如果这些文化都不与城市相关联，那么引擎将从 city_i18n 表中为该城市选择一个随机文化。如果您希望将不在 CultureRank 中的文化放在首位，请交换 ISNULL 函数中的 1 和 0 参数。

编辑:

SELECT
    Name
FROM
    City_i18n
    LEFT JOIN CultureRank ON City_i18n.Culture = CultureRank.Culture
WHERE
    ID = 2745
AND City_i18n.Culture IN ('es_ES', 'pt_PT') -- If Required
AND NOT EXISTS (
    SELECT
        NULL
    FROM
        City_i18n Cities
        LEFT JOIN CultureRank CitiesRank ON Cities.Culture = CitiesRank.Culture
    WHERE
        City_i18n.Name = Cities.Name
    AND CultureRank.Rank < CitiesRank.Rank)

这应该会为您从 City_i18n 表中获取给定 ID 的每个名称，除非根据您的整理顺序存在另一条具有相同名称的记录，该记录在 CultureRank 表中具有更高的排名。