Select query mysql results 'û' appears when I search for 'u'

Question

My query is:

SELECT * FROM enwiki.page where title_text = 'drug';

One of the results I got has the tilte_text = 'Drûg'. How can I prevent that?

prevent what? Just say no. – Strawberry Jun 05 '15 at 22:00 — Strawberry, Jun 05 '15 at 22:00

javierfdezg · Accepted Answer · 2015-06-08T13:17:44.083

5

Use the correct collate (eg: utf8_general)

SELECT * FROM enwiki.page where title_text = 'drug' COLLATE utf8_general;

MySQL has a CHARSET that tells how does the DB store the values internally. On the other handthe COLLATION instruct the DB how to search, compare and order the data.

For example, if you use any collation that ends in _ci (that stands for case insensitive, and you search like this:

SELECT name FROM myTable WHERE name LIKE %Home%;

You can receive this back:

Home
HOME
homE
home

and so on...

Same thing happens with the symbols or accents of the letters (in your case û). You have to use a collation that consider them.

edited Jun 08 '15 at 13:17

answered Jun 05 '15 at 22:00

javierfdezg

2,087
1
22
31

@Andi, have you had a chance to try it out? Need any more help? – javierfdezg Jun 06 '15 at 11:30
the query didn't work, and also took 134 sec, I am trying to alter table to change the collation of the specific column, it takes time. – Andi Keikha Jun 08 '15 at 14:07
1

Don't need to change the collation yet. Try using COLLATE utf8_general; – javierfdezg Jun 08 '15 at 14:09
I tried: utf8mb4_general_ci as well: said is not valid for CHARSET 'utf8' – Andi Keikha Jun 08 '15 at 14:27
Try this mysql command: SHOW COLLATION LIKE %utf8% – javierfdezg Jun 08 '15 at 14:27
There are a bunch of utf8_(something) or utf8mb4_(something). – Andi Keikha Jun 08 '15 at 14:29
1

Is there an utf8_unicode? Try that if yes – javierfdezg Jun 08 '15 at 14:33
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/79964/discussion-between-javierfdezg-and-andi). – javierfdezg Jun 08 '15 at 14:40

Select query mysql results 'û' appears when I search for 'u'

1 Answers1