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

问题

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

I had following code that did the job:

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 = new mysqli("localhost", "root", "", "test");

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

Can anyone please help me out? Thanks.

回答1:

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

$mysqli = new mysqli("localhost", "root", "", "test");

$mysqli->set_charset("utf8");

回答2:

You can use 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.

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

回答3:

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

or

mysqli->set_charset("utf8")

回答4:

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

See https://bugs.php.net/bug.php?id=52267 which garnered the response from uw@php.net:

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

He/she also links to 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 csname specifies a valid character set name. The connection collation becomes the default collation of the character set. This function works like the SET NAMES statement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string()

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

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:

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;

回答5:

$con=mysqli_connect("localhost","admin","1234","web");

$con->set_charset("utf8");

来源：https://stackoverflow.com/questions/13433734/php-mysql-set-names-utf8-collate-utf8-unicode-ci-doesnt-work-with-mysqli