Php + Mysql (UTF-8)一些字符仍然是错误的。

Well i got a php script that takes nicknames from a the Steam web-api and insert them into a mysql db. Many of them got rare russian and greek characters. I set php to utf-8 in the php.ini and in all the php files with

我有一个php脚本，它从一个蒸汽web api中获取昵称，并将它们插入到一个mysql数据库中。他们中的许多人都有罕见的俄罗斯和希腊字母。在php中，我将php设置为utf-8。在所有php文件中。

mb_internal_encoding('utf-8');

My PDO connector is configured to handle utf8

我的PDO连接器配置为处理utf8。

$connection = new PDO('mysql:host=localhost;dbname=d2bd;mysql:charset=utf8mb4', 'root', '');
$connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$connection->setAttribute(PDO::ATTR_PERSISTENT, true);
$connection->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'");

my mysql db is properly configured with utf8mb4

我的mysql db被正确配置为utf8mb4。

character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8
character_sets_dir C:\xampp\mysql\share\charsets\
collation_connection utf8mb4_unicode_ci
collation_database utf8mb4_unicode_ci
collation_server utf8mb4_unicode_ci
completion_type NO_CHAIN
concurrent_insert AUTO
connect_timeout 10
core_file OFF

In few words i take the input of the web-api and encode it with uft8_encode(). Then i insert it into the db. The problem is that some characters are not well encoded and when i recall them from the database they are all bugged.

我很少使用web api的输入，并用uft8_encode()对其进行编码。然后把它插入到db中。问题是有些字符没有很好的编码，当我从数据库中回忆它们时，它们都被窃听了。

Example 1:

示例1:

1.Input -> Перуанский чертовски

1。输入- >Перуанскийчертовски

2.Encode -> ÐеÑÑанÑкий ÑеÑÑовÑки

2。编码- >ÐеNNанNкийNеNNовNки

3.Insert into DB

3所示。插入数据库

4.Select from DB -> Ð?еÑ?Ñ?анÑкий Ñ?еÑ?Ñ?овÑкÐ

4所示。从DB - >选择ÐеN ? N ?анNкийN ?еN ? N ?овNкÐ

5.Decode

5.解码

6.Output -> �?е�?�?анский �?е�?�?овск�

6。输出- >�?е�?�?еанский�?�?�?овск�

Example 2:

示例2:

1.Input -> $ |/| 1 ↓_ € ♥ J

1。输入-> $ |/| 1

2.Encode -> $ |/| 1 â_ ⬠⥠J

2。编码- > | / | 1美元现代¬¥J

3.Insert into DB

3所示。插入数据库

4.Select from DB -> 1 â??_ â?¬ â?¥ J

4所示。从DB -> 1 a中选择??_一个吗?a¬?¥J

5.Decode

5.解码

6.Output -> 1 �??_ �?� �?� J

6。输出- > 1�? ?_�?��?J�

2 个解决方案

#1

Checklist for Problems with character/charset/collation

Including mysql, mysqli, PDO

---

Content

1. DISCLAIMER
2. 免责声明
3. My insert's in my DB doesn't work properly! What can i do?
4. 我的插入在数据库中不能正常工作!我能做什么?
5. Change Charset and Collation of a Database or Table
6. 更改数据库或表的字符集和排序。
7. Set the encoding of your skript files
8. 设置skript文件的编码。
9. Set the charset of your page with php or meta tag
10. 用php或元标签设置页面的charset。
11. What's the difference between UTF8 and UTF8mb4?
12. UTF8和UTF8mb4有什么区别?
13. Answer to this specific Question
14. 回答这个具体的问题。
15. Further Information/Additional Links
16. 进一步的信息/额外的链接
17. Side Notes
18. 一边笔记

1. DISCLAIMER

This Answer should not only answer this question, also should the answer be a bit more extensive, so more people find faster a bundled and good answer!

这个答案不仅应该回答这个问题，也应该更广泛一些，让更多的人找到更快的捆绑和好的答案!

!Important Notice!
If you change something in your Database always make sur you have a backup of your database! Check it 2 times, or 3!

重要通知!如果你在你的数据库中改变了一些东西，那就一定要备份你的数据库!检查它2次，或者3次!

I'm open for improvements and comments, such as error corrections.
In addition I apologize if the grammar is not perfect: D

我对改进和评论持开放态度，比如错误更正。另外，如果语法不完美，我道歉。

If you get stuck on a question like this:

如果你遇到这样的问题:

