I use INSERT INTO SELECT to transfer user data through 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 name of the index on db2.users)
Here is a query where the name field from db2.users is varchar (50) unique and not a null index, and the name field from db1.users is varchar (60) a unique and non-zero index. I already checked the length of the field in each record, and the lengths are much less than 50.
INSERT INTO db2.users (name, email, uid) SELECT name, IF (mail = '', NULL, mail) AS email, uid FROM db1.users;
The name field of db1.users has non-printable or white spaces.
What could be the problem?
Update
I created several test tables, and, as shown below, there are two tables with a very similar structure and without data (I changed the length as intended, 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 is set to "utf8_unicode_ci" and the source field is "utf8_general_ci", changing this option solves the problem.
source share