MySQL 如何在 phpMyAdmin 中显示 UTF-8 字符？

How to display UTF-8 characters in phpMyAdmin?

提问by Matt McCormick

I have my database properly set to UTF-8 and am dealing with a database containing Japanese characters. If I do SELECT *... from the mysql command line, I properly see the Japanese characters. When pulling data out of the database and displaying it on a webpage, I see it properly.

我已将我的数据库正确设置为 UTF-8，并且正在处理包含日语字符的数据库。如果我从 mysql 命令行执行 SELECT *... ，我会正确地看到日语字符。从数据库中提取数据并将其显示在网页上时，我正确地看到了它。

However, when viewing the table data in phpMyAdmin, I just see garbage text. ie.

但是，在 phpMyAdmin 中查看表数据时，我只看到垃圾文本。IE。

?§?ˉ?—￥????–??????￥???§?????—￥????–?????...

?§?ˉ?—￥????–??????￥???§?????—￥????–?????...

How can I get phpMyAdmin to display the characters in Japanese?

如何让 phpMyAdmin 以日语显示字符？

The character encoding on the HTML page is set to UTF-8.

HTML 页面上的字符编码设置为 UTF-8。

Edit:

编辑：

I have tried an export of my database and opened up the .sql file in geany. The characters are still garbled even though the encoding is set to UTF-8. (However, doing a mysqldump of the database also shows garbled characters).

我已经尝试导出我的数据库并在 geany 中打开了 .sql 文件。即使编码设置为 UTF-8，字符仍然是乱码。（但是，做数据库的mysqldump也显示乱码）。

The character set is set correctly for the database and all tables ('latin' is not found anywhere in the file)

为数据库和所有表正确设置了字符集（在文件中的任何地方都找不到“拉丁”）

CREATE DATABASE `japanese` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

I have added the lines to my.cnf and restarted mysql but there is no change. I am using Zend Framework to insert data into the database.

我已将这些行添加到 my.cnf 并重新启动了 mysql，但没有任何变化。我正在使用 Zend Framework 将数据插入到数据库中。

I am going to open a bounty for this question as I really want to figure this out.

我将为此问题悬赏，因为我真的很想弄清楚这一点。

采纳答案by Matt McCormick

I did a little more googling and came across thispage

我做了更多的谷歌搜索，发现了这个页面

The command doesn't seem to make sense but I tried it anyway:

该命令似乎没有意义，但我还是尝试了：

In the file /usr/share/phpmyadmin/libraries/dbi/mysqli.dbi.lib.php at the end of function PMA_DBI_connect()just before the returnstatement I added:

在我添加PMA_DBI_connect()的return语句之前的函数末尾的文件 /usr/share/phpmyadmin/libraries/dbi/mysqli.dbi.lib.php 中：

mysqli_query($link, "SET SESSION CHARACTER_SET_RESULTS =latin1;");
mysqli_query($link, "SET SESSION CHARACTER_SET_CLIENT =latin1;");

And it works! I now see Japanese characters in phpMyAdmin. WTF? Why does this work?

它有效！我现在在 phpMyAdmin 中看到了日语字符。跆拳道？为什么这样做？

回答by timdream

Unfortunately, phpMyAdmin is one of the first php application that talk to MySQL about charset correctly. Your problem is most likely due to the fact that the database does not store the correct UTF-8 strings at first place.

不幸的是，phpMyAdmin 是第一个与 MySQL 正确讨论字符集的 php 应用程序之一。您的问题很可能是因为数据库首先没有存储正确的 UTF-8 字符串。

In order to correctly display the characters correctly in phpMyAdmin, the data must be correctly stored in the database. However, convert the database into correct charset often breaks web apps that does not aware charset-related feature provided by MySQL.

为了在phpMyAdmin中正确显示字符，数据必须正确存储在数据库中。但是，将数据库转换为正确的字符集通常会破坏不了解 MySQL 提供的与字符集相关的功能的 Web 应用程序。