• Php + Mysql (UTF-8, utf8mb4) some characters are still bug
• Php + Mysql (UTF-8, utf8mb4)一些字符仍然是错误的。
• How to convert an entire MySQL database characterset and collation to UTF-8?
• 如何将整个MySQL数据库字符集和排序转换为UTF-8?
• “Incorrect string value” when trying to insert UTF-8 into MySQL
• 当试图将UTF-8插入MySQL时，“字符串值不正确”。
• Change MySQL default character set to UTF-8 in my.cnf?
• 在my.cnf中，将MySQL默认字符集设置为UTF-8 ?
• Using utf8mb4 with php and mysql
• 使用utf8mb4使用php和mysql。
• PDO + MySQL and broken UTF-8 encoding
• PDO + MySQL和破解UTF-8编码。
• Error in insertion data in php Mysql
• 在php Mysql中插入数据出错。
• PHP PDO: charset, set names?
• PHP PDO: charset，设置名称?
• SET NAMES utf8 in MySQL?
• 在MySQL中设置utf8名称?
• PHP mysql charset utf8 problems
• PHP mysql charset utf8问题。
• UTF-8 all the way through
• UTF-8一直通过。
• Manipulating utf8mb4 data from MySQL with PHP
• 用PHP操作utf8mb4数据。
• ERROR 1115 (42000) : Unknown character set: 'utf8mb4' in mysql
• 错误1115(42000):未知字符集:“utf8mb4”在mysql中。

...then my answer maybe helps you!

…那么我的回答也许对你有帮助!

2. My insert's in my DB doesn't work properly! What can i do?

If your insert's doesn't work properly an your inserted data looks something like this in your database then this could have various reasons!

如果插入的数据不能正常工作，那么在数据库中插入的数据看起来就像这样，这可能有各种原因!

Examples:

例子:

??????????
????????????????
�??_ �?�
â_ ⬠⥠J

Here is a little checklist you can go trought and check if everything is how it should be!
(After the checklist there a few extra informations for mysql, mysqli and PDO)

这里有一个小清单，你可以去看看是否一切都是这样!(核对表后，mysql、mysqli和PDO提供了一些额外信息)

Checklist:

检查表:

• Make sure default character sets is set on tables, client, server & text fields
  • If NOT See Point 3
  • 如果没有看到点3。
• 确保默认字符集设置在表、客户端、服务器和文本字段中，如果没有看到第3点。
• Make sure your database connections character sets
  • IF NOT See Point mysql/PDO
  • 如果没有看到Point mysql/PDO。
• 如果没有看到Point mysql/PDO，请确保数据库连接字符集。
• Make sure if your displaying data that the charset of the document is set!
  • IF NOT See Point 5
  • 如果没有看到点5。
• 请确保您的显示数据是否设置了文档的字符集!如果没有看到点5。
• Make sure your skript files are saved with the right charset!
  • IF NOT See Point 4
  • 如果没有看到点4。
• 确保您的skript文件使用正确的字符集保存!如果没有看到点4。
• Make sure you set your character and your charset!
  • IF NOT See Point mysql/PDO
  • 如果没有看到Point mysql/PDO。
• 确定你的角色和你的字符集!如果没有看到Point mysql/PDO。
• Make sure you forms accept utf8!
  • IF NOT See Point 5
  • 如果没有看到点5。
• 确保你的表格接受utf8!如果没有看到点5。
• Make sure you have set the connection encoding
  • IF NOT See Point mysql/pdo
  • 如果没有看到Point mysql/pdo。
• 如果没有看到Point mysql/pdo，请确保设置了连接编码。
• Make sure you have set the servercharacter encoding right
  • IF NOT See Point mysql/pdo
  • 如果没有看到Point mysql/pdo。
• 如果没有看到Point mysql/pdo，请确保设置了servercharacter编码。

• ...

  …

• You have to be sure your using utf8/ utf8mb4 everywhere!

  你必须确保你在任何地方都使用utf8/ utf8mb4 !

mysql:

mysql:

-mysql_query("SET NAMES 'utf8'"); Run SET NAMES before every query you use. Because if a mysql driver don't provied mechanismus to charset then you have to use SET NAMES!
-mysql_query("SET CHARACTER SET utf8 "); Set character to utf8
-mysql_set_charset('utf8'); Set your charset to utf8
-mysql API driver doesn't support utf8mb4 (ERROR 1115 (42000))
-character_set_server=utf8 to set server character

mysql_query(use utf8”“集名称);在您使用的每个查询之前运行集合名称。因为如果一个mysql驱动程序没有提供给charset的机制，那么你就必须使用集合名称!-mysql_query(“SET CHARACTER SET utf8”);将字符设置为utf8 -mysql_set_charset('utf8');将charset设置为utf8 -mysql API驱动程序不支持utf8mb4(错误1115 (42000))-character_set_server=utf8来设置服务器字符。

