MySQL不按瑞典字符åäö排序，尽管排序规则正确。

Question

这个查询语句：

SELECT customer_id, customer_name FROM customers WHERE isActive = '1' ORDER BY customer_name ASC

输出结果如下：

+-------------+-----------------------+
| customer_id | customer_name         |
+-------------+-----------------------+
|           1 | Äname                 |
|           2 | Aname                 |
|           3 | Bname                 |
+-------------+-----------------------+

尽管我使用了utf8_swedish_ci的排序规则，为什么它不能排序特殊的瑞典字符呢？

SHOW TABLE STATUS FROM myDatabase WHERE name = 'customers';

+-------------+----------------------------+
| Name        | Engine   | Collation       |
+-------------+----------------------------+
| customers   | MyISAM   | utf8_swedish_ci |
+-------------+----------------------------+

我甚至尝试在查询中添加排序规则:

SELECT * FROM customers WHERE isActive = 1 COLLATE utf8_swedish_ci ORDER BY customer_name ASC

但是我得到了：

Error Code: 1253. COLLATION 'utf8_swedish_ci' is not valid for CHARACTER SET 'binary'

- David

---

列的字符集是什么？ - Álvaro González

@ÁlvaroG.Vicario utf8_swedish_ci - David

SHOW CREATE TABLE customers 的输出是什么？ - Álvaro González

Answer 1

关于默认行为我不确定，但正确的语法是：

SELECT customer_id, customer_name
FROM customers
WHERE isActive = '1'
ORDER BY customer_name COLLATE utf8_swedish_ci ASC

- Álvaro González

---

建议使用 utf8mb4。 - marko