CODEWITHSUNDEEP
mysqlinsertauto-increment
firetiger77
firetiger77

Reputation: 369

Prevent auto-increment on MySQL duplicate insert with multiple values

Is there an accepted practice for bulk inserting values that don't exist into a table without auto-incrementing when you attempt to insert a row that already exists?

There's a great answer for the single-row insert case at: Prevent auto increment on MySQL duplicate insert

However, for insertion efficiency I'd like to insert a large number of rows with a single SQL command. (i.e.:INSERT INTO myBigTable VALUES ((value1_row1,value2_row1),(value1_row2,value2_row2) ... )

ADDITIONAL INFO: I would like to have all the ID's available, since my table has the potential of becoming extremely large. Changing the auto_increment variable size to a BIGINT would be a last resort. My insertion application will attempt to insert a large number of already-existing rows on a regular basis (think stock price updates), so I'll effectively have a large number of auto-incremented ID's skipped.

WHY I'M USING AUTO-INCREMENT: I believe that for query speed, I should be using an integer index into my (very large) table as the primary key instead of a combination of string fields. I also believe that I should use auto_increment, so MySQL handles concurrency for me.

Upvotes: 0

Views: 386

Answers (1)

Barmar
Barmar

Reputation: 780861

You can use the technique in the other question, using a UNION query:

INSERT INTO yourTable (col1, col2, ...)
SELECT $row1_value1, $row1_value2, ...
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable
    WHERE unique_col = $row1_value1)
UNION ALL
SELECT $row2_value1, $row2_value2, ...
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable
    WHERE unique_col = $row2_value1)
UNION ALL
SELECT $row3_value1, $row3_value2, ...
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable
    WHERE unique_col = row1_value1)
UNION ALL
SELECT $row4_value1, $row4_value2, ...
FROM DUAL
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable
    WHERE unique_col = $row4_value1)

Upvotes: 1

Related Questions