how to create index with accent in MySQL

Question

I have a database which I manage with phpMyAdmin. I have a table to save the verb tense and the verb. It looks like it follows:

Column | Type        | Collation         |  Attributes | Null | Default | Extra
-------+-------------+-------------------+-------------+------+---------+------
form   | varchar(50) | latin1_swedish_ci |             | No   |         |
verb   | varchar(50) | latin1_swedish_ci |             | Yes  | NULL    |

and I created and index to have a faster access:

Keyname     | Type  | Unique | Packed | Column | Cardinality | Collation | Null | Comment
------------+-------+--------+--------+--------+-------------+-----------+------+--------
verbs_index | BTREE | Yes    | No     | form   | 1           | A         |      |
            |       |        |        | verb   | 1           | A         | YES  |

The goal of this is to have an association between a verb and all its verbs tenses (form) but the problem comes when I try to insert a pair (form,verb) with an accent if the form without accent already exists. Those are the same words to MySql and I get the error:

Duplicate entry 'form-verb' for key 'verbs_index'.

I'd like to insert:

insert into verbs values('o','verb1'); (without accent)
insert into verbs values('ó','verb1'); (with accent)

I've been looking at collation stuff and I've already tried with every latin and utf8.

Would [this post](http://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql) help ? Don't be sad, you're not alone with that problem, which is not a bug (although I find it strange default behavior but still...) — Laurent S., Jun 25 '13 at 17:41

score 1 · Accepted Answer · answered Jun 25 '13 at 18:50

The "case insensitive" collation will compare strings ignoring any kind of variation. The case of course. But it will also ignore diacritical marks. So for example, o, O, Ô and ò are considered equal.

To know the available collations on your system, use SHOW COLLATION:

mysql> SHOW COLLATION;
+----------------------+----------+-----+---------+----------+---------+
| Collation            | Charset  | Id  | Default | Compiled | Sortlen |
+----------------------+----------+-----+---------+----------+---------+
[...]
| latin1_german1_ci    | latin1   |   5 |         | Yes      |       1 |
| latin1_swedish_ci    | latin1   |   8 | Yes     | Yes      |       1 |
| latin1_danish_ci     | latin1   |  15 |         | Yes      |       1 |
| latin1_german2_ci    | latin1   |  31 |         | Yes      |       2 |
| latin1_bin           | latin1   |  47 |         | Yes      |       1 |
| latin1_general_ci    | latin1   |  48 |         | Yes      |       1 |
| latin1_general_cs    | latin1   |  49 |         | Yes      |       1 |
| latin1_spanish_ci    | latin1   |  94 |         | Yes      |       1 |
| latin2_czech_cs      | latin2   |   2 |         | Yes      |       4 |
| latin2_general_ci    | latin2   |   9 | Yes     | Yes      |       1 |
| latin2_hungarian_ci  | latin2   |  21 |         | Yes      |       1 |
| latin2_croatian_ci   | latin2   |  27 |         | Yes      |       1 |
[...]

Say you which to change your table collation to latin1_general_cs (_cs for case-sensitive -- well case + accents and so on):

mysql> ALTER TABLE verbs COLLATE latin1_general_cs;

how to create index with accent in MySQL

1 Answers1