php mysql SET NAMES 'utf8' COLLATE 'utf8_unicode_ci' 不适用于 mysqli

php mysql SET NAMES 'utf8' COLLATE 'utf8_unicode_ci' doesn't work with mysqli

提问by Davit

I am migrating my site into php mysqli from php mysql_* methods.

我正在将我的网站从 php mysql_* 方法迁移到 php mysqli。

I had following code that did the job:

我有以下代码来完成这项工作：

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

Without this query my string characters (in Georgian language) were written with question marks. For example it was written ????????? instead of ?????????

如果没有这个查询，我的字符串字符（格鲁吉亚语）是用问号写的。例如它被写成????????? 代替 ？？？？？？？？？

So since it did its job I was happy, but now I cannot do the same with mysqli.

所以既然它完成了它的工作，我很高兴，但现在我不能用 mysqli 做同样的事情。

$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci'");

Can anyone please help me out? Thanks.

任何人都可以帮我吗？谢谢。

回答by Samuel Cook

It is not recommended to use mysqli query in order to set names but rather mysqli::set_charset

不建议使用 mysqli 查询来设置名称，而是使用mysqli::set_charset

$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->set_charset("utf8");

回答by Anirudh Ramanathan

You can use mysqli_set_charset

您可以使用 mysqli_set_charset

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

这是更改字符集的首选方法。不建议使用 mysqli_query() 来设置它（例如 SET NAMES utf8）。

However, to set collation, you will still have to use the SET NAMESquery.

但是，要设置排序规则，您仍然必须使用SET NAMES查询。

回答by ro0ter

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

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

or

或者

mysqli->set_charset("utf8")

mysqli->set_charset("utf8")

回答by Fred Gandt

A PHP feature request/bug report was filed...

已提交 PHP 功能请求/错误报告...

See https://bugs.php.net/bug.php?id=52267which garnered the response from [email protected]:

参见https://bugs.php.net/bug.php?id=52267，它获得了 [email protected] 的响应：

...use mysqli_set_charset()to set the charset and then SET NAMESto change the collation.

...用于mysqli_set_charset()设置字符集，然后SET NAMES更改排序规则。

He/she also links to http://dev.mysql.com/doc/refman/5.1/en/mysql-set-character-set.html

他/她还链接到http://dev.mysql.com/doc/refman/5.1/en/mysql-set-character-set.html

This function is used to set the default character set for the current connection. The string csnamespecifies a valid character set name. The connection collation becomes the default collation of the character set. 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()

该函数用于设置当前连接的默认字符集。该字符串csname指定有效的字符集名称。连接排序规则成为字符集的默认排序规则。此函数的工作方式与SET NAMES语句类似，但也会设置 的值mysql->charset，从而影响所使用的字符集mysql_real_escape_string()

And I'll link to http://dev.mysql.com/doc/refman/5.6/en/charset-collate.htmlwhich shows how to make queries using whatever collation suits that query.

我将链接到http://dev.mysql.com/doc/refman/5.6/en/charset-collat​​e.html，它显示了如何使用适合该查询的任何排序规则进行查询。

With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements. Here are some examples:

使用 COLLATE 子句，您可以覆盖任何用于比较的默认排序规则。COLLATE 可用于 SQL 语句的各个部分。这里有些例子：

• With ORDER BY:
  SELECT k FROM t1 ORDER BY k COLLATE latin1_german2_ci;

• With AS:
  SELECT k COLLATE latin1_german2_ci AS k1 FROM t1 ORDER BY k1;

• With GROUP BY:
  SELECT k FROM t1 GROUP BY k COLLATE latin1_german2_ci;

• With aggregate functions:
  SELECT MAX(k COLLATE latin1_german2_ci) FROM t1;

• With DISTINCT:
  SELECT DISTINCT k COLLATE latin1_german2_ci FROM t1;

• With WHERE:
  SELECT * FROM t1 WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k; SELECT * FROM t1 WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

• With HAVING:
  SELECT k FROM t1 GROUP BY k HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;

• 与ORDER BY：
  SELECT k FROM t1 ORDER BY k COLLATE latin1_german2_ci;

• 与AS：
  SELECT k COLLATE latin1_german2_ci AS k1 FROM t1 ORDER BY k1;

• 与GROUP BY：
  SELECT k FROM t1 GROUP BY k COLLATE latin1_german2_ci;

• 使用聚合函数：
  SELECT MAX(k COLLATE latin1_german2_ci) FROM t1;

• 与DISTINCT：
  SELECT DISTINCT k COLLATE latin1_german2_ci FROM t1;

• 与WHERE：
  SELECT * FROM t1 WHERE _latin1 'Müller' COLLATE latin1_german2_ci = k; SELECT * FROM t1 WHERE k LIKE _latin1 'Müller' COLLATE latin1_german2_ci;

• 与HAVING：
  SELECT k FROM t1 GROUP BY k HAVING k = _latin1 'Müller' COLLATE latin1_german2_ci;

回答by teerapuch

$con=mysqli_connect("localhost","admin","1234","web");
$con->set_charset("utf8");