mysql - COLLATION 'utf8_general_ci' 对 CHARACTER SET 'latin1' 无效

Question

我正在尝试修复字符编码问题 - 之前我们为该列设置了排序规则 utf8_general_ci 导致问题，因为它不区分重音..

我正在尝试查找数据库中可能受到影响的所有条目。

set names utf8;
select * from table1 t1 join table2 t2 on (t1.pid=t2.pid and t1.id != t2.id) collate utf8_general_ci;

但是，这会产生错误:

ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

1. 现在使用 DEFAULT CHARACTER SET utf8
定义数据库
2. 表是用 CHARSET=utf8
定义的
3. “pid”列定义为:CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
4. 服务器版本为 Server version: 5.5.37-MariaDB-0ubuntu0.14.04.1 (Ubuntu)

问题:当 latin1 似乎没有出现在表/架构定义中的任何位置时，为什么我会收到关于 latin1 的错误？

MariaDB [(none)]> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

Answer 1

首先，运行这个查询:

SHOW VARIABLES LIKE '%char%';

您的帖子中显示了 character_set_server='latin1' ...

因此，进入您的 my.cnf 并添加或取消注释这些行:

character-set-server = utf8
collation-server = utf8_unicode_ci

重启服务器。