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

I use INSERT INTO SELECT to migrate users' data across databases, but it generates

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

Duplicate entry '                  　' for key 'users_name_unique'

although the data source is another unique index and should not contain any duplicate data.('users_name_unique' is the index name on db2.users)

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

Here is the query, where name field from db2.users is varchar(50) unique and not null index, and name field from db1.users is varchar(60) unique and not null index. I have already checked the length of the field in every record, and the lengths are all much smaller than 50.

这是查询，其中db2.users的name字段是varchar（50）唯一且不是null索引，db1.users中的name字段是varchar（60）唯一而不是null索引。我已经检查了每个记录中字段的长度，长度都小于50。

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

There are non-printable or white spaces in the name field from db1.users.

db1.users的名称字段中有不可打印或空格。

What might be the problem?

可能是什么问题？

update

I created multi test tables, and as following, there are two tables with very similar structure and no data (I changed the length on purpose since the source data is varchar(60)), but different results.

我创建了多个测试表，如下所示，有两个表具有非常相似的结构且没有数据（由于源数据是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'

update

It turns out that the destination field's collation is set to 'utf8_unicode_ci' and the original field is 'utf8_general_ci', changing this option solve the problem.

1 个解决方案

#1

Here is the reason:

原因如下：

The destination field's collation is set to 'utf8_unicode_ci' (laravel's default collation) and the original field is 'utf8_general_ci'.

目标字段的排序规则设置为'utf8_unicode_ci'（laravel的默认排序规则），原始字段为'utf8_general_ci'。

These collations have different rules of "sort" or "equal". Changing this option solved the problem.

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