MySQL matching unicode characters with ascii version

You specified the name column as text CHARACTER SET utf8 COLLATE utf8_unicode_ci which tells MySQL to consider e and é as equivalent in matching and sorting. That collation and utf8_general_ci both make a lot of things equivalent.

http://www.collation-charts.org/ is a great resource once you learn how to read the charts, which is pretty easy.

If you want e and é etc. to be considered different then you must choose a different collation. To find out what collations are on your server (assuming you’re limited to UTF-8 encoding):

mysql> show collation like 'utf8%';

And choose using the collation charts as a reference.

One more special collation is utf8_bin in which there are no equivalencies, it’s a binary match.

The only MySQL Unicode collations I’m aware of that are not language specific are utf8_unicode_ci, utf8_general_ci and utf8_bin. They are rather weird. The real purpose of a collation is to make the computer match and sort as a person from somewhere would expect. Hungarian and Turkish dictionaries have their entries ordered according to different rules. Specifying a collation allows you to sort and match according to such local rules.

For example, it seems Danes consider e and é equivalent but Icelanders don’t:

mysql> select _utf8'e' collate utf8_danish_ci
    -> = _utf8'é' collate utf8_danish_ci as equal;
+-------+
| equal |
+-------+
|     1 |
+-------+

mysql> select _utf8'e' collate utf8_icelandic_ci
    -> = _utf8'é' collate utf8_icelandic_ci as equal;
+-------+
| equal |
+-------+
|     0 |
+-------+

Another handy trick is to fill a one column table with a bunch of characters you’re interested in (it’s easier from a script) and then MySQL can tell you the equivalencies:

mysql> create table t (c char(1) character set utf8);
mysql> insert into t values ('a'), ('ä'), ('á');
mysql> select group_concat(c) from t group by c collate utf8_icelandic_ci;
+-----------------+
| group_concat(c) |
+-----------------+
| a               |
| á               |
| ä               |
+-----------------+

mysql> select group_concat(c) from t group by c collate utf8_danish_ci;
+-----------------+
| group_concat(c) |
+-----------------+
| a,á             |
| ä               |
+-----------------+

mysql> select group_concat(c) from t group by c collate utf8_general_ci;
+-----------------+
| group_concat(c) |
+-----------------+
| a,ä,á           |
+-----------------+

You have set collation to utf8_unicode_ci which equates accented latin characters. Additional information can be found here.

one think you can to with yor query srting is to decode it…

< ?php
$query="অ্যাক্রোবেট রিডার"; // some Unicode charectors
$query=urldecode($query);
$qry= "SELECT * FROM table WHERE books LIKE '%$query%";

//rest of the code....
?>

it worked for me. 🙂

Advertisements
By Rz Rasel Posted in Php

31 comments on “MySQL matching unicode characters with ascii version

  1. Hi just thought i would tell you something.. This is twice now i’ve landed on your blog in the last 2 weeks looking for completely unrelated things. Spooky or what?

  2. Thank you for another fantastic article. Where else may anybody get that kind of information in such an ideal means of writing? I have a presentation subsequent week, and I am at the look for such information.

  3. Wow, marvelous blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your site is fantastic, as well as the content!

  4. Magnificent beat ! I would like to apprentice even as you amend your website, how can i subscribe for a weblog web site? The account aided me a appropriate deal. I have been a little bit acquainted of this your broadcast offered vibrant transparent concept.

  5. Hi there, just became aware of your blog through Google, and found that it’s really informative. I’m gonna watch out for brussels. I will be grateful if you continue this in future. Lots of people will be benefited from your writing. Cheers!

  6. Hi, I just ran across your weblog via google. Your article is truly relevant to my life currently, and I’m really pleased I found your website.

  7. I was wondering if you ever considered changing the layout of your blog? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having one or two images. Maybe you could space it out better?

  8. This layout is great! You certainly know how to keep a new reader amused. Between your wit along with your videos, I had been almost moved to get started on my personal blog (nicely, almost… HaHa!)#) Fantastic job. I genuinely loved what we had to convey, and more than that, how a person presented that. Too neat!

  9. Thank an individual, I’ve recently been searching for info regarding this topic forever and yours is the best I’ve identified till today. But, what in regards to the bottom series? Are anyone sure regarding the source?

  10. Im no expert, but I suppose you just made an excellent point. You clearly fully understand what youre speaking about, and I can definitely get behind that. Thanks for being so upfront and so straightforward.

  11. Excellent merchandise from you, man. Khmer Karaoke Megastars » Somnangblogs I have understand the stuff previous to and you are just extremely great. I truly like that which you have purchased here, really like what you’re stating and the way you declare it. You make it entertaining and also you still care for to keep it smart. I cannot wait you just read much much more from anyone. This is in reality a wonderful Khmer Karaoke Celebrities » Somnangblogs informations.

  12. This is a good posting, I was wondering if I could use this write-up on my website, I will link it back to your website though. If this is a problem please let me know and I will take it down right away

  13. Common info is definitely helpful to most who are looking for a useful way of spending your time and effort around the home pc. The guy can possibly be completely to show these. Many thanks

  14. Wonderful site you have here but I was curious about if you knew of any user discussion forums that cover the same topics discussed here? I’d really like to be a part of community where I can get advice from other knowledgeable people that share the same interest. If you have any recommendations, please let me know. Cheers!

  15. hey there and thank you for your information – I’ve definitely picked up something new from proper here. I did however experience a few technical points the use of this website, since I experienced to reload the web site lots of times previous to I may get it to load correctly. I were thinking about if your web host is OK? No longer that I am complaining, but slow loading instances times will often affect your placement in google and can damage your quality rating if ads and ***********|advertising|advertising|advertising and *********** with Adwords. Well I’m adding this RSS to my email and could look out for much extra of your respective intriguing content. Make sure you replace this once more very soon..

  16. Hello there, just became aware of your weblog via Google, and located that it is really informative. I’m going to watch out for brussels. I will be grateful should you proceed this in future. A lot of other people will probably be benefited out of your writing. Cheers!

  17. This may be isn’t the best place to ask, I wanted some feedback on a good chiropractor because I need great one, I wanted reviews or data on Chiropractic Works 8305 Liberty Rd Baltimore, MD 21244 (410) 922-4341

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s