mysql query select like with diacritic Turkish let

问题:

I have a token table in Turkish; it's default collation is utf8_general_ci On FreeBSD server, mysql version is 5.6.15

I want to query;

select * from tokens where type like 'âmâ';

or

select * from tokens where type='âmâ';

With these queries, result must be one unique for 'âmâ' (it means 'blind' in Turkish also) But i have four raw result;

result 1 "amâ" means 'but'
result 2 "ama" means 'but'
result 3 "âma" means 'blind'
result 4 "âmâ" means 'blind'

that didnt i want.

I tried different collations and character sets and names. But same results with working ones.

Any help please

回答1:

You could force a binary comparison:

SELECT * FROM tokens WHERE BINARY type='âmâ';

please see the documentation of the binary operator.

回答2:

The Turkish collation is latin5_turkish_ci. See: Character Sets and Collations in MySQL.

Use the COLLATE keyword in the WHERE-clause.

SELECT *
FROM tokens
WHERE type = 'âmâ' COLLATE latin5_turkish_ci;

I have not tested it. I hope it helps.

See: Using COLLATE in SQL Statements and Collation of Expressions.