如何在不增加重复项的auto_increment列的情况下执行批量mysql？

Question

这是我的数据库模式

+------------------+------------------+------+-----+---------------------+----------------+
| 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也会增加。

这个问题：Prevent auto increment on MySQL duplicate insert不处理批量插入。我想做这样的事情：

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表并不能很好地处理多个值。

有什么想法吗？MySQL 5.5.

Answer 1

在阅读了另一篇文章之后，一种方法是将批量插入的临时表插入其中。然后从temp表中选择行到实际表中。重复的行将在此时删除，而实际表中的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，没有任何空白。

Answer 2

由于INSERT simple确实会增加表上的自动增量计数器(即使对数据不做任何更改)，一个简单的方法就是在事务结束时恢复自动增量值，方法是利用引擎逻辑，在试图强制执行过低的值时，总是选择一个大于当前最高AI值的整数：

ALTER TABLE phone_numbers AUTO_INCREMENT = 0;

这样做不需要重新生成表，可以按以下方式进行测试：

SELECT MAX(id) FROM phone_numbers;
INSERT IGNORE INTO phone_numbers (phone_number) VALUES ("0123456789");
SELECT MAX(id) FROM phone_numbers;
INSERT IGNORE INTO phone_numbers (phone_number) VALUES ("0123456789");
SELECT MAX(id) FROM phone_numbers;
ALTER TABLE phone_numbers AUTO_INCREMENT = 1;
INSERT IGNORE INTO phone_numbers (phone_number) VALUES ("1000000000");
SELECT MAX(id) FROM phone_numbers;

+---------+
| MAX(id) |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

Query OK, 1 rows affected (0.00 sec)

+---------+
| MAX(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---------+
| MAX(id) |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

Query OK, 1 rows affected (0.33 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

+---------+
| MAX(id) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Answer 3

只需使用插入忽略。它将忽略因重复键而失败的任何插入。其余的插入将通过罚款。