完整性约束违规：1062 utf8_unicode_ci排序规则的重复条目 [英] Integrity constraint violation: 1062 Duplicate entry for utf8_unicode_ci collation

问题描述

  CREATE TABLE`tag`（$ b） $ b`id` int（11）NOT NULL AUTO_INCREMENT，
`name` varchar（255）COLLATE utf8_unicode_ci NOT NULL，
 PRIMARY KEY（`id`），
 UNIQUE KEY`UNIQ_389B7835E237E06` （`name`）
）ENGINE = InnoDB AUTO_INCREMENT = 13963 DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci 
  

此表的排序规则是utf8_unicode_ci。当我尝试插入以下2个条目时，我得到一个完整性约束违规执行。

SQL日志：

  130607 14:35:53 1096将imtpdb @ localhost连接到imtpdb 
 1096查询SET NAMES utf8 
 1096查询START TRANSACTION 
 1096查询INSERT INTO标签（name）VALUES（'até'）
 1096查询INSERT INTO标签（名称）VALUES（'ate'）
 1096查询回滚
 1096退出
  

确切的错误信息是：

  SQLSTATE [23000]：违反完整性约束：1062对于UNIQ_389B7835E237E06的密钥，重复输入$
  

cnf：

  [client] 
 default-character-set = utf8 
 
 [mysql ] 
 default-character-set = utf8 
 
 
 [mysqld] 
 default-character-set = utf8 
 collat​​ion-server = utf8_unicode_ci 
 init-connect ='SET NAMES utf8'
 character-set-server = utf8 
  

我正在运行mysql 5.1.54。我一直在试图弄清楚一段时间，现在无济于事。有谁知道我做错了什么？哦，只是说明显的。表中不存在该条目。

解决方案

排序规则 utf8_unicode_ci 是不区分大小写和不区分大小写，这就是为什么它认为'até'和'ate'作为重复。 没有unicode多语言排序规则是不区分大小写和重音敏感的。根据您的需要，您可以尝试不同的解决方法。一些相关问题：

• 是否有一个MySQL utf8排序规则不会混合重音字符？


• MySQL不区分大小写但重要的UTF8独特键


• 如何进行重音敏感搜索MySql

I have a table called tag with a unique constraint on the name column:

CREATE TABLE `tag` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_389B7835E237E06` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13963 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The collation for this table is utf8_unicode_ci. When I try to insert the following 2 entries I get an "Integrity constraint violation" execption.

SQL log:

130607 14:35:53  1096 Connect   imtpdb@localhost on imtpdb
1096 Query     SET NAMES utf8
1096 Query     START TRANSACTION
1096 Query     INSERT INTO tag (name) VALUES ('até')
1096 Query     INSERT INTO tag (name) VALUES ('ate')
1096 Query     rollback
1096 Quit

The exact error message is:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'ate' for key 'UNIQ_389B7835E237E06'

my.cnf:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
default-character-set = utf8
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

I'm running mysql 5.1.54. I have been trying to figure this out for a while now to no avail. Does anyone know what I'm doing wrong? Ohh, just to state the obvious. The entry doesn't exist in the table already.

解决方案

Collation utf8_unicode_ci is both case insensitive and accent insensitive, that's why it considers 'até' and 'ate' as duplicates. There is no unicode multilingual collation that is case insensitive and accent sensitive. Depending on your needs, you may try different workarounds. Some related questions:

• Is there a MySQL utf8 collation that will not conflate accented characters?
• MySQL Case Insensitive but Accent Sensitive UTF8 Unique Key
• How to conduct an Accent Sensitive search in MySql

这篇关于完整性约束违规：1062 utf8_unicode_ci排序规则的重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！