2

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?

Improve this question
1
  • 1
    prevent what? Just say no. Commented Jun 5, 2015 at 22:00

1 Answer 1

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

@Andi, have you had a chance to try it out? Need any more help?
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.
Don't need to change the collation yet. Try using COLLATE utf8_general;
I tried: utf8mb4_general_ci as well: said is not valid for CHARSET 'utf8'
Is there an utf8_unicode? Try that if yes
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.