Databases
StackOverflow2013
Postsdbo
POMySQL distinction between e and é (e acute) - UNIQUE index
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL distinction between e and é (e acute) - UNIQUE index
    primarykey
    data
    text
    <p>I have a table, <code>students</code>, with 3 columns: <code>id</code>, <code>name</code>, and <code>age</code>. I have a <code>UNIQUE</code> index <code>Index_2</code> on columns <code>name</code> and <code>age</code>.</p> <pre><code>CREATE TABLE `bedrock`.`students` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(45) NOT NULL, `age` INTEGER UNSIGNED NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `Index_2` USING BTREE(`name`, `age`) ) ENGINE = InnoDB; </code></pre> <p>I tried this insert option:</p> <pre><code>insert into students (id, name, age) values (1, 'Ane', 23); </code></pre> <p>which works ok. Than I've tried this one (see <strong>Ané</strong> - <strong>e acute</strong>):</p> <pre><code>insert into students (id, name, age) values (2, 'Ané', 23); </code></pre> <p>and I receive this error message:</p> <pre><code>"Duplicate entry 'Ané-23' for key 'Index_2'" </code></pre> <p>MySQL somehow does not make any distinction between "Ane" and "Ané". How I can resolve this and why this is happening?</p> <p>Charset for table students is "utf8" and collation is "utf8_general_ci".</p> <pre><code>ALTER TABLE `students` CHARACTER SET utf8 COLLATE utf8_general_ci; </code></pre> <p><strong>Later edit1: @Crozin:</strong></p> <p>I've changed to use collation utf8_bin:</p> <pre><code>ALTER TABLE `students` CHARACTER SET utf8 COLLATE utf8_bin; </code></pre> <p>but I receive the same error.</p> <p>But if I create the table from start with charset utf8 and collation utf8_bin, like this:</p> <pre><code>CREATE TABLE `students2` ( `id` INTEGER UNSIGNED AUTO_INCREMENT, `name` VARCHAR(45), `age` VARCHAR(45), PRIMARY KEY (`id`), UNIQUE INDEX `Index_2` USING BTREE(`name`, `age`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_bin; </code></pre> <p>both below insert commands works ok:</p> <pre><code>insert into students2 (id, name, age) values (1, 'Ane', 23); // works ok insert into students2 (id, name, age) values (2, 'Ané', 23); // works ok </code></pre> <p>This seems to be very weird.</p> <p><strong>Later edit 2:</strong></p> <p>I saw another answer here. I'm not sure if the user deleted or it get lost. I was just testing it:</p> <p>The user wrote that first he created 3 tables with 3 different charsets:</p> <pre><code>CREATE TABLE `utf8_bin` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) COLLATE utf8_bin NOT NULL, `age` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `Index_2` (`name`,`age`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; CREATE TABLE `utf8_unicode_ci` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL, `age` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `Index_2` (`name`,`age`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; CREATE TABLE `utf8_general_ci` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) COLLATE utf8_general_ci NOT NULL, `age` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `Index_2` (`name`,`age`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; </code></pre> <p>The results of the user are:</p> <pre><code>Insert commands: INSERT INTO utf8_bin VALUES (1, 'Ane', 23), (2, 'Ané', 23); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 INSERT INTO utf8_unicode_ci VALUES (1, 'Ane', 23), (2, 'Ané', 23); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 INSERT INTO utf8_general_ci VALUES (1, 'Ane', 23), (2, 'Ané', 23); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 </code></pre> <p>Here are my results:</p> <pre><code>INSERT INTO utf8_bin VALUES (1, 'Ane', 23), (2, 'Ané', 23); //works ok INSERT INTO utf8_unicode_ci VALUES (1, 'Ane', 23), (2, 'Ané', 23); // Duplicate entry 'Ané-23' for key 'Index_2' INSERT INTO utf8_general_ci VALUES (1, 'Ane', 23), (2, 'Ané', 23); //Duplicate entry 'Ané-23' for key 'Index_2' </code></pre> <p>I'm not sure why in his part this <code>INSERT</code> command worked and for me doesn't work.</p> <p>He also wrote that he tested this on Mysql on Linux - has to do something with this?! Even I do not think so.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMichael Currie
    1. USPaul
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL distinction between e and é (e acute) - UNIQUE index
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL distinction between e and é (e acute) - UNIQUE index
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL distinction between e and é (e acute) - UNIQUE index
      1. USmingos
      1. VTFavorite
    1. CONot related to your question, but it's never a good idea to have a unique key on a name field... Many people have the same name. What do you plan to do in that instance? That said, the fact you have created a unique key based on name and age would suggest a design decision.....
      singulars
      1. POMySQL distinction between e and é (e acute) - UNIQUE index
      1. USBrendan Bullen
    2. COHi Brendan, this is just an example, a fictive one - is not the same as I have in the project I work! I know is not ok to have an unique index on columns like 'name' and 'age'. I've choose this example (not a very intelligent one - I admin) instead choosing the real example which contains more then 10 columns....
      singulars
      1. POMySQL distinction between e and é (e acute) - UNIQUE index
      1. USPaul
    3. COThat makes sense.
      singulars
      1. POMySQL distinction between e and é (e acute) - UNIQUE index
      1. USBrendan Bullen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload