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.
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.