why MySQL query shows results both for Ù† and Ú† when I call Ú†?

Question

I have a database table with a column where I categorized Persian alphabetic letters to select with MySQL WHERE later. everything works fine for all letters, but I have a problem while selecting letter (چ) which is stored as (Ù†) in database and (ن) which is stored as (Ú†).
first I thought the problem could be from inserting same letters, but when I checked in database , letters where stored with different encoding I mean (Ù†) and (Ú†).
when I zoom in these letters the tick over U is different. both letters are echoed correctly when I echo them on webpage, but when I choose to select letters WHERE letter = 'چ' it shows letters with (ن) too!!!
all of the webpages that insert and read data from DB are in UTF-8 and database collation is utf_persian-ci.
I cant find where the problem is with this? any help is appreciated,

Answer 1

This is a typical result of using a 'locale specific unicode encoding', in your case utf8_persian_ci. I expect that if you switch your collation to utf8_unicode_ci, it will work as expected.

If by any change you want to get rid of the case-insensitivity, you could switch to utf8_bin.

For further reference see the MySQL documentation.

Answer 2

Mojibake. (or not; see below) Probably:

• The bytes you have in the client are correctly encoded in utf8 (good).
• You connected with SET NAMES latin1 (or set_charset('latin1') or ...), probably by default. (It should have been utf8.)
• The column in the tables may or may not have been CHARACTER SET utf8, but it should have been that.

For PHP:

⚈  mysqli interface: mysqli_set_charset('utf8') function.
⚈  PDO interface: set the charset attribute of the PDO dsn or via SET NAMES utf8.

The COLLATION (eg, utf8_persion_ci) is not relevant to Mojibake. It is relevant to how characters are ordered.

Edit

You say "is stored as (Ù†)" -- How do you know? Most attempts to see what is stored are subject to the client fiddling with the bytes. This is a sure way to see what is there:

 SELECT col, HEX(col) FROM tbl ...

For چ, the HEX should be DA86 for proper utf8 (or utf8mb4) encoding. If you get C39AE280A0, then you have "double encoding". In general, Arabic/Persian/Farsi should be of the form Dxyy.

If you read چ while connected with latin1, you will get Ù†, which is DA86 in latin1 encoding (Ù = DA and † = 86).

ن encodes as D986.

Double Encoding

I used hex(col) to send query and got C399E280A0 for ن and C39AE280A0 for چ .

So, you have "double encoding", not "Mojibake".

C399 is utf8 for Ù; E280A0 is utf8 for †. Your character was changed from latin1 to utf8 twice. Usually the end result is invisible to the outside world, but messed up in the table. That is because the SELECT decodes twice. However, since you are seeing only one decode, things are not that simple.

Caveat: You have a situation where I have not experimented; the advice I give you could be wrong.

Here's what probably happened.

• The client had characters encoded as utf8 (good) hex: D986;
• When inserting, the application lied by claiming that the client had latin1 encoding. (This is the old default.); D9 converted to Ù and 86 converted to †;
• The column in the table declared CHARACTER SET utf8 (good). But now the Ù is stored as C399 and the † is stored as E280A0, for a total of 5 bytes;
• When reading the connection claimed utf8 (good) for the client, so those 5 bytes were turned back into Ù†;
• The client dutifully said the utf8 data was Ù†.

Notice the imbalance between the INSERT and the SELECT. You tagged this PHP; did PHP both write and read the data? Did it have a different setting for the charset for writing and reading?

The problem seems to be only in setting the charset for writing. It needed to be explicitly utf8, not defaulting to latin1.

But what about the data? If everything I said (about double encoding) matches what you have, then an UPDATE can fix the data. See my blog for the details.