mysql选择不同的字母，包括扩展的拉丁字符 [英] mysql select distinct letters, including extended latin characters

问题描述

原始问题:

表结构:

CREATE TABLE `texts` (
  `letter` VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `text` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  INDEX (`letter` ASC),
  INDEX (`text` ASC)
)
ENGINE InnoDB
CHARACTER SET utf8
COLLATE utf8_general_ci;

样本数据:

INSERT INTO `texts`
(`letter`, `text`)
VALUES
('a', 'Apple'),
('ā', 'Ābols'),
('b', 'Bull'),
('c', 'Cell'),
('č', 'Čakste');

我正在执行的查询:

SELECT DISTINCT `letter` FROM `texts`;

预期结果:

`letter`
a
ā
b
c
č

实际结果:

`letter`
a
b
c

我尝试了很多utf8归类(utf8_ [bin | general_ci | unicode_ci]， utf8mb4_ [bin | general_ci | unicode_ci]等)，它们都不起作用.如何 解决这个问题?

I've tried many utf8 collations (utf8_[bin|general_ci|unicode_ci], utf8mb4_[bin|general_ci|unicode_ci] etc), none of them work. How to fix this?

编辑以澄清问题:我想要的不仅仅是获取所有字母 ，但也可以按照我在预期中指定的顺序来获取它们 结果. utf8_bin获取所有字母，但它们在 错误的方式-扩展的拉丁字符仅在所有基本字符之后才遵循 拉丁字符(例如:a，b，c，ā，č).另外，我的实际表 使用每个字母有很多文本，因此必须分组.

Edit for clarification: what I want is not just to get all the letters out, but also get them in the order I specified in the expected results. utf8_bin gets all the letters, but they are ordered in the wrong way - extended latin characters follow only after all the basic latin characters (example: a, b, c, ā, č). Also, the actual table I'm using has many texts per letter, so grouping is a must.

这是实时网站中的完整表格数据- http://pastebin.com/cH2DUzf3 执行该SQL并在此之后运行以下查询:

Edit #2: here's the full table data from the live site - http://pastebin.com/cH2DUzf3 Executing that SQL and running the following query after that:

SELECT DISTINCT BINARY `letter` FROM `texts` ORDER BY `letter` ASC

几乎可以得到完美的结果，但有一个例外:字母'ū'在之前'u'，至少可以奇怪地说，因为所有其他扩展的拉丁文字母显示在其基本的拉丁语版本之后.我该如何解决最后一个问题?

yields almost perfect results, with one exception: the letter 'ū' is before 'u', which is weird to say the least, because all other extended latin letters show up after their basic latin versions. How do I solve this one last problem?

推荐答案

检查检查 SQL小提琴

这篇关于mysql选择不同的字母，包括扩展的拉丁字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！