MySQL和MySQL:带有西里尔字母的UTF-8 [英] MySQL and PHP: UTF-8 with Cyrillic characters
问题描述
我正在尝试在MySQL表中插入西里尔字母值，但是编码存在问题.
I'm trying to insert a Cyrillic value in the MySQL table, but there is a problem with encoding.
Php:
<?php
$servername = "localhost";
$username = "a";
$password = "b";
$dbname = "c";
$conn = new mysqli($servername, $username, $password, $dbname);
mysql_query("SET NAMES 'utf8';");
mysql_query("SET CHARACTER SET 'utf8';");
mysql_query("SET SESSION collation_connection = 'utf8_general_ci';");
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "UPDATE `c`.`mainp` SET `search` = 'test тест' WHERE `mainp`.`id` =1;";
if ($conn->query($sql) === TRUE) {
}
$conn->close();
?>
MySQL:
| id | search |
| 1 | test ав |
注意:PHP文件为utf-8，数据库排序规则为utf8_general_ci
Note: PHP file is utf-8, database collation utf8_general_ci
推荐答案
您在此处混合API，
mysql_*和mysqli_*没有混合.您应该坚持使用mysqli_(看起来仍然如此)，因为不推荐使用mysql_*函数，并且在PHP7中将其完全删除了.
You are mixing APIs here,
mysql_*andmysqli_*doesn't mix. You should stick withmysqli_(as it seems you are anyway), asmysql_*functions are deprecated, and removed entirely in PHP7.
您的实际问题是某个地方的字符集问题.这里有一些指针，可以帮助您为应用程序获取正确的字符集.这涵盖了开发PHP/MySQL应用程序时可能遇到的一般问题的多数.
Your actual issue is a charset problem somewhere. Here's a few pointers which can help you get the right charset for your application. This covers most of the general problems one can face when developing a PHP/MySQL application.
-
您整个应用程序中的
- ALL 属性必须设置为UTF-8
- 将文档另存为UTF-8(不带BOM)(如果使用的是Notepad ++，则为
Format->Convert to UTF-8 w/o BOM) -
PHP和HTML中的标头都应设置为UTF-8
- ALL attributes throughout your application must be set to UTF-8
- Save the document as UTF-8 w/o BOM (If you're using Notepad++, it's
Format->Convert to UTF-8 w/o BOM) The header in both PHP and HTML should be set to UTF-8
-
HTML (在
<head></head>标记内):
<meta charset="UTF-8">
PHP (在文件顶部，未显示任何输出):
PHP (at the top of your file, before any output):
header('Content-Type: text/html; charset=utf-8');
在连接到数据库后，将连接对象的字符集设置为UTF-8，如下所示(直接在连接之后)
Upon connecting to the database, set the charset to UTF-8 for your connection-object, like this (directly after connecting)
mysqli_set_charset($conn, "utf8"); /* Procedural approach */
$conn->set_charset("utf8"); /* Object-oriented approach */
这是针对mysqli_* 的，mysql_*和PDO也有类似的内容(请参见此答案的底部).
This is for mysqli_*, there are similar ones for mysql_* and PDO (see bottom of this answer).
还要确保将数据库和表设置为UTF-8，您可以像这样进行操作:
Also make sure your database and tables are set to UTF-8, you can do that like this:
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
(任何已存储的数据都不会转换为正确的字符集，因此您需要使用干净的数据库来执行此操作，如果字符损坏则需要在执行此操作后更新数据).
(Any data already stored won't be converted to the proper charset, so you'll need to do this with a clean database, or update the data after doing this if there are broken characters).
- 如果使用的是
json_encode()，则可能需要应用JSON_UNESCAPED_UNICODE标志，否则它将把特殊字符转换为等效的十六进制字符.
- If you're using
json_encode(), you might need to apply theJSON_UNESCAPED_UNICODEflag, otherwise it will convert special characters to their hexadecimal equivalent.
请记住，整个代码管道中的 EVERYTHING 必须设置为UFT-8，否则您的应用程序中可能会遇到字符损坏的情况.
Remember that EVERYTHING in your entire pipeline of code needs to be set to UFT-8, otherwise you might experience broken characters in your application.
除此列表外，可能还有一些具有用于指定字符集的特定参数的函数.手册将告诉您这一点(示例为 htmlspecialchars() ).
In addition to this list, there may be functions that has a specific parameter for specifying a charset. The manual will tell you about this (an example is htmlspecialchars()).
还有一些针对多字节字符的特殊功能，例如:strtolower()不会降低多字节字符，因为您必须使用mb_strtolower()，请参见此
There are also special functions for multibyte characters, example: strtolower() won't lower multibyte characters, for that you'll have to use mb_strtolower(), see this live demo.
注释1 :请注意，它的某处标记为
utf-8(带破折号)，而某处标记为utf8(不带破折号).重要的是要知道什么时候使用，因为它们通常是不可互换的.例如，HTML和PHP想要utf-8，而MySQL不需要.
Note 1: Notice that its someplace noted as
utf-8(with a dash), and someplace asutf8(without it). It's important that you know when to use which, as they usually aren't interchangeable. For example, HTML and PHP wantsutf-8, but MySQL doesn't.
注释2 :在MySQL中，字符集"和排序规则"不是同一回事，请参见 UTF-8:常规?斌吗Unicode吗?.
Note 2: In MySQL, "charset" and "collation" is not the same thing, see Difference between Encoding and collation?. Both should be set to utf-8 though; generally collation should be either utf8_general_ci or utf8_unicode_ci, see UTF-8: General? Bin? Unicode?.
注释3 :如果您使用表情符号，则在数据库和连接中都需要使用utf8mb4字符集而不是标准的utf8指定MySQL. HTML和PHP仅包含UTF-8.
Note 3: If you're using emojis, MySQL needs to be specified with an utf8mb4 charset instead of the standard utf8, both in the database and the connection. HTML and PHP will just have UTF-8.
使用mysql_和PDO设置UTF-8
Setting UTF-8 with mysql_ and PDO
-
PDO:这是在对象的DSN中完成的.请注意
charset属性
$pdo = new PDO("mysql:host=localhost;dbname=database;charset=utf8", "user", "pass");
mysql_:这样做与mysqli_*非常相似，但是它没有将连接对象作为第一个参数.
mysql_: This is done very similar to mysqli_*, but it doesn't take the connection-object as the first argument.
mysql_set_charset('utf8');
这篇关于MySQL和MySQL:带有西里尔字母的UTF-8的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
