How to search for Accent Sensitive in MySql

Question

I have a MySQL table with general utf8 collation. In the table, I see two entries:

Abade
ABAD

I am using a query that looks like this:

SELECT * FROM `words` WHERE `word` = 'abád' 

The query result gives both words:

Abade
ABAD

Is there a way to indicate that I only want MySQL to find the accented word? I want the request to return only

abád

I also tried this query:

 SELECT * FROM `words` WHERE BINARY `word` = 'abád' 

It does not give me any results. Thanks for the help.

Answer 1

If your search terms in this field will always be accent sensitive, then declare the field match as utf8_bin (which will compare utf8 bytes for equality) or use a language specific match that distinguishes between accented and non-accented characters.

 col_name varchar(10) collate utf8_bin 

If searches are usually accent-insensitive but you want to make an exception for this search, try

 WHERE col_name = 'abád' collate utf8_bin 

Answer 2

In my version (MySql 5.0), there is no combination of charset utf8 for case-insensitive, accent-sensitive queries. The only distinguishing mark for utf8 is utf8_bin. However, it is also case sensitive.

My job was to use something like this:

 SELECT * FROM `words` WHERE LOWER(column) = LOWER('aBád') COLLATE utf8_bin 

Answer 3

MySQL error for future link http://bugs.mysql.com/bug.php?id=19567 .

Answer 4

 SELECT * FROM `words` WHERE column = 'abád' collate latin1_General_CS 

(or your collation, including cs)

Answer 5

You can try to find the sixth character variable, HEX () in mysql and use a similar function in your programming language and map them. This worked well for me when I did a listing where a person could select a person’s first letter.

Answer 6

Well, you just described what sorting utf8_general_ci is (a, á, à, â, ä, å is still a comparison in comparison).

MySQL Server 5.1 has also made changes to utf8_general_ci and utf8_unicode_ci, so it also depends on the version of the server. Check documents better.

So, if this is MySQL server 5.0, I would go for utf8_unicode_ci instead of utf8_general_ci, which is obviously not true for your use case.

Answer 7

I was getting the same error.

I changed the mapping of my table to utf8_bin (via phpMyAdmin) and the problem was resolved.

Hope this helps! :)

Answer 8

Verify that the sort type of the database table ends with "_ci". This means that the case is case insensitive ...

Change it to match the same or closest name without "_ci" ...

For example ... change "utf8_general_ci" to "utf8_bin" FEM

Answer 9

The accepted answer is good, but be careful that you might have to use COLLATE utf8mb4_bin!

 WHERE col_name = 'abád' collate utf8mb4_bin 

Bugs fixed above, for example:

MySQL said: Documentation 1253 - COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'