PDO:

PDO:

-$dbh->exec("set names utf8"); If your using PDO you can use this line to SET NAMES
-$dbh = new PDO("mysql:host=$host;dbname=$db;charset=utf8"); This line set the charset but you have to have PHP 5.3.6 or higher
-$dbh->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' "); You can also set SET NAMES with this line
-mb_internal_encoding('UTF-8'); to set the encoding when you use PDO

- $ dbh - > exec(“名字utf8 ");如果您使用PDO，您可以使用这一行来设置名称——$dbh = new PDO(“mysql:host=$host;dbname=$db;charset=utf8”);这一行设置了charset，但您必须拥有PHP 5.3.6或更高-$dbh->setAttribute(PDO: MYSQL_ATTR_INIT_COMMAND，“设置名称”utf8mb4“COLLATE”utf8mb4_unicode_ci”);您还可以使用这一行-mb_internal_encoding('UTF-8')设置设置名称;在使用PDO时设置编码。

3. Change Charset and Collation of a Database or Table

If you have to change the charset or collation of a database or table you can use these lines of code:

如果您必须更改数据库或表的charset或collation，您可以使用以下代码行:

ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

4. Set the encoding of your skript files

You may have to check that your skript(php) files are saved with the right charset!

您可能需要检查您的skript(php)文件是否使用正确的字符集保存!

For this i would recommend you Notpad++!

为此，我建议你不要使用“pad++”!

If you have opened your file in notpad go to the menupoint 'Encoding' and change the charset

如果你在notpad中打开了你的文件，去menupoint '编码'并修改字符集。

5. Set the charset of your page with php or meta tag

For displaying data in utf8/utf8mb4 you have to be sure you site is set with the right charset!

要在utf8/utf8mb4中显示数据，您必须确定您的站点是用正确的字符集设置的!

You can set the charset in 3 ways like this:

你可以用三种方式设置charset:

//PHP
ini_set("default_charset", "UTF-8");
header('Content-type: text/html; charset=UTF-8');

//HTML
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Also to accept utf8 in your form use:

也可以接受utf8在您的表格使用:

<form accept-charset="UTF-8">

6. What's the difference between UTF8 and UTF8mb4?

UTF8:
-utf8 does only support symbols with 3 bytes
-...(many more)

UTF8: - UTF8只支持带有3个字节的符号……(更多)

UTF8MB4:
-utf8mb3 does support symbols with 4 bytes
-...(many more)

UTF8MB4: -utf8mb3支持4字节的符号-…(更多)

7. Answer to this specific Question

I think this should work since your using PDO:
(After you created a PDO object! If your using a PHP version less then 5.3.6)

我认为这应该起作用，因为您使用PDO:(创建了PDO对象之后!如果您使用的是PHP版本，则为5.3.6。

$dbh->exec("set names utf8");

Otherwise try one of these:

否则，试试下面这些:

ini_set("default_charset", "UTF-8");
header('Content-type: text/html; charset=UTF-8');

UPDATE:

更新:

To change the collation or charset of a database or table use this:

更改数据库或表的排序规则或字符集:

ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

8. Further Information/Additional Links

• default character set
• 默认字符集
• character set
• 字符集
• mysql_set_charset
• mysql_set_charset
• error_reporting
• error_reporting
• pdo
• pdo
• mysql
• mysql
• mysqli
• mysqli

9. Side Notes

9.1 Error Reporting
If Error's not get displayed use this code snippet:

9.1错误报告如果没有显示错误，请使用以下代码片段:

<?php
    error_reporting(E_ALL);
    ini_set("display_errors", 1);
?>

9.2 Unicode
So that you don't make any mistake you have to really understand utf8!

9.2 Unicode，这样你就不会犯任何错误，你必须真正理解utf8!

9.3 One word to mysql, mysqli and PDO
My Personal ranking is:

9.3一个词到mysql, mysqli和PDO我的个人排名是:

1. PDO
   
2. PDO
3. mysqli
   
4. mysqli
5. mysql
   
6. mysql

I would recommend you to use PDO or mysqli, because the have many benefits against mysql!

我建议您使用PDO或mysqli，因为对mysql有很多好处!

#2

I changed the collation of the tables from SQLyog, but it seems that it's broken. When i changed them directly from a sql query it worked.

我从SQLyog改变了表格的整理，但是看起来好像坏了。当我直接从它工作的sql查询中更改它们时。