Non-english characters in MySQL command-line on Windows

Question

Table was created with:

 CREATE TABLE IF NOT EXISTS `mathsqs` (
       `questions` varchar(5000) NOT NULL,
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

I have inserted data through PHP using mysqli.
To confirm the insertion, I tried SELECT * FROM mathsqs LIMIT 1 on the Windows command line. It shows question marks for non english characters.

How do I see the exact posted data in MySql command line?

Example data I'm trying to handle:

இரு எண்களின் பெருக்கல் பலன் 3375 அவ்வெண்களின் மீ.பெ.வ 15

I am sorry I did not get you correctly. When you say "It shows question marks for non english characters. I had some problem while retrieval. But data is visible while retrieving." do you mean you're able to see the data but with non-english characters replaced with question marks? can you also clarify where are you running the select query? from `MySQLi` or from somewhere else? it could be that the editor is not able to display unicode correctly but the database is holding the correct value. — Anurag, Mar 22 '16 at 06:12
@Anurag I have inserted data into the table through `mysqli php`. It works fine. No error. So I wanted to see the inserted data through `mysql command line`. It shows only question mark for tamil characters. While retrieving through PHP, I see the data as I inserted — Gibbs, Mar 22 '16 at 06:14
where you are checking by "select * from matchsqs limit 1;" first check it from db server console instead of from any gui tool...2nd thing that make sure that the value was inserted only after setting this table as utf8. — Zafar Malik, Mar 22 '16 at 06:14
even you should check "select * from matchsq order by id desc limit 1;" — Zafar Malik, Mar 22 '16 at 06:15
@ZafarMalik I am running the query from command line of Mysql. Yes Table charset is utf8 — Gibbs, Mar 22 '16 at 06:15
use $mysqli->set_charset("utf8"), and also try to use `n` while insert query ex. `insert(n'columnname')` — SagarPPanchal, Mar 22 '16 at 07:06
[Handling Unicode Front To Back In A Web App](http://kunststube.net/frontback/) — deceze, Mar 22 '16 at 09:29
!important: The problem here is not with mysql but the command line console not being able to display the unicode Tamil characters. Please read the question and comments with answer. @deceze I appreciate the S.O link you shared marking the post as duplicate. But that post doesn't cover the problem mentioned here. Displaying Tamil characters in mysql command line. — Anurag, Mar 22 '16 at 09:46
@deceze I request you to please go through both the question and posted answer and if you think the question is answered there please share your views, otherwise, this question doesn't seem like a duplicate to [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Anurag, Mar 22 '16 at 09:46
@Anurag Care to rewrite the question to make it clear it's about the command line? As is the question doesn't say that at all. Happy to reopen once it's a (somewhat) unique question. — deceze, Mar 22 '16 at 09:47
@deceze I asked the author to update it already. I am going to go ahead and update it myself. Thanks! — Anurag, Mar 22 '16 at 09:50
Oh wait! the question is updated already to mention the `MySql command line` in the description at the end. — Anurag, Mar 22 '16 at 09:52
@GopsAB please include 'MySql command-line' in the question itself. — Anurag, Mar 22 '16 at 09:54
What command line environment exactly are you working with? Windows? Linux? — deceze, Mar 22 '16 at 10:08
@deceze it is windows command-line. Launching the windows command utility with Active code page 65001 `cmd.exe /K chcp 65001` and then running mysql with default-character-set=utf8 parameter still doesn't has any effect. — Anurag, Mar 22 '16 at 10:18

Anurag · Accepted Answer · 2016-03-22T09:19:50.953

Assuming that you have already set the table char set to utf8 and it's collation is utf8.

Try adding this line just before you $mysqli command:

$mysqli->set_charset("utf8")

Also, set the utf 8 encoding in your page header where this output is coming.

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

Update:

When running the sql from command line, make sure you have set the default charset property before launching the mysql client. Something like:

Start the client with option --default-character-set=utf8.

mysql --default-character-set=utf8

To set it as a default option to be included automatically each time you run the mysql client add an entry in your my.cnf file, in the [mysql] section as:

[mysql]
default-character-set=utf8

Update #2:

@GopsAB I ran the DML statement to create the table as you specified. Followed the same process and surprisingly, I have having the same problem. I can't figure out why the question marks are displayed even after enforcing the character encoding.

So I digged further and made sure the command prompt was set to use use 'Lucidia Console' font and Active page code chcp 65001, By setting the property of the console to the use 'Lucidia Console' font and then running: chcp 65001 and followed the same process.

But now instead of '?' marks I am getting BOM character boxes....but the surprising thing is when I copy the console text that is displayed for the value of the column, I am getting the proper text: போக்குவரத்து (this is pasted directly from the console). Strange hah!

Important!

Turns out MySQL’s utf8 charset only partially implements proper UTF-8 encoding. It can only store UTF-8-encoded symbols that consist of one to three bytes; encoded symbols that take up four bytes aren’t supported.

In your case, the characters are stored perfectly and they are retrieved in php page and in mysql editors like sql workbench or Toad for SQL properly. Only the command line interface is unable to display them for some weird reason even after setting proper encoding and page type as discussed above. The text displayed in the console when copy/pasted displays correctly in notepad or any other place where you can type.

Running SET NAMES 'big5'; and SET NAMES 'utf8'; doesn't have any effect either

and neither did SET collation_connection = utf8_unicode_ci; SET NAMES utf8; did anything new but displayed only boxes which is the actual value when copy/pasted but on the console itself masked in boxes.

So until here you are good! nothing is wrong in your SQL and the values stored in the database are fine and fetched properly.

Something Extra:

MySQL’s utf8mb4

Luckily, MySQL 5.5.3 (released in early 2010) introduced a new encoding called utf8mb4 which maps to proper UTF-8 and thus fully supports Unicode, including astral symbols.

Switching from MySQL’s utf8 to utf8mb4

For each database:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

For each table:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For each column:

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a VARCHAR column.)

MySQL’s utf8mb4 Reference: How to support full Unicode in MySQL databases

Ok. I will try this. And Assume I have set names as utf8. And created table as mentioned in the question. And I run the query `INSERT INTO mathsqs(questions) VALUES('போக்குவரத்து');` . If I paste this query in mysql console, it shows only question marks for tamil characters. — Gibbs, Mar 22 '16 at 06:20
@GopsAB Check the updated answer. It will be more clear if you can update the question to mention that you're running the sql from the command line interface. — Anurag, Mar 22 '16 at 06:22
Something is missing. I set the charset as you said and verified the charset by `show variables like "character_set_database";` And I ran my insert query. It shows only question marks — Gibbs, Mar 22 '16 at 06:33
@GopsAB that is strange. I will run the query you posted and check for myself. — Anurag, Mar 22 '16 at 06:39