这可能是与 unicode 相关的问题。

我有一个mysql源文件:

set names utf8;
STATUS;
drop table tianya;
create table tianya ( name char(50) not null primary key, passwd char (50) not null, email char(50));
insert into tianya values ("■■■■■■■■","68221",""),("12345678","098",""),("〡〢〣〤〥〦〧〨","1","");

当我运行这个 sql 文件时，mysql 报告: ERROR 1062 (23000) at line 5: Duplicate entry '〡〢〣〤〥〦‧と' for key 'PRIMARY'

如下图:

但是，正如我们所见，该表是全新的，并且3个主键彼此不同。

那么，是什么导致了这个错误？

=======添加一些关于 2015 年 8 月 28 日的额外信息

mysql> create table tianya ( name char(50) not null primary key, passwd char (50) not null, email char(50));
Query OK, 0 rows affected (0.16 sec)

mysql> 
mysql> 
mysql> insert into tianya values ("■■■■■■■■","68221",""),("12345678","098","");
Query OK, 2 rows affected (0.19 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select name,hex(name) from tianya; 
+--------------------------+--------------------------------------------------+
| name                     | hex(name)                                        |
+--------------------------+--------------------------------------------------+
| ■■■■■■■■                 | E296A0E296A0E296A0E296A0E296A0E296A0E296A0E296A0 |
| 12345678                 | 3132333435363738                                 |
+--------------------------+--------------------------------------------------+
2 rows in set (0.03 sec)

mysql> insert into tianya values ("1234567","098",""),("〡〢〣〤〥〦〧〨","1","");                         
ERROR 1062 (23000): Duplicate entry '〡〢〣〤〥〦〧〨' for key 'PRIMARY'
mysql> delete from tianya;
Query OK, 2 rows affected (0.02 sec)

mysql> insert into tianya values ("1234567","098",""),("〡〢〣〤〥〦〧〨","1","");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select name,hex(name) from tianya;
+--------------------------+--------------------------------------------------+
| name                     | hex(name)                                        |
+--------------------------+--------------------------------------------------+
| 1234567                  | 31323334353637                                   |
| 〡〢〣〤〥〦〧〨         | E380A1E380A2E380A3E380A4E380A5E380A6E380A7E380A8 |
+--------------------------+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql> describe tianya;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(50) | NO   | PRI | NULL    |       |
| passwd | char(50) | NO   |     | NULL    |       |
| email  | char(50) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.05 sec)

mysql> show create table tianya ( name char(50) not null primary key, passwd char (50) not null, email char(50));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( name char(50) not null primary key, passwd char (50) not null, email char(50))' at line 1
mysql> show create table tianya;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                   |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tianya | CREATE TABLE `tianya` (
  `name` char(50) COLLATE utf8_unicode_ci NOT NULL,
  `passwd` char(50) COLLATE utf8_unicode_ci NOT NULL,
  `email` char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

====和另一个测试:

xungeng@fl-ubuntu:~/tmp/a$ mysql -u root test < a.sql 
Table   Create Table
t2      CREATE TABLE `t2` (\n  `name` varchar(10) COLLATE utf8_unicode_ci NOT NULL,\n  UNIQUE KEY `name` (`name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
ERROR 1062 (23000) at line 5: Duplicate entry 'hì' for key 'name'
xungeng@fl-ubuntu:~/tmp/a$ cat a.sql 
set names utf8;
drop table if exists t2;
create table t2 (name varchar(10) not null unique key);
show create table t2;
insert into t2 values ("hí"),("hì");

最佳答案

终于知道是怎么回事了。

整理 utf_unicode_ci 不区分大小写。如果存在键“test”或“Test”，这会使“TEST”的主键或唯一键的插入失败。我还以为碰撞是由'■■■■■■■■'和'〡〢〤〥〦‧と'引起的，这误导了你，也误导了我。事实上碰撞是由'12345678'和'〡〢〤〥〦‧と'引起的。

'〡〢〤〥〦‧と'的符号看起来很奇怪。它们分别是数字 12345678 的中国古代符号。 [引用:https://zh.wikipedia.org/wiki/%E7%AD%B9%E7%AE%97(仅限中文版)]

为了解决这个问题，我将 utf8_unicode_ci 更改为 utf8_bin。

关于mysql - mysql奇怪的 "Duplicate entry"错误，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32170605/