问题描述
我有一个表，学生，有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 （实际上它适用于所有 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索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！