php 在 MySQL 中设置名称 utf8？

SET NAMES utf8 in MySQL?

提问by JasonDavis

I often see something similar to this below in PHP scripts using MySQL

我经常在使用 MySQL 的 PHP 脚本中看到类似下面的内容

query("SET NAMES utf8");   

I have never had to do this for any project yet so I have a couple basic questions about it.

我从来没有为任何项目做过这个，所以我有几个基本的问题。

1. Is this something that is done with PDO only?
2. If it is not a PDO specific thing, then what is the purpose of doing it? I realize it is setting the encoding for mysql but I mean, I have never had to use it so why would I want to use it?

1. 这是仅使用 PDO 完成的事情吗？
2. 如果它不是 PDO 特定的东西，那么这样做的目的是什么？我意识到它正在为 mysql 设置编码，但我的意思是，我从来没有使用过它，所以我为什么要使用它？

采纳答案by Vinko Vrsalovic

It is needed whenever you want to send data to the server having characters that cannot be represented in pure ASCII, like '?' or '?'.

每当您想将数据发送到具有无法用纯 ASCII 表示的字符（如“？”）的服务器时，都需要它。或者 '？'。

That if the MySQL instance is not configured to expect UTF-8 encoding by default from client connections (many are, depending on your location and platform.)

如果 MySQL 实例未配置为默认来自客户端连接的 UTF-8 编码（许多是，取决于您的位置和平台。）

Read http://www.joelonsoftware.com/articles/Unicode.htmlin case you aren't aware how Unicode works.

如果您不了解 Unicode 的工作原理，请阅读http://www.joelonsoftware.com/articles/Unicode.html。

Read Whether to use "SET NAMES"to see SET NAMES alternatives and what exactly is it about.

阅读是否使用“SET NAMES”以查看 SET NAMES 替代方案以及它到底是关于什么的。

回答by karim79

From the manual:

从手册：

SET NAMES indicates what character set the client will use to send SQL statements to the server.

SET NAMES 指示客户端将使用什么字符集向服务器发送 SQL 语句。

More elaborately, (and once again, gratuitously lifted from the manual):

更详细地说，（再一次，从手册中无缘无故地解除了）：

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)

SET NAMES 指示客户端将使用什么字符集向服务器发送 SQL 语句。因此，SET NAMES 'cp1251' 告诉服务器，“来自这个客户端的未来传入消息在字符集 cp1251 中。” 它还指定服务器用于将结果发送回客户端的字符集。（例如，如果您使用 SELECT 语句，它会指示用于列值的字符集。）

回答by Ondra ?i?ka

Getting encoding right is really tricky - there are too many layers:

正确编码真的很棘手 - 层数太多：

• Browser
• Page
• PHP
• MySQL

• 浏览器
• 页
• PHP
• MySQL

The SQL command "SET CHARSET utf8" from PHP will ensure that the client side (PHP) will get the data in utf8, no matter how they are stored in the database. Of course, they need to be stored correctly first.

PHP 的 SQL 命令“SET CHARSET utf8”将确保客户端 (PHP) 将获取 utf8 中的数据，无论它们如何存储在数据库中。当然，它们首先需要正确存储。

DDL definition vs. real data

DDL 定义与真实数据

Encoding defined for a table/column doesn't really mean that the data are in that encoding. If you happened to have a table defined as utf8but stored as differtent encoding, then MySQL will treat them as utf8and you're in trouble. Which means you have to fix this first.

为表/列定义的编码并不意味着数据采用该编码。如果您碰巧有一个表定义为utf8但存储为不同的编码，那么 MySQL 会将它们视为utf8并且您有麻烦。这意味着你必须先解决这个问题。

What to check

检查什么

You need to check in what encoding the data flow at each layer.

您需要检查每层数据流的编码方式。

• Check HTTP headers, headers.
• Check what's really sent in body of the request.
• Don't forget that MySQL has encoding almost everywhere:
  • Database
  • Tables
  • Columns
  • Server as a whole
  • Client
    Make sure that there's the right one everywhere.

• 检查 HTTP 标头、标头。
• 检查请求正文中真正发送的内容。
• 不要忘记 MySQL 几乎到处都有编码：
  • 数据库
  • 表
  • 列
  • 服务器整体
  • 客户
    确保到处都有合适的人。

Conversion

转换

If you receive data in e.g. windows-1250, and want to store in utf-8, then use this SQL before storing:

如果您在 eg 中接收数据windows-1250，并想存储在 中utf-8，则在存储之前使用此 SQL：

