phpmyadmin 显示奇怪的字符(utf8)

Question

所以我已经将 mysql 设置为完全支持 utf8:

my.cnf:

[mysqld]
character_set_client=utf8
character-set-server=utf8
collation_server=utf8_general_ci

和

mysql> show variables like "char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> show variables like "coll%";
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

现在，我使用 mysql 查询在我的 Web 应用程序中正确存储和获取(和 json_encode)utf8 字符，但 phpmyadmin 出现问题。问题是 phpmyadmin 将这些国际字符显示为乱码。

έλεος

sadaa

为了澄清问题，我说的是新数据，而不是已经存储的可能已损坏的数据。

phpmyadmin 的 session 变量是:

show variables like "char%";
Variable_name   Value   
character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    utf8
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/

show variables like "coll%" 
Variable_name   Value   
collation_connection    utf8mb4_general_ci
collation_database  utf8_general_ci
collation_server    utf8_general_ci

到目前为止，我发现国际 utf8 字符在 phpmyadmin 中正确显示而不是乱码的唯一方法是运行

SET NAMES utf8

每次在客户端应用程序 (php) 查询之前:a) 很奇怪，因为正如你所看到的，一切都是正确的 utf8b) 我想避免每次都运行该查询

这是 phpmyadmin 独有的问题还是我的设置的一般数据编码问题？不管怎样，我该如何解决这个问题？最好只使用配置文件设置，不必每次都运行 SET NAMES utf8 查询。

提前致谢。

Answer 1

回答我自己的问题。

看起来这不是 phpmyadmin 问题，而是一般编码问题。

问题是我没有设置客户端连接字符集。

如果我显示 php 文件中的变量，我会得到以下结果:

{"Variable_name":"character_set_client","Value":"latin1"}
{"Variable_name":"character_set_connection","Value":"latin1"}
{"Variable_name":"character_set_database","Value":"utf8"}
{"Variable_name":"character_set_filesystem","Value":"binary"}
{"Variable_name":"character_set_results","Value":"latin1"}
{"Variable_name":"character_set_server","Value":"utf8"}
{"Variable_name":"character_set_system","Value":"utf8"}
{"Variable_name":"character_sets_dir","Value":"\/usr\/share\/mysql\/charsets\/"}



{"Variable_name":"collation_connection","Value":"latin1_swedish_ci"}
{"Variable_name":"collation_database","Value":"utf8_general_ci"}
{"Variable_name":"collation_server","Value":"utf8_general_ci"}

最初我尝试添加到 my.cnf

[client]
default-character-set=utf8

它不起作用。

所以解决方案是添加:

charset=UTF8

在PDO初始化

之后我得到了正确的变量值:

{"Variable_name":"character_set_client","Value":"utf8"}
{"Variable_name":"character_set_connection","Value":"utf8"}
{"Variable_name":"character_set_database","Value":"utf8"}
{"Variable_name":"character_set_filesystem","Value":"binary"}
{"Variable_name":"character_set_results","Value":"utf8"}
{"Variable_name":"character_set_server","Value":"utf8"}
{"Variable_name":"character_set_system","Value":"utf8"}
{"Variable_name":"character_sets_dir","Value":"\/usr\/share\/mysql\/charsets\/"}



{"Variable_name":"collation_connection","Value":"utf8_general_ci"}
{"Variable_name":"collation_database","Value":"utf8_general_ci"}
{"Variable_name":"collation_server","Value":"utf8_general_ci"}