MySQL How do I retrieve ID of LAST_INSERT_ID when using INSERT IGNORE INTO?

Question

Scenerio:

1. I'm inserting a CSV file into my database.
2. I'm using jdbc driver to iterate over the file and do the my inserts
3. A name column is made unique so inserts do not change the table when same value arrives
4. I'm using INSERT INTO IGNORE to keep the iteration from breaking when event happens
5. I'm using LAST_INSERT_ID() to add as FK in next table of insert
6. I get 0 when INSERT_IGNORE happens

How do I do fix this?

 Connection con = null;

    try
    {
      Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
      con.setAutoCommit(false);

      Statement s1 = (Statement) con.createStatement();
      s1.executeUpdate("INSERT IGNORE INTO ORIGIN (ORI_NAME) VALUES(\""
          + d.getOriginName() + "\")");

      Statement s2 = (Statement) con.createStatement();
      s2.executeUpdate("INSERT IGNORE INTO STOCK (ORI_ID,S_TITLE) VALUES ("
          + "(SELECT ORI_ID FROM ORIGIN WHERE ORI_ID =LAST_INSERT_ID()),\""
          + d.getStockTitle() + "\")");

     }

      con.commit();

    }
    catch (Exception e)
    {
      if (con != null)
        try
        {
          con.rollback();
        }
        catch (SQLException e1)
        {
          e1.printStackTrace();
        }

      e.printStackTrace();
    }
    finally
    {
      if (con != null)
        try
        {
          con.close();
        }
        catch (SQLException e)
        {
          e.printStackTrace();
        }
    }

Example of CSV file to be inserted into the database

US, P1, M, 200, 344
US, P1, L, 233, 344
US, P2, M, 444, 556
MX, Q4, L, 656, 777

Tables: http://sqlfiddle.com/#!2/b5752

Answer 1

If you want to get an ID for a subsequent insert, your code has to test for a 0 value of LAST_INSERT_ID and if it gets it, do a SELECT on the table to find the right value for the FK for your insert.

In pseudo-code, this would look like:

$err = mysql("insert ignore into mytable values ($x, $y)")
if($err){
    do something for an error
}
$id = mysql("select last_insert_id()");
if($id == 0){
    $id = mysql("select id from othertable where condition is true for the record I want to reference in my insert");
    if($id == 0){
        do something for error
    }
}
mysql("insert into othertable VALUES ($id, other stuff)")

You'll have to translate that to your own application and language.

Answer 2

Are you familiar with MySQL's ON DUPLICATE KEY UPDATE syntax?

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

 // Results are the same as doing an update.
 // Note the way the PK can be used to avoid an IGNORE.
 s1.executeUpdate("INSERT INTO ORIGIN (ORI_NAME) VALUES(\""
          + d.getOriginName() + "\" ON DUPLICATE KEY UPDATE ORI_ID=ORI_ID)");

A pratical example here involving the maintenance of failed email addresses for a site:

http://blog.developpez.com/james-poulson/p10016/sql/ne-rien-modifier-en-cas-de-duplicate-key/

As for retrieving the last inserted id the following is taken from the mysql.com link above:

Suppose that id is the AUTO_INCREMENT column. To make LAST_INSERT_ID() meaningful for updates, insert rows as follows:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;