In MySQL, query of a word also matches words with special characters-CodePudding

The following query is supposed to match all comments containing 'testa', but it matches 'testä', as shown below:

mysql> select comment from document where comment='testa';
 --------- 
| comment |
 --------- 
| testä   |
 --------- 
1 row in set (0.00 sec)

How to do an exact match only for the string 'testa', so that it doesn't show results for its matching special character?

CodePudding user response：

I think I have a solution for you. You can use convert(column_name using latin1). Please check my code below=>

CREATE TABLE document(
 comment  varchar(20)
);

Insert INTO document
SELECT 'testä';

Insert INTO document
SELECT 'testa';

select comment from document where convert(comment using latin1)='testa';

if you want to use COLLATE then MySql version should be 8.0 as Akina mentioned in the description.

Note : Please check the link dbfiddle