I have a MySQL table with utf8 general ci collation. In the table, I can see two entries:
abad
abád
I am using a query that looks like this:
SELECT * FROM `words` WHERE `word` = 'abád'
The query result gives both words:
abad
abád
Is there a way to indicate that I only want MySQL to find the accented word? I want the query to only return
abád
I have also tried this query:
SELECT * FROM `words` WHERE BINARY `word` = 'abád'
It gives me no results. Thank you for the help.
If your searches on that field are always going to be accent-sensitive, then declare the collation of the field as utf8_bin (that'll compare for equality the utf8-encoded bytes) or use a language specific collation that distinguish between the accented and un-accented characters.
col_name varchar(10) collate utf8_bin
If searches are normally accent-insensitive, but you want to make an exception for this search, try;
WHERE col_name = 'abád' collate utf8_bin
Update for MySQL 8.0, plus addressing some of the Comments and other Answers:
CHARACTER SET matches the beginning of the COLLATION.COLLATION name ending in _bin will ignore both upper/lower case and accents. Examples: latin1_bin, utf8mb4_bin.COLLATION name containing _as_ will ignore accents, but do case folding or not based on _ci vs _cs.SHOW COLLATION;.utf8mb4 is now the default charset. You should be using that instead of utf8.CHARACTER SET and COLLATION set 'properly' on each column (or defaulted by the table definition) than to dynamically use any conversion routine such as CONVERT().
SELECT * FROM `words` WHERE column = 'abád' collate latin1_General_CS
(or your collation including cs)
You can try searching for the hex variable of the character, HEX() within mysql and use a similar function within your programming language and match these. This worked well for me when i was doing a listing where a person could select the first letter of a person.