MySQL 和 PHP:带有西里尔字符的 UTF-8 [英] MySQL and PHP: UTF-8 with Cyrillic characters
问题描述
我正在尝试在 MySQL 表中插入一个 Cyrillic 值,但是编码有问题.
PHP:
connect_error){die("连接失败:" . $conn->connect_error);}$sql = "UPDATE `c`.`mainp` SET `search` = 'test тест' WHERE `mainp`.`id` =1;";if ($conn->query($sql) === TRUE) {}$conn->close();?>
MySQL:
<代码>|身份证 |搜索 ||1 |测试ав |
注意:PHP文件为utf-8
,数据库整理utf8_general_ci
您在这里混合 API,
mysql_*
和mysqli_*
不会混合.你应该坚持使用mysqli_
(反正看起来你是这样),因为mysql_*
函数已被弃用,并在 PHP7 中完全删除.
您的实际问题是某个地方的字符集问题.这里有一些提示,可以帮助您为您的应用程序获得正确的字符集.这涵盖了开发 PHP/MySQL 应用程序时可能面临的大多数一般问题.
- 所有 整个应用程序的属性都必须设置为 UTF-8
- 将文档另存为 UTF-8 w/o BOM(如果您使用 Notepad++,则为
Format
->Convert to UTF-8 w/o BOM
) PHP 和 HTML 中的标头都应设置为 UTF-8
HTML(在
标签内):
PHP(在文件顶部,在任何输出之前):
header('Content-Type: text/html; charset=utf-8');
连接到数据库后,将连接对象的字符集设置为 UTF-8,像这样(连接后直接)
mysqli_set_charset($conn, "utf8");/* 程序方法 */$conn->set_charset("utf8");/* 面向对象的方法 */
这是用于
mysqli_*
的,mysql_*
和 PDO 也有类似的(见本答案底部).还要确保您的数据库和表设置为 UTF-8,您可以这样做:
ALTER DATABASE databasename CHARACTER SET utf8 COLLATE utf8_unicode_ci;ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;
(任何已存储的数据都不会被转换为正确的字符集,因此您需要使用干净的数据库来执行此操作,或者如果存在损坏的字符,则在执行此操作后更新数据).
- 如果您正在使用
json_encode()
,则可能需要应用JSON_UNESCAPED_UNICODE
标志,否则它会将特殊字符转换为其等效的十六进制字符.
请记住,整个代码管道中的所有都需要设置为 UFT-8,否则您的应用程序中可能会遇到损坏的字符.
除此列表外,可能还有一些函数具有用于指定字符集的特定参数.手册会告诉你这个(一个例子是htmlspecialchars()
).>
对于多字节字符也有一些特殊的函数,例如:strtolower()
不会降低多字节字符,为此你必须使用 mb_strtolower()
,请参阅此现场演示.
注意 1:注意它的某个地方被标记为 utf-8
(带破折号),而某个地方被标记为 utf8
(没有它).知道何时使用哪个很重要,因为它们通常不可互换.例如,HTML 和 PHP 需要 utf-8
,但 MySQL 不需要.
注意 2:在 MySQL 中,字符集"和排序规则"不是一回事,参见 编码和整理的区别?.不过,两者都应设置为 utf-8;通常排序规则应该是 utf8_general_ci
或 utf8_unicode_ci
,参见 UTF-8: General?斌?Unicode?a>.
注意 3:如果您使用表情符号,则需要使用 utf8mb4
字符集而不是标准的 utf8
字符集来指定 MySQL,两者都是在数据库和连接中.HTML 和 PHP 将只有 UTF-8
.
<小时>
使用 mysql_
和 PDO
PDO:这是在您的对象的 DSN 中完成的.注意
charset
属性,$pdo = new PDO("mysql:host=localhost;dbname=database;charset=utf8", "user", "pass");
mysql_
:这与mysqli_*
非常相似,但它不将连接对象作为第一个参数.mysql_set_charset('utf8');
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 ав |
Note: PHP file is utf-8
, database collation utf8_general_ci
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.
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 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 (inside
<head></head>
tags):<meta charset="UTF-8">
PHP (at the top of your file, before any output):
header('Content-Type: text/html; charset=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 */
This is for
mysqli_*
, there are similar ones formysql_*
and PDO (see bottom of this answer).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).
- If you're using
json_encode()
, you might need to apply theJSON_UNESCAPED_UNICODE
flag, otherwise it will convert special characters to their hexadecimal equivalent.
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.
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()
).
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.
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.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
orutf8_unicode_ci
, see UTF-8: General? Bin? Unicode?.Note 3: If you're using emojis, MySQL needs to be specified with an
utf8mb4
charset instead of the standardutf8
, both in the database and the connection. HTML and PHP will just haveUTF-8
.
Setting UTF-8 with mysql_
and PDO
PDO: This is done in the DSN of your object. Note the
charset
attribute,$pdo = new PDO("mysql:host=localhost;dbname=database;charset=utf8", "user", "pass");
mysql_
: This is done very similar tomysqli_*
, but it doesn't take the connection-object as the first argument.mysql_set_charset('utf8');
这篇关于MySQL 和 PHP:带有西里尔字符的 UTF-8的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!