本文介绍了MySQL区分e和é(e急性) - UNIQUE索引的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个表,学生,有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索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-14 12:25