我正在尝试解决字符编码问题 - 之前我们将此列的排序规则设置为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'
- 数据库现在定义为
DEFAULT CHARACTER SET utf8
- 表定义为
CHARSET=utf8
- "pid"列定义为:
CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
- 服务器版本为: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 |
+----------------------+-------------------+
SHOW CREATE TABLE table1
和SHOW CREATE TABLE table2
。你的客户端连接字符集是什么?还发布SHOW VARIABLES LIKE '%char%'
。 - Michael Berkowskiset names utf8
,所以希望character_set_client
会显示utf8
。 - Michael Berkowski