php 使用 MySQLi 设置字符集

Set character set using MySQLi

提问by sys_debug

I'm fetching data in Arabic from MySQL tables with MySQLi. So I usually use this in procedural style:

我正在使用 MySQLi 从 MySQL 表中获取阿拉伯语数据。所以我通常以程序风格使用它：

mysql_query("SET NAMES 'utf8'"); 
mysql_query('SET CHARACTER SET utf8'); 

Now I am using the OOP style so I am trying to see if there is something I could set rather than the above?

现在我正在使用 OOP 风格，所以我想看看是否有我可以设置的东西而不是上面的东西？

I only found this in PHP manual so I did it, but what about setting names to UTF8?

我只在 PHP 手册中找到了这个，所以我做到了，但是将名称设置为 UTF8 怎么样？

$mysqli->set_charset("utf8");

回答by xdazz

It's the same:

一样的：

$mysqli->query("SET NAMES 'utf8'");

$mysqli->query("SET NAMES 'utf8'");

From the manual:

从手册：

This is the preferred way to change the charset. Using mysqli::query() to execute SET NAMES .. is not recommended.

这是更改字符集的首选方法。不推荐使用 mysqli::query() 来执行 SET NAMES .. 。

$mysqli->set_charset("utf8");is just enough, let the mysqli db driver do the thing for you.

$mysqli->set_charset("utf8");就足够了，让 mysqli db 驱动程序为您做这件事。

回答by Mark Amery

You should use

你应该使用

$mysqli->set_charset("utf8");

Don'tuse SET NAMESor SET CHARACTER SETexplicitly when using MySQLi., and certainly don't use both like the question asker here originally was. Reasons that this is a bad idea:

在使用 MySQLi. 时不要使用SET NAMES或SET CHARACTER SET明确使用，当然也不要像这里的提问者最初那样使用两者。这是一个坏主意的原因：

• Calling SET CHARACTER SET utf8after SET NAMES utf8actually just undoessome of the work that SET NAMESdid.

• The PHP manual explicitly warns usto use mysqli_set_charset, not SET NAMES:

  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.

• Under the hood, mysqli_set_charsetis just a wrapper for mysql_set_character_setfrom the MySQL C API (or its mysqlnd equivalent). Looking at the docs for that function, we can see the difference between it and SET NAMES:

  This function works like the SET NAMESstatement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string()

  In other words, $mysqli->set_charset('foo')will do everything SET NAMES foodoes and alsoensure that mysqli_real_escape_stringrespects the new encoding. Admittedly, if you're only using encodings like Latin 1 and UTF 8 that strictly extend ASCII (that is, which encode all ASCII strings exactly as they would be encoded in ASCII), then using SET NAMESinstead of set_charsetwon't break anything. However, if you're using more unusual encodings like GBK, then you could end up garbling your strings or even introducing SQL injection vulnerabilities that bypass mysqli_real_escape_string.

• 调用SET CHARACTER SET utf8后，SET NAMES utf8实际上只是撤销了一些工作SET NAMES做。

• PHP 手册明确警告我们使用mysqli_set_charset，而不是SET NAMES：

  这是更改字符集的首选方法。不建议使用 mysqli_query() 来设置它（例如 SET NAMES utf8）。有关更多信息，请参阅 MySQL 字符集概念部分。

• 在幕后，mysqli_set_charset它只是mysql_set_character_setMySQL C API（或其 mysqlnd 等价物）的包装器。查看该函数的文档，我们可以看到它和SET NAMES：

  此函数的工作方式与SET NAMES语句类似，但也会设置 的值mysql->charset，从而影响所使用的字符集mysql_real_escape_string()

  换句话说，$mysqli->set_charset('foo')将尽一切SET NAMES foo不和也保证mysqli_real_escape_string方面的新的编码。诚然，如果您只使用像拉丁文 1 和 UTF 8 这样严格扩展 ASCII 的编码（即，对所有 ASCII 字符串进行编码，就像它们以 ASCII 编码一样），那么使用SET NAMES而不是set_charset不会破坏任何东西。但是，如果您使用更不寻常的编码（如 GBK），那么您最终可能会出现字符串乱码甚至引入绕过mysqli_real_escape_string.

It's thus good practice to only use set_charset, not SET NAMES. There's nothing that SET NAMESdoes that set_charsetdoesn't, and set_charsetavoids the risk of mysqli_real_escape_stringbehaving incorrectly (or even insecurely).

因此，最好只使用set_charset，而不使用SET NAMES。没有什么可以SET NAMES不这样set_charset做的，并且set_charset可以避免mysqli_real_escape_string行为不正确（甚至不安全）的风险。

回答by Obaidul Haque

You can use this. Its realy nice for mysqli Character Set

你可以用这个。它非常适合 mysqli 字符集

$dbhost     = "localhost";
$dbuser     = "root";
$dbpass     = "dbpass";
$dbname     = "dbname";

$conn = mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);
mysqli_query($conn,"SET CHARACTER SET 'utf8'");
mysqli_query($conn,"SET SESSION collation_connection ='utf8_unicode_ci'");

回答by AMB

For procedural style lovers.

对于程序风格爱好者。

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

/* change character set to utf8 */
mysqli_set_charset($conn,"utf8");

回答by alpc

mysqli_query($conn,"SET NAMES 'latin5'");

$conn = connection string

$conn = 连接字符串