MySQL COLLATION 'utf8_general_ci' 对 CHARACTER SET 'latin1' 无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25852239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
提问by Stephen
I am trying to fix a character encoding issue - previously we had the collation set for this column utf8_general_ci which caused issues because it is accent insensitive..
我正在尝试修复字符编码问题 - 以前我们为此列 utf8_general_ci 设置了排序规则，这会导致问题，因为它不区分重音。
I'm trying to find all the entries in the database that could have been affected.
我正在尝试查找数据库中可能受到影响的所有条目。
set names utf8;
select * from table1 t1 join table2 t2 on (t1.pid=t2.pid and t1.id != t2.id) collate utf8_general_ci;
However, this generates the error:
但是，这会产生错误：
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
- The database is now defined with
DEFAULT CHARACTER SET utf8 - The table is defined with
CHARSET=utf8 - The "pid" column is defined with:
CHARACTER SET utf8 COLLATE utf8_bin NOT NULL - The server version is Server version: 5.5.37-MariaDB-0ubuntu0.14.04.1 (Ubuntu)
- 数据库现在定义为
DEFAULT CHARACTER SET utf8 - 该表定义为
CHARSET=utf8 - “pid”列定义为：
CHARACTER SET utf8 COLLATE utf8_bin NOT NULL - 服务器版本为服务器版本：5.5.37-MariaDB-0ubuntu0.14.04.1 (Ubuntu)
Question: Why am I getting an error about latin1 when latin1 doesn't seem to be present anywhere in the table / schema definition?
问题：当 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 |
+----------------------+-------------------+
回答by Mihai
Firstly run this query
首先运行这个查询
SHOW VARIABLES LIKE '%char%';
You have character_set_server='latin1'
你有 character_set_server='latin1'
If so,go into your config file,my.cnf and add or uncomment these lines:
如果是这样，请进入您的配置文件 my.cnf 并添加或取消注释这些行：
character-set-server = utf8
collation-server = utf8_unicode_ci
Restart the server. Yes late to the party,just encountered the same issue.
重新启动服务器。是的，聚会迟到了，刚遇到同样的问题。
回答by Thomas Lauria
The same error is produced in MariaDB (10.1.36-MariaDB) by using the combination of parenthesis and the COLLATE statement. My SQL was different, the error was the same, I had:
在 MariaDB (10.1.36-MariaDB) 中使用括号和 COLLATE 语句的组合会产生同样的错误。我的 SQL 是不同的，错误是一样的，我有：
SELECT *
FROM table1
WHERE (field = 'STRING') COLLATE utf8_bin;
Omitting the parenthesis was solving it for me.
省略括号是为我解决它。
SELECT *
FROM table1
WHERE field = 'STRING' COLLATE utf8_bin;
