Is MySQL converting UTF-8 characters in my query (not the results), stripping accents?

Question

I've some records in a DB where one of the VARCHAR fields may contain accented letters. If I do the following query using the CLI MySQL client I get 1 row returned, which is correct:

SELECT site_id, site_name FROM tbl_site WHERE site_name LIKE '%ém%'

However, using some PHP (PDO) to do the same query returns all the rows that contain 'em', but not the one row that contains 'ém'. Even more strangely, MySQL query logging shows that the query contains 'é', not 'e'.

Any ideas what might be going on here?

Answer 1

This depends on the character set and collation of the database, table or column. Are you performing the query against the same table?

Could you post the results of

SHOW CREATE TABLE <yourTable>;

Additionally, you could specify the desired collation on a query level:

SELECT site_id, site_name FROM tbl_site WHERE site_name LIKE '%ém%' collate utf8_bin;
-- same as
SELECT site_id, site_name FROM tbl_site WHERE BINARY site_name LIKE '%ém%';

(Note: You should very carefully evaluate the perfomance of such queries)

Answer 2

The utf8_general_ci collation is both case-insensitive and accent-insensitive.