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

Question

我使用 INSERT INTO SELECT 跨数据库迁移用户数据，但它会生成

Duplicate entry '                  　' for key 'users_name_unique'

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

这是查询，其中来自 db2.users 的名称字段是 varchar(50) 唯一且非空索引，而来自 db1.users 的名称字段是 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”，更改此选项可以解决问题。

Answer 1

原因如下:

目标字段的排序规则设置为“utf8_unicode_ci”(laravel 的默认排序规则)，原始字段为“utf8_general_ci”。

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