问题描述
我正在尝试解决字符编码问题-以前我们为utf8_general_ci列设置了归类设置,由于该字符对重音不敏感,因此导致出现问题.
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..
我正试图在数据库中查找所有可能受到影响的条目.
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;
但是,这会产生错误:
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
- 现在使用
DEFAULT CHARACTER SET utf8
定义了数据库 - 该表是用
CHARSET=utf8
定义的 - "pid"列的定义如下:
CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
- 服务器版本为服务器版本:5.5.37-MariaDB-0ubuntu0.14.04.1(Ubuntu)
- 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)
问题:当表格/架构定义中的任何地方似乎都不存在latin1时,为什么会出现关于latin1的错误?
Question: Why am I getting an error about latin1 when latin1 doesn't seem to be present anywhere in the table / schema definition?
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 |
+----------------------+-------------------+
推荐答案
首先运行此查询
SHOW VARIABLES LIKE '%char%';
您有character_set_server='latin1'
如果是,请进入您的配置文件my.cnf并添加或取消注释以下行:
If so,go into your config file,my.cnf and add or uncomment these lines:
character-set-server = utf8
collation-server = utf8_unicode_ci
重新启动服务器.是的,聚会晚了,只是遇到了同样的问题.
Restart the server.Yes late to the party,just encountered the same issue.
这篇关于COLLATION'utf8_general_ci'对CHARACTER SET'latin1'无效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!