user187291 user187291 - 4 months ago 7
PHP Question

Whether to use "SET NAMES"

In reading "High performance MySQL" from O'Reilly I've stumbled upon the following


Another common garbage query is SET
NAMES UTF8, which is the wrong way to
do things anyway (it does not change
the client library's character set; it
affects only the server).


I'm a bit confused, because I used to put "SET NAMES utf8" on the top of every script to let the db know that my queries are utf8 encoded.

Can anyone comment the above quote, or, to put it more formally, what are your suggestions / best practices to ensure that my database workflow is unicode-aware.

My target languages are php and python if this is relevant.

Answer

mysql_set_charset() would be an option - but an option limited to the ext/mysql. For ext/mysqli it is mysqli_set_charset and for PDO::mysql you need to specify a connection parameter.

As using this function results in a MySQL API call, it should be considered much faster than issuing a query.

In respect of performance the fastest way to ensure a UTF-8-based communiction between your script and the MySQL server is setting up the MySQL server correctly. As SET NAMES x is equivalent to

SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;

whereas SET character_set_connection = x internally also executes SET collation_connection = <<default_collation_of_character_set_x>> you can also set these server variables statically in your my.ini/cnf.

Please be aware of possible problems with other applications running on the same MySQL server instance and requiring some other character set.

Comments