MySQL `INSERT INTO SELECT`语句在唯一字段上生成重复条目错误

Question

我使用INSERT INTO SELECT 来迁移用户的数据到其他数据库中，但是这会产生一些问题。

Duplicate entry '                  　' for key 'users_name_unique'

虽然数据源是另一个唯一索引，不应包含任何重复的数据。（'users_name_unique' 是 db2.users 上的索引名称）

以下是查询语句，其中 db2.users 的 name 字段是 varchar(50) 唯一且非空索引，db1.users 的 name 字段是 varchar(60) 唯一且非空索引。我已经检查了每条记录中字段的长度，并且这些长度都远小于 50。

INSERT INTO db2.users (name, email, uid) SELECT
    name,
    IF (mail = '', NULL, mail) AS email,
    uid
FROM
    db1.users;

在db1.users的名称字段中存在不可打印或空白字符。

可能的问题是什么？

更新

我创建了多个测试表，如下所示，有两个结构非常相似且没有数据的表（我故意更改了长度，因为源数据是varchar（60）），但结果不同。

    mysql> desc ttt3;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> desc users;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(60)      | NO   | UNI | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> show index from ttt3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ttt3  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| ttt3  |          0 | name     |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> show index from users;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY           |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | users_name_unique |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> insert into ttt3(name) select name from scratch.users where scratch.users.uid != 0;
Query OK, 1556 rows affected (0.24 sec)
Records: 1556  Duplicates: 0  Warnings: 0

mysql> insert into users(name) select name from scratch.users where scratch.users.uid != 0;
ERROR 1062 (23000): Duplicate entry '                  　' for key 'users_name_unique'

更新

事实证明，目标字段的排序规则设置为“utf8_unicode_ci”，而原始字段是“utf8_general_ci”，更改此选项可以解决问题。

- leetom

---

我认为你的假设要么是错误的（源查询确实返回了重复项，这应该很容易验证），要么目标表已经有一些值。 - Álvaro González

@ÁlvaroGonzález 我已经删除了db2.users中的所有数据以进行测试。 - leetom

@leetom：你说你的主键值中有不可打印字符。我不知道有没有一些名称在名称中具有“null”值。可能数据库引擎将其视为字符串的“结尾”，就像许多编程语言一样。这样，您的其余主键将被截断，最终导致重复... - Thomas Voß

删除索引后，检查是否也删除了由于主键创建的索引。这应该是根本原因。 - I_am_Batman

@Serg 发现目标字段的排序规则设置为“utf8_unicode_ci”，更改此选项解决了问题。谢谢！ - leetom

Answer 1

原因在于：

目标字段的排序规则(collation)被设置为'utf8_unicode_ci'(laravel默认的排序规则)，而原始字段的排序规则是'utf8_general_ci'。

这些排序规则对于“排序”或“相等”有不同的规则。更改此选项解决了问题。

- leetom