Set Names Utf8 in MySQL

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.

mysql_query(SET NAMES 'UTF8'); to solve '???' character

Question closer as per my comment:

You've tried mysql_query("SET NAMES 'UTF8'");

But you see, mysql_ and mysqli_ functions do not mix together.

Try/use $mysqli->set_charset("utf8"); instead just before your query.

MySQL 'set names latin1' seems to cause data to be stored as utf8

1. SET NAMES latin1 declares that the encoding in your client is latin1.
2. But (apparently) it is actually utf8.
3. So, when you type é, the client generates the 2 bytes C3 A9.
4. Then those are sent as if they were latin1 to the server (mysqld).
5. The Server says "Oh, I am getting some latin1 bytes, and I will be putting them into a latin1 column, so I don't need to transform them.
6. In go two latin1 characters é (hex C3A9). This is called Mojibake.
7. If you do SET NAMES utf8 and SELECT the text, you will "see" é and it will be 4 bytes (hex C383C2A9)!

Bottom line: Your client encoding was really utf8, so you should have said SET NAMES utf8 (or utf8mb4). Confused? Welcome to the club.

php mysql SET NAMES 'utf8' COLLATE 'utf8_unicode_ci' doesn't work with mysqli

It is not recommended to use mysqli query in order to set names but rather mysqli::set_charset

$mysqli = new mysqli("localhost", "root", "", "test");
$mysqli->set_charset("utf8");

UTF-8 MySQL and Charset

I set everything to UTF-8

Not quite.

You have to tell mysql your client's encoding.

As a matter of fact, you don't have to set up "everything" in utf-8. You can have your tables in latin1 and output in utf-8. Or contrary.

Very flexible.

But you have to set up client's encoding explicitly.
So, that's why it works with set names utf8. Because this query setting up client's encoding. And let Mysql know that data must be sent in utf-8. Pretty sensible, huh?

Also I have to mention your SQL dump. It needs same setting. Just SET NAMES somewhere at the top. Because you are sending these queries from some client too. And this client's encoding needs to be set up as well.

And one more thing to mention: be sure your server sending proper encoding in the Content-type header. You didn't set it to UTF-8 too.

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.

Why does the MySQL Connection ignore the SET NAMES utf8 directive in R?

There's a handshake that happens, and the client ends up telling the MySQL to use latin1. It is possible to disable the handshake, or on initial connection SET NAMES utf8.

See:
http://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_character-set-client-handshake

PHP PDO: charset, set names?

You'll have it in your connection string like:

"mysql:host=$host;dbname=$db;charset=utf8mb4"

HOWEVER, prior to PHP 5.3.6, the charset option was ignored. If you're running an older version of PHP, you must do it like this:

$dbh = new PDO("mysql:host=$host;dbname=$db",  $user, $password);
$dbh->exec("set names utf8mb4");

---

Related Topics