Search code examples
mysqlinsertinnodbbulkinsertmyisam

MySQL Bulk Insert Ignore

I've a few questions regarding the MySQL bluk insert Ignore statement, both for InnoDB and MyISAM.An example:

INSERT IGNORE INTO table(`reference`) VALUES ('1','2','3')

Reference is an unique key also.

  1. Is a bulk insert atomic? So if I send this to the database can I be assured that no other inserts occur in between this statement?

  2. A batch insert returns the value of the First ID inserted of the batch. However, what happens if the first insert happens to be a duplicate (do I get a 0 as a return value or is the next (successful insert returned, I couldn't find this in the docs)

Kind regards,

Solution

  • Many things are going on here...

    • Syntax error: You have 1 column, but 3 values. Perhaps you meant this instead? ... VALUES (1), (2), (3).
    • After that fix, your one statement is essentially equivalent to three 1-row INSERT IGNOREs wrapped in a transaction (if using InnoDB).
    • Because of IGNORE, any duplicate key will not abort the insert.
    • The VARIABLE innodb_autoinc_lock_mode controls what happens with AUTO_INCREMENT during a multi-row INSERT. Need to know its values to answer this question completely. By default, the AUTO_INCREMENT id will be bumped up by 3, regardless of how many reference values are duplicates. This leads to a surprise "burning" of ids. (And eliminates your question about what the "First ID" is.)
    • Nothing is "atomic" in a non-transactional engine such as MyISAM. In particular, ...
    • With MyISAM, a multi-row write (insert/update/delete) that is interrupted (eg, by power failure), may have done some of the rows -- you cannot tell how many. (This is an important reason to avoid MyISAM.)