MySQL won't store non-english characters

Question

Ok, I asked this question but the solutions didn't solve this problem.

Basically, my database stores ONLY English characters, even if its fields are set to UTF-8 unicode ci mode.

I tried storing Russian letters, it prints out weird �и � characters.

Then I looked at PhpMyAdmin, those weird characters looked like this:

гк

HOWEVER, When I store something right from PhpMyAdmin, the Russian letters look fine. Yet, my page prints out ????? - question marks.

What is happening??? How to store and display different languages?

Answer 1

Someone is already answered. Here

You need to change collation to "utf8mb4_unicode_ci".

Example database query

ALTER TABLE `table_name` CHANGE `column_name` `column_name` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

Answer 2

This was my problem too and I finally found the way to solve it. I'm not a skilled coder but I write the answer here so that it may be helpful to those who just like me, didn't manage to solve their issue using other ways they have tried.

In my opinion, in general, you always have to make sure you've done these three things first:

1- Add this tag to each of your HTML sources that uses some text with a language other than English(add it between the opening and closing head tags):

<meta charset="utf8"/>

(As you see it's a tag without a closing pair)

2- Just after where you connect to your database (for example using a mysqli object called $conn), do this query too:

$conn->query("SET NAMES 'utf8'");

(At least this worked for my own language which is Farsi)

3- Set the collation of the tables you make to "utf8-general-ci" from scratch(...and I emphasize on this "from scratch". If you want to know the reason, read the rest of my answer below!):

Of course all these three things I've said above are important, but none of them helped myself when I was wrestling with this awkward disaster! :( So I was getting disappointed, but then as always this SO page showed up! :) I read the brilliant article suggested by @deceze which is in the comments below the post, and saw these lines in it:

The database server has a default character set, a database can have a default character set, a database table can have a default character set and finally the column has a character set setting. The rule is simple: if no explicit character set is specified for a column, the next higher default is used for it. The server, database and table defaults all have no influence whatsoever if the column has an explicit character set.

Actually, my silly problem was that I had made the table earlier(with a unicode other than the utf8) and then when trying to change its unicode to utf8, it didn't seem to work, because as the article says, even the collation of a table is separate from the collation of every single one of its fields, so when you change one, others tend to keep their default collations(the one you chose when you were making the table from scratch!) Finally, I went to structure tab in phpmyadmin, and changed the collation of those fields which were still using latin1 to utf8-general-ci too. (ci here stands for Case Insensitive)

I hope it helps.

Answer 3

First, make sure you're working with the same charset in all the process, by this I mean, form, process, and db.

I'd recommend working with charset=utf-8 in the meta tag in the head of your HTML.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Finally, don't forget to set your text fields to:

utf8_unicode_ci

I hope this works for you. Let me know if you need more info.