May I ask: is MySQL > version 4.1? What web app is the database for? phpBB? Was the database migrated from an older version of the web app, or an older version of MySQL?

请问：MySQL > 4.1 版吗？数据库用于什么 Web 应用程序？phpBB？数据库是从旧版本的 Web 应用程序还是旧版本的 MySQL 迁移而来的？

My suggestion is not to brother if the web app you are using is too old and not supported. Only convert database to real UTF-8 if you are sure the web app can read them correctly.

如果您使用的网络应用程序太旧且不受支持，我的建议是不要兄弟。如果您确定 Web 应用程序可以正确读取它们，则仅将数据库转换为真正的 UTF-8。

---

---

Edit:

编辑：

Your MySQL is > 4.1, that means it's charset-aware. What's the charset collation settings for you database? I am pretty sure you are using latin1, which is MySQL name for ASCII, to store the UTF-8 text in 'bytes', into the database.

您的 MySQL 是 > 4.1，这意味着它可以识别字符集。您的数据库的字符集整理设置是什么？我很确定您正在使用latin1，这是 ASCII 的 MySQL 名称，将 UTF-8 文本以“字节”存储到数据库中。

For charset-insensitive clients (i.e. mysql-cli and php-mod-mysql), characters get displayed correctly since they are being transfer to/from database as bytes. In phpMyAdmin, bytes get read and displayed as ASCII characters, that's the garbage text you seem.

对于字符集不敏感的客户端（即 mysql-cli 和 php-mod-mysql），字符会正确显示，因为它们以字节形式传入/传出数据库。在 phpMyAdmin 中，字节被读取并显示为 ASCII 字符，这就是你看起来的垃圾文本。

Countless hours had been spend years ago (2005?) when MySQL 4.0 went obsolete, in many parts of Asia. There is a standard way to deal with your problem and gobbled data:

数年前（2005 年？）当 MySQL 4.0 在亚洲的许多地方过时时，已经花费了无数个小时。有一种标准方法可以处理您的问题和吞噬的数据：

1. Back up your database as .sql
2. Open it up in UTF-8 capable text editor, make sure they look correct.
3. Look for charset collation latin1_general_ci, replace latin1to utf8.
4. Save as a new sql file, do not overwrite your backup
5. Import the new file, they will now look correctly in phpMyAdmin, and Japanese on your web app will become question marks. That's normal.
6. For your php web app that rely on php-mod-mysql, insert mysql_query("SET NAMES UTF8");after mysql_connect(), now the question marks will be gone.

7. Add the following configuration my.inifor mysql-cli:

   # CLIENT SECTION
   [mysql]
   default-character-set=utf8
   # SERVER SECTION
   [mysqld]
   default-character-set=utf8
   

1. 将您的数据库备份为 .sql
2. 在支持 UTF-8 的文本编辑器中打开它，确保它们看起来正确。
3. 寻找charset collation latin1_general_ci，替换latin1为utf8。
4. 另存为新的sql文件，不要覆盖你的备份
5. 导入新文件，它们现在将在 phpMyAdmin 中正确显示，并且您的 Web 应用程序上的日语将变成问号。这是正常的。
6. 对于依赖 php-mod-mysql 的 php web 应用程序，在mysql_query("SET NAMES UTF8");之后插入mysql_connect()，现在问号将消失。

7. my.ini为mysql-cli添加如下配置：

   # CLIENT SECTION
   [mysql]
   default-character-set=utf8
   # SERVER SECTION
   [mysqld]
   default-character-set=utf8
   

For more information about charset on MySQL, please refer to manual: http://dev.mysql.com/doc/refman/5.0/en/charset-server.html

关于 MySQL 字符集的更多信息，请参考手册：http: //dev.mysql.com/doc/refman/5.0/en/charset-server.html

Note that I assume your web app is using php-mod-mysql to connect to the database (hence the mysql_connect()function), since php-mod-mysql is the only extension I can think of that still trigger the problem TO THIS DAY.

