从几个输出中选择一个在mysql中具有特殊字符的输出

Question

In my database I have two forms of the same word. So for example when I search for amor I get amor and āmōr. How can I fetch just āmōr from the database.

Answer 1

If you don't want to mess around with Collation (for some reason):

SELECT a.text
FROM table a
WHERE a.text = 'amor'
AND BINARY(a.text) = BINARY('amor')

I left the first condition with the plain text so that MySQL can use an index on text, in case that exists. The second condition will make sure you are only getting the exact text (including capitalization) you searched for.

EDIT: Based on comment.

If you do want to get the accented result, you need to use a collation that ignores such accents. The one you need to use depends on the collation of the column..

Try any of the below:

SELECT a.text
FROM table a
WHERE a.text = 'amor'
COLLATE 'utf8_general_ci';

Or:

SELECT a.text
FROM table a
WHERE a.text = 'amor'
COLLATE 'latin1_general_ci';

EDIT #2: Based on more comments.

To always get the accented version in case there is more than one match:

SELECT a.text
FROM table a
WHERE a.text = 'amor' -- this condition returns both results
ORDER BY LENGTH(BINARY(a.text)) DESC
LIMIT 1