4

In MySQL, what is the difference between doing:

SET NAMES 'utf8'

And:

SET CHARACTER SET 'utf8'

I've taken a look at Connection Character Sets and Collations MySQL documentation page but I'm still a bit confused... Do both commands need to be issued in order to make MySQL UTF-8 aware? Or is SET NAMES enough?

Community
  • 1
  • 1
Alix Axel
  • 151,645
  • 95
  • 393
  • 500
  • Note that MySQL's 'utf8' is actually just a small subset of UTF-8 (up to 3 bytes). Use 'utf8mb4' instead: http://stackoverflow.com/a/24487309/664132 – basic6 Jul 10 '14 at 10:25

2 Answers2

3

SET NAMES

SET NAMES indicates what character set the client will use to send SQL statements to the server. That means that SET NAMES 'cp1251' tells the server “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client.

SET CHARACTER SET

SET CHARACTER SET is similar to SET NAMES, but sets character_set_connection and collation_connection to character_set_database and collation_database. A SET CHARACTER SET x statement is equivalent to these three statements:

SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database;

Do both commands need to be issued in order to make MySQL UTF-8 aware? Or is SET NAMES enough?

SET NAMES is enough.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • 1
    Keep in mind that `SET NAMES` doesn't notify the client lib (libmysql.dll) about the change in character encoding. This might affect the result of client lib functions like mysql\_real\_escape\_string(). If your application relies on real\_escape\_string() you should look for a function/method in your API that also takes care of the client-side aspect of the encoding. For the C API that would be http://dev.mysql.com/doc/refman/5.0/en/mysql-set-character-set.html , for php-mysql http://docs.php.net/mysql_set_charset – VolkerK Jan 30 '10 at 15:24
  • @VolkerK: Thanks for the `mysql_set_charset()` link, is there a PDO equivalent? – Alix Axel Jun 06 '10 at 21:51
0

SET NAMES has encoding for connection, and SET CHARACTER SET is encoding for table. Using both command is highly recomment, to avoid terrible charter problems...