请注意，我假设您的 Web 应用程序正在使用 php-mod-mysql 连接到数据库（因此是该mysql_connect()函数），因为 php-mod-mysql 是我能想到的唯一扩展，直到今天仍然会触发问题。

phpMyAdmin use php-mod-mysqli to connect to MySQL. I never learned how to use it because switch to frameworks* to develop my php projects. I strongly encourage you do that too.

phpMyAdmin 使用 php-mod-mysqli 连接 MySQL。我从来没有学习过如何使用它，因为切换到框架 * 来开发我的 php 项目。我强烈鼓励你也这样做。

• Many frameworks, e.g. CodeIgniter, Zend, use mysqli or pdo to connect to databases. mod-mysql functions are considered obsolete cause performance and scalability issue. Also, you do not want to tie your project to a specific type of database.

• 许多框架，例如 CodeIgniter、Zend，使用 mysqli 或 pdo 连接到数据库。mod-mysql 函数被认为是过时的，会导致性能和可扩展性问题。此外，您不希望将您的项目绑定到特定类型的数据库。

回答by Naama Katiee

If you're using PDO don't forget to initiate it with UTF8:

如果您使用 PDO，请不要忘记使用 UTF8 启动它：

 $con = new PDO('mysql:host=' . $server . ';dbname=' . $db . ';charset=UTF8', $user, $pass,  array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

(just spent 5 hours to figure this out, hope it will save someone precious time...)

（只花了 5 个小时才弄明白，希望它能节省一些人宝贵的时间......）

回答by Dinaga

I had the same problem,

我有同样的问题，

Set all text/varchar collations in phpMyAdmin to utf-8 and in php files add this:

将 phpMyAdmin 中的所有 text/varchar 排序规则设置为 utf-8，并在 php 文件中添加以下内容：

mysql_set_charset("utf8", $your_connection_name);

mysql_set_charset("utf8", $your_connection_name);

This solved it for me.

这为我解决了它。

回答by ajreal

the solution for this can be as easy as :

解决这个问题的方法很简单：

1. find the phpmysqladmin connection function/method
2. add this after database is conncted $db_conect->set_charset('utf8');

1. 找到phpmysqladmin连接函数/方法
2. 在数据库连接后添加这个 $db_conect->set_charset('utf8');

回答by mazic roohi

phpmyadmin doesn't follow the MySQL connection because it defines its proper collation in phpmyadmin config file.

phpmyadmin 不遵循 MySQL 连接，因为它在 phpmyadmin 配置文件中定义了正确的排序规则。

So if we don't want or if we can't access server parameters, we should just force it to send results in a different format (encoding) compatible with client i.e. phpmyadmin

因此，如果我们不想要或无法访问服务器参数，我们应该强制它以与客户端兼容的不同格式（编码）发送结果，即 phpmyadmin

for example if both the MySQL connection collation and the MySQL charset are utf8 but phpmyadmin is ISO, we should just add this one before any select query sent to the MYSQL via phpmyadmin :

例如，如果 MySQL 连接排序规则和 MySQL 字符集都是 utf8 但 phpmyadmin 是 ISO，我们应该在通过 phpmyadmin 发送到 MYSQL 的任何选择查询之前添加这个：

SET SESSION CHARACTER_SET_RESULTS =latin1;

回答by pleerock

Here is my way how do I restore the data without looseness from latin1 to utf8:

这是我如何从 latin1 到 utf8 不松散地恢复数据的方法：

/**
     * Fixes the data in the database that was inserted into latin1 table using utf8 encoding.
     *
     * DO NOT execute "SET NAMES UTF8" after mysql_connect.
     * Your encoding should be the same as when you firstly inserted the data.
     * In my case I inserted all my utf8 data into LATIN1 tables.
     * The data in tables was like D”D?D￠D?D?D?D.
     * But my page presented the data correctly, without "SET NAMES UTF8" query.
     * But phpmyadmin did not present it correctly.
     * So this is hack how to convert your data to the correct UTF8 format.
     * Execute this code just ONCE!
     * Don't forget to make backup first!
     */
    public function fixIncorrectUtf8DataInsertedByLatinEncoding() {
        // mysql_query("SET NAMES LATIN1") or die(mysql_error()); #uncomment this if you already set UTF8 names somewhere

        // get all tables in the database
        $tables = array();
        $query = mysql_query("SHOW TABLES");
        while ($t = mysql_fetch_row($query)) {
            $tables[] = $t[0];
        }
        // you need to set explicit tables if not all tables in your database are latin1 charset
        // $tables = array('mytable1', 'mytable2', 'mytable3'); # uncomment this if you want to set explicit tables

        // duplicate tables, and copy all data from the original tables to the new tables with correct encoding
        // the hack is that data retrieved in correct format using latin1 names and inserted again utf8
        foreach ($tables as $table)  {
            $temptable = $table . '_temp';
            mysql_query("CREATE TABLE $temptable LIKE $table") or die(mysql_error());
            mysql_query("ALTER TABLE $temptable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci") or die(mysql_error());
            $query = mysql_query("SELECT * FROM `$table`") or die(mysql_error());
            mysql_query("SET NAMES UTF8") or die(mysql_error());
            while ($row = mysql_fetch_row($query)) {
                $values = implode("', '", $row);
                mysql_query("INSERT INTO `$temptable` VALUES('$values')") or die(mysql_error());
            }
            mysql_query("SET NAMES LATIN1") or die(mysql_error());
        }

        // drop old tables and rename temporary tables
        // this actually should work, but it not, then
        // comment out this lines if this would not work for you and try to rename tables manually with phpmyadmin
        foreach ($tables as $table)  {
            $temptable = $table . '_temp';
            mysql_query("DROP TABLE `$table`") or die(mysql_error());
            mysql_query("ALTER TABLE `$temptable` RENAME `$table`") or die(mysql_error());
        }
        // now you data should be correct

        // change the database character set
        mysql_query("ALTER DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci") or die(mysql_error());

        // now you can use "SET NAMES UTF8" in your project and mysql will use corrected data
    }

回答by HaMid Ali

Change latin1_swedish_cito utf8_general_ciin phpmyadmin->table_name->field_name

更改latin1_swedish_ci为utf8_general_ciphpmyadmin->table_name->field_name

This is where you find it on the screen:

这是您在屏幕上找到它的位置：

回答by codemonkey

The function and file names don't match those in newer versions of phpMyAdmin. Here is how to fix in the newer PHPMyAdmins:

函数名和文件名与较新版本的 phpMyAdmin 中的不匹配。以下是在较新的 PHPMyAdmins 中修复的方法：

1. Find file: phpmyadmin/libraries/DatabaseInterface.php

2. In function: public function query

3. Right after the opening {add this:

   if($link != null){
       mysqli_query($link, "SET SESSION CHARACTER_SET_RESULTS =latin1;");
       mysqli_query($link, "SET SESSION CHARACTER_SET_CLIENT =latin1;");
   }
   

1. 查找文件： phpmyadmin/libraries/DatabaseInterface.php

2. 在功能上： public function query

3. 在开场之后{添加以下内容：

   if($link != null){
       mysqli_query($link, "SET SESSION CHARACTER_SET_RESULTS =latin1;");
       mysqli_query($link, "SET SESSION CHARACTER_SET_CLIENT =latin1;");
   }
   

That's it. Works like a charm.

就是这样。奇迹般有效。

回答by user3773240

Its realy simple to add multilanguage in myphpadmin if you got garbdata showing in myphpadmin, just go to myphpadmin click your database go to operations tab in operation tab page see collation section set it to utf8_general_ci, after that all your garbdata will show correctly. a simple and easy trick

如果在myphpadmin中显示garbdata，在myphpadmin中添加多语言真的很简单，只需转到myphpadmin单击您的数据库转到操作选项卡页面中的操作选项卡，查看整理部分将其设置为utf8_general_ci，之后您的所有garbdata都会正确显示。一个简单易行的技巧