How to conduct an Accent Sensitive search in MySql

Accepted answer is good, but beware that you may have to use COLLATE utf8mb4_bin instead!

WHERE col_name = 'abád' collate utf8mb4_bin

Above fixes errors like:

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

The MySQL bug, for future reference, is http://bugs.mysql.com/bug.php?id=19567.

In my version (MySql 5.0), there is not available any utf8 charset collate for case insensitive, accent sensitive searches. The only accent sensitive collate for utf8 is utf8_bin. However it is also case sensitive.

My work around has been to use something like this:

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

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:

• The CHARACTER SET matches the beginning of the COLLATION.
• Any COLLATION name ending in _bin will ignore both upper/lower case and accents. Examples: latin1_bin, utf8mb4_bin.
• Any COLLATION name containing _as_ will ignore accents, but do case folding or not based on _ci vs _cs.
• To see the collations available (on any version), do SHOW COLLATION;.
• utf8mb4 is now the default charset. You should be using that instead of utf8.
• It is better to have the 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().