Clarifications needed about SET NAMES instruction and character_set variables

Question

Recently, i had the need to store emoji in some of the table in my DB (MySQL / MariaDB), so after a bit of research i've found out that the encoding i was using (UTF8) wasn't enough and i needed to move to UTF8-mb4. I then changed the charset of the table i needed to the new encoding, but clearly it wasn't enough. I changed also my connection string i use when creating a new DbConnection, specifying the encoding utf8mb4, and still i couldn't manage to save emojy. Finally i've found this answer Using utf8mb4 in MySQL , which suggested the use of SET NAMES instruction. This solved my problem, but i noticed that the instruction was active until i restarted the db service. I would like to know:

1. which exactly is the effect of using SET NAMES instruction, especially for the tables that are using the old utf8 encoding?
2. which is the proper way to use it? (In my.ini or in my code?)
3. Is there anything i need to worry about this instruction?
4. Are there other ways to set character_set_client and character_set_connection to utf8mb4?

Answer 1

1. The effect of the SET NAMES command is specified in the MySQL manual: it sets these three system variables to the specified value:

• character_set_client
• character_set_connection
• character_set_results

It can't have any effect on tables using the old utf8 collation, as emoji cannot be stored in that encoding; the data is lost. For new tables, it may have an effect on how the server interprets string literals your client sends (character_set_connection) and how emoji retrieved from the table are sent back to the client (character_set_results).

2. You should never need to use it explicitly in your code. A correct client library will set this value for you, both via the handshake response packet that the client sends, and as needed if the server ever switches to a different character set for the connection. MySqlConnector has the correct behaviour in this regard and I would recommend switching to it. (Disclaimer: lead author)

3. No, let your client library take care of it for you.

4. Yes, in the handshake response packet, but this is an implementation detail of the client library you're using. You can also set them with a statement like SET @@character_set_connection = 'utf8mb4';.