SET NAMES 'cp1250';

If you have data in DB as windows-1250and want to retreive utf8, use:

如果您在 DB 中有数据windows-1250并想要 retreive utf8，请使用：

SET CHARSET 'utf8';

Few more notes:

还有一些注意事项：

• Don't rely on too "smart" tools to show the data. E.g. phpMyAdmin does (was doing when I was using it) encoding really bad. And it goes through all the layers so it's hard to find out.
• Also, Internet Explorer had really stupid behavior of "guessing" the encoding based on weird rules.
• Use simple editors where you can switch encoding. I recommend MySQL Workbench.

• 不要依赖过于“智能”的工具来显示数据。例如 phpMyAdmin 确实（在我使用它时正在做）编码非常糟糕。它贯穿了所有层，所以很难找到。
• 此外，Internet Explorer 有非常愚蠢的行为，即根据奇怪的规则“猜测”编码。
• 使用简单的编辑器，您可以在其中切换编码。我推荐 MySQL Workbench。

回答by usama sulaiman

This query should be written before the query which create or update data in the database, this query looks like :

此查询应在创建或更新数据库中的数据的查询之前编写，此查询如下所示：

mysql_query("set names 'utf8'");

Note that you should write the encode which you are using in the header for example if you are using utf-8 you add it like this in the header or it will couse a problem with Internet Explorer

请注意，您应该编写您在标头中使用的编码，例如，如果您使用的是 utf-8，则在标头中像这样添加它，否则它会导致 Internet Explorer 出现问题

so your page looks like this

所以你的页面看起来像这样

<html>
    <head>
        <title>page title</title>
        <meta charset="UTF-8" />   
    </head>
    <body>
    <?php
            mysql_query("set names 'utf8'");   
            $sql = "INSERT * FROM ..... ";  
            mysql_query($sql);
    ?>    

    </body>
</html>

回答by nurp

The solution is

解决办法是

 $conn->set_charset("utf8");

回答by user1783273

Instead of doing this via an SQL query use the php function: mysqli::set_charset mysqli_set_charset

不是通过 SQL 查询执行此操作，而是使用 php 函数：mysqli::set_charset mysqli_set_charset

Note:

This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended.

See the MySQL character set concepts section for more information.

Note:

This is the preferred way to change the charset. Using mysqli_query() to set it (such as SET NAMES utf8) is not recommended.

有关更多信息，请参阅 MySQL 字符集概念部分。

from http://www.php.net/manual/en/mysqli.set-charset.php

来自http://www.php.net/manual/en/mysqli.set-charset.php

回答by user3162905

Thanks @all!

谢谢@所有人！

don't use: query("SET NAMES utf8"); this is setup stuff and not a query. put it right afte a connection start with setCharset() (or similar method)

不要使用： query("SET NAMES utf8"); 这是设置内容而不是查询。使用 setCharset() （或类似方法）在连接开始后立即放置

some little thing in parctice:

一些小事：

status:

地位：

• mysql server by default talks latin1
• your hole app is in utf8
• connection is made without any extra (so: latin1) (no SET NAMES utf8 ..., no set_charset() method/function)

• mysql 服务器默认使用 latin1
• 你的洞应用程序是utf8
• 没有任何额外的连接（所以：latin1）（没有 SET NAMES utf8 ...，没有 set_charset() 方法/函数）

Store and read data is no problem as long mysql can handle the characters. if you look in the db you will already see there is crap in it (e.g.using phpmyadmin).

只要mysql可以处理字符，存储和读取数据就没有问题。如果您查看数据库，您将已经看到其中有废话（例如使用 phpmyadmin）。

until now this is not a problem! (wrong but works often (in europe)) ..

直到现在这都不是问题！（错误但经常工作（在欧洲））..

..unless another client/programm or a changed library, which works correct, will read/save data. then you are in big trouble!

..除非另一个客户端/程序或更改后的库，工作正常，将读取/保存数据。那你就麻烦大了！

回答by dmitry_podyachev

Not only PDO. If sql answer like '????' symbols, preset of you charset (hope UTF-8) really recommended:

不仅是 PDO。如果 sql 回答像 '????' 符号，你的字符集预设（希望UTF-8）真的推荐：

if (!$mysqli->set_charset("utf8")) 
 { printf("Can't set utf8: %s\n", $mysqli->error); }

or via procedure style mysqli_set_charset($db,"utf8")

或通过程序风格 mysqli_set_charset($db,"utf8")