mysql - Windows 与 freeBSD 上的 MariaDB 字符集和排序规则

Question

我在追查为什么我的 MariaDB Windows 开发安装与我的 FreeBSD 安装不匹配时遇到了一些麻烦。这是我在 freeBSD 上的测试数据库的一些输出:

MariaDB [www]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | latin1             |
| character_set_connection | latin1             |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | latin1             |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | latin1_swedish_ci  |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+
10 rows in set (0.00 sec)

MariaDB [www]> SHOW FULL COLUMNS FROM items;
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id          | int(11)      | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| description | varchar(255) | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.01 sec)

MariaDB [www]> select description from items where id=15;
+------------------------+
| description            |
+------------------------+
| 15 kΩ  1/8W 1%  (smd) |
+------------------------+
1 row in set (0.00 sec)

这是 Windows 上的等价物:

MariaDB [www]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | latin1             |
| character_set_connection | latin1             |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | latin1             |
| character_set_server     | latin1             |
| character_set_system     | utf8               |
| collation_connection     | latin1_swedish_ci  |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | latin1_swedish_ci  |
+--------------------------+--------------------+
10 rows in set (0.01 sec)
MariaDB [www]> SHOW FULL COLUMNS FROM items;
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type         | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| id          | int(11)      | NULL               | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| description | varchar(255) | utf8mb4_unicode_ci | YES  |     | NULL    |                | select,insert,update,references |         |
+-------------+--------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
2 rows in set (0.01 sec)

MariaDB [www]> select description from items where id=15;
+------------------------+
| description            |
+------------------------+
| 15 kΩ  1/8W 1%  (smd) |
+------------------------+
1 row in set (0.00 sec)

所以我的问题是，在设置比较方面我遗漏了什么可能是 Windows 上损坏字符 (Ω) 的来源，以及需要将其更改为什么才能获得相同的字符我的 FreeBSD 服务器。 FreeBSD 安装稍新 10.1.23-MariaDB FreeBSD Ports 与 10.1.19-MariaDB mariadb.org 二进制分发版。

Answer 1

问题#1:始终使用 utf8(或 utf8mb4)。 latin1 没有 Ω。你很幸运能在任何一个系统上看到它。请参阅 Trouble with utf8 characters; what I see is not what I stored 中的“最佳实践”

问题 #2: 在 Windows 上，cmd 不一定默认支持 UTF-8。命令“chcp”控制“代码页”。 chcp 65001 提供 UTF-8，但它也需要安装一个特殊的字符集。要在控制台窗口中设置字体:右键单击窗口标题 → 属性 → 字体 → 选择 Lucida Console 。

检查:要查看数据是否正确存储(在整个更改为 utf8mb4 之后)，SELECT HEX(...) ... 查看您是否得到CEA9 用于 Omega。如果你得到 C38EC2A9，你就有了“双重编码”。当以 UTF-8 编码时，其他希腊字母将是 CExx 或 CFxx。