Accented and unaccented letters are equal in MariaDB

Question

I have a table in a MariaDB database, which contains words of any language. The problem is that, for example, 'a' == 'á' is considered true.

MariaDB [(none)]> select 'a' = 'á';
+------------+
| 'a' = 'á'  |
+------------+
|          1 |
+------------+

The words' column is marked UNIQUE in the table, which is good to prevent duplicates, but it treats, for example, 'arat' and 'árát' the same, so the insertion of 'arat' fails if 'árát' is already present, which is not the expected behaviour, both words should be allowed to present in the table parallelly.

I'm using MariaDB 10.7.3-1 on ArchLinux. How to set MariaDB to differentiate these words?

Answer 1

Use COLLATE like below to do the comparison in character set.

SELECT 'str1' = 'str2' COLLATE <charset>;