MySQL `INSERT INTO SELECT` clause generate Duplicate entry error on unique field

Question

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

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)

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.

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.

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.

    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.

Answer 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'.

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