如何在不增加重复项的auto

如何在不增加重复项的auto_increment列的情况下执行批量mysql插入？(How do I do a bulk mysql insert without incrementing the auto_increment column for duplicates?)

这是我的数据库架构

+------------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------------------+----------------+ | phone_number | varchar(64) | NO | UNI | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | +------------------+------------------+------+-----+---------------------+----------------+

我希望能够同时插入多个电话号码（phone_number是一个唯一的密钥），但如果我有重复项，我不想增加auto_increment字段。

如果我执行INSERT INTO phone_numbers (phone_number) VALUES (%values%) ON DUPLICATE KEY UPDATE id=id; 即使重复，auto_increment也会增加。

这个问题： 防止MYSQL上的自动增量重复插入不处理批量插入。 我想做这样的事情： INSERT INTO phone_numbers (phone_number) SELECT '12345', '123456' FROM DUAL WHERE NOT EXISTS( SELECT phone_number FROM phone_numbers WHERE phone_number IN ('12345', '123456');但是DUAL table并没有真正处理多个值。

有任何想法吗？ MySQL 5.5。

Here's my database schema

+------------------+------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------------+------+-----+---------------------+----------------+ | phone_number | varchar(64) | NO | UNI | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | +------------------+------------------+------+-----+---------------------+----------------+

I'd like to be able to insert several phone numbers (phone_number is a unique key) at once, but I don't want to increment the auto_increment field if I have duplicates.

If I do INSERT INTO phone_numbers (phone_number) VALUES (%values%) ON DUPLICATE KEY UPDATE id=id; the auto_increment will increase even for duplicates.

This question: prevent autoincrement on MYSQL duplicate insert doesn't handle bulk inserts. I'd like to do something like this: INSERT INTO phone_numbers (phone_number) SELECT '12345', '123456' FROM DUAL WHERE NOT EXISTS( SELECT phone_number FROM phone_numbers WHERE phone_number IN ('12345', '123456'); but the DUAL table doesn't really handle multiple values well.

Any ideas? MySQL 5.5.

最满意答案

阅读完其他文章之后，一种方法是使用批量插入的临时表。 然后从临时表中选择行到我们的实际表中。 此时将删除重复的行，并且实际表中的auto_increment字段将是正确的。

CREATE TABLE PHONE_NUMBERS (id int(10) NOT NULL AUTO_INCREMENT, phone_number varchar(64), primary key (id), unique(phone_number) ); CREATE TEMPORARY TABLE TMP_PHONE_NUMBERS ( phone_number varchar(64), unique(phone_number) ); ' bulk insert INSERT INTO tmp_phone_numbers (phone_number) VALUES (%values%) ' remove phone numbers that already exist. This will create a unique ' set of phone numbers that do not exist in the real table. DELETE FROM tmp_phone_numbers WHERE phone_number in (SELECT phone_number from phone_numbers); ' copy into real table INSERT INTO phone_numbers (phone_number) SELECT phone_number FROM tmp_phone_numbers; ' Temp table is dropped when your connection is closed.

这是另一种选择：

如果您知道在第一次批量加载或任何后续批量加载时不会命中int（10），则可以使用INSERT INTO phone_numbers (phone_number) VALUES (%values%) ON DUPLICATE KEY UPDATE id=id; 这将在id字段中留下空白。 但是在完成批量加载后，您可以删除ID列，然后重新添加它，这将重新创建所有ID，没有间隙。

After reading the other article one way would be to have a temp table that you bulk insert into. Then select the rows from the temp table into our actual table. The duplicate rows would be removed at that point and the auto_increment field in the actual table would be correct.

CREATE TABLE PHONE_NUMBERS (id int(10) NOT NULL AUTO_INCREMENT, phone_number varchar(64), primary key (id), unique(phone_number) ); CREATE TEMPORARY TABLE TMP_PHONE_NUMBERS ( phone_number varchar(64), unique(phone_number) ); ' bulk insert INSERT INTO tmp_phone_numbers (phone_number) VALUES (%values%) ' remove phone numbers that already exist. This will create a unique ' set of phone numbers that do not exist in the real table. DELETE FROM tmp_phone_numbers WHERE phone_number in (SELECT phone_number from phone_numbers); ' copy into real table INSERT INTO phone_numbers (phone_number) SELECT phone_number FROM tmp_phone_numbers; ' Temp table is dropped when your connection is closed.

Here is another option:

If you know you won't hit int(10) in the first bulk load or any subsequent bulk loads you can use INSERT INTO phone_numbers (phone_number) VALUES (%values%) ON DUPLICATE KEY UPDATE id=id; which will leave gaps in the id field. But after you are done bulk loading, you can remove the ID column, then re-add it back which will recreate all of your id's with no gaps.