MySQL区分e和é（e急性） - UNIQUE索引 [英] MySQL distinction between e and é (e acute) - UNIQUE index

问题描述

我有一个表，学生，有3列： id ， / code>和 age 。
在列上有 UNIQUE 索引 Index_2 c>和 age 。

I have a table, students, with 3 columns: id, name, and age. I have a UNIQUE index Index_2 on columns name and age.

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;

我试过这个插入选项：

insert into students (id, name, age)
values (1, 'Ane', 23);

比较我尝试过这种方法（请参阅Ané - 急性）：

which works ok. Than I've tried this one (see Ané - e acute):

insert into students (id, name, age)
values (2, 'Ané', 23);

，我收到此错误讯息：

"Duplicate entry 'Ané-23' for key 'Index_2'"

MySQL在某种程度上没有区分Ane和Ané。

MySQL somehow does not make any distinction between "Ane" and "Ané". How I can resolve this and why this is happening?

表学生的字符集是utf8，排序规则是utf8_general_ci。

Charset for table students is "utf8" and collation is "utf8_general_ci".

ALTER TABLE `students` CHARACTER SET utf8 COLLATE utf8_general_ci;

稍后edit1：@Crozin：

我已更改为使用排序规则utf8_bin：

I've changed to use collation utf8_bin:

ALTER TABLE `students`
CHARACTER SET utf8 COLLATE utf8_bin;

但我收到同样的错误。

但是如果我从charset utf8和整理utf8_bin开始创建表，像这样：

But if I create the table from start with charset utf8 and collation utf8_bin, like this:

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;

两个下面insert命令工作正常：

both below insert commands works ok:

insert into students2 (id, name, age)
values (1, 'Ane', 23); // works ok

insert into students2 (id, name, age)
values (2, 'Ané', 23); // works ok

这似乎很奇怪。

稍后编辑2：

我在这里看到另一个答案。我不知道用户是否删除或丢失。
我只是测试它：

I saw another answer here. I'm not sure if the user deleted or it get lost. I was just testing it:

用户写道，首先他创建了3个表，有3个不同的字符集：

The user wrote that first he created 3 tables with 3 different charsets:

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;

用户的结果是：

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

b $ b

这里是我的结果：

Here are my results:

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'

我不知道为什么在他的部分这个 INSERT 命令工作，对我来说不起作用。

I'm not sure why in his part this INSERT command worked and for me doesn't work.

他还写道，他测试这个在Linux上的Mysql - 必须做这件事吗？即使我不这么认为。

He also wrote that he tested this on Mysql on Linux - has to do something with this?! Even I do not think so.

推荐答案

，排序规则是utf8_general_ci

and collation is "utf8_general_ci".

这就是答案。如果您使用 utf8_general_ci （实际上它适用于所有 utf _..._ [ci | cs] ）排序规则那么在比较中会忽略变音符号：

And that's the answer. If you're using utf8_general_ci (actually it applies to all utf_..._[ci|cs]) collation then diacritics are bypassed in comarison, thus:

SELECT "e" = "é" AND "O" = "Ó" AND "ä" = "a"

结果 1 。

如果你想区分ą和 code>然后使用 utf8_bin 排序规则（请记住它也区分大写和小写字符）。

If you want to distinguish between ą and a then use utf8_bin collation (keep in mind that it also distinguish between uppercase and lowercase characters).

顺便说一句，名字和年龄不保证任何唯一性。

By the way name and age don't guarantee any uniqueness.

这篇关于MySQL区分e和é（e急性） - UNIQUE索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！