Whether to Use "Set Names"

Whether to use SET NAMES

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.

Difference between SET NAMES and SET CHARSET

Here's a summary of what variables are set by each statement:

Variable                 SET NAMES   SET CHARSET
character_set_client     argument    argument
character_set_results    argument    argument
character_set_connection argument    default for default db
collation_connection     argument*   default for default db

So the biggest difference is that SET CHARSET doesn't set the connection charset to the charset you specify in the argument, instead it sets it to the charset for your current default database, that is if you have done USE <database>.

Why do both statements exist? I don't know, I checked the manual all the way back to MySQL 4.1 and both statements are there, but with no explanation for why they needed two statements.

I would guess it's for compatibility with other SQL implementations.

SET NAMES utf8 in MySQL?

It is needed whenever you want to send data to the server having characters that cannot be represented in pure ASCII, like 'ñ' or 'ö'.

That if the MySQL instance is not configured to expect UTF-8 encoding by default from client connections (many are, depending on your location and platform.)

Read http://www.joelonsoftware.com/articles/Unicode.html in case you aren't aware how Unicode works.

Read Whether to use "SET NAMES" to see SET NAMES alternatives and what exactly is it about.

Ignore safety check when using setnames

+1 Now fixed in v1.8.11. From NEWS :

setnames(DT,"oldname","newname") no longer complains about duplicated
column names in DT so long as oldname is unique and unambiguous.
Thanks to Wet Feet for highlighting.

mysql: SET NAMES utf8 on each connection?

you can modify mysql config(my.cnf)

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8

Working with SET NAMES utf8mb4 with utf8 tables

I think there should no problem using SET NAMES utf8mb4 for all connections.

(utf8mb3 is a synonym of utf8 in MySQL; I'll use the former for clarity.)

utf8mb3 is a subset of utf8mb4, so your client's bytes will be happy either way (except for Emoji, which needs utf8mb4). When the bytes get to (or come from) a column that is declared only there will be a check to verify that you are not storing Emoji or certain Chinese characters, but otherwise, it goes through with minimal fuss.

I suggest

 ALTER TABLE ... CONVERT TO utf8mb4

as the 'right' way to convert a table. However, it converts all varchar/text columns. This may be bad...

If you JOIN a converted table to an unconverted table, then you will be trying to compare a utf8mb3 string to a utf8mb4 string. MySQL will throw up its hands and convert all rows from one to the other. That is no INDEX will be useful.

So... Be sure to at least be consistent about any columns that are involved in JOINs.

SET NAMES command fails with access denied

Setting the php file to UTf-8 seemed to work.

MySQL SET NAMES near the top of the slow query log

If all queries are slow, SET NAMES can have a very low priority on the server, and therefore wait until the load dies down. If you have a lot of other queries that take a long time executing, you might want to try to optimize those first.

Another solution to this "problem", could be adding this to your my.cnf on the server:

[mysqld]
init-connect = 'SET NAMES utf8'

This makes sure the character set is set to UTF-8 when a client connects, so the client does not have to wait for the result of this "query". You probably want to disable the SET NAMES query in your software.

Mainly, I wouldn't worry too much unless you really have A LOT of SET NAMES that takes this long.

---

Related Topics