2

I have a MySQL table that has a two column primary key, the latter of which was auto increment when it used to be MyISAM. When I switched it to InnoDB, I had to convert the auto_increment column to a normal int that updated itself to max()+1 with a before insert trigger. No problems so far..

The Java PreparedStatement that does the insert needs to know the last_insert_id of the row that was just inserted, and without the RETURNING function of Oracle/etc. I'm not sure how best to retrieve that value. Is there a way to get that emulated auto increment value without wrapping all of my insert functions into a stored function or requiring a second query?

Just for reference, something along the lines of (just writing this out, so might not be perfect, but it should get the idea across):

CREATE TABLE t (
  id1 int NOT NULL, 
  id2 int NOT NULL DEFAULT '0',
  PRIMARY KEY (id1, id2)
) ENGINE=InnoDB;
DELIMITER $$
CREATE TRIGGER t_auto_increment BEFORE INSERT ON t
FOR EACH ROW
BEGIN
    SET NEW.id2 = (SELECT MAX(id2)+1 FROM t WHERE id1=NEW.id1);
END$$
DELIMITER ;

I know with other DB engines I can just tell it to return id2 with the insert query, which I assume would solve my problem, but MySQL doesn't seem to support this. I'd rather not make what would be a number of stored functions to wrap every insert, so the best I thought of was setting a variable and looking for it afterwards.. though I'd much prefer something that doesn't require a second query to the server, of course, despite said query would be light.

user1442811
  • 21
  • 1
  • Using JDBC? See [How to get the insert ID in JDBC?](http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc). – eggyal Jun 07 '12 at 18:01
  • 1
    why did you convert it? InnoDB also supports auto increment columns, you should not be doing this. – Maurício Linhares Jun 07 '12 at 18:06
  • Because it's the trigger generating the key and not the actual DB, it doesn't consider it a generated key, like it would with an actual (and not emulated) auto increment. – user1442811 Jun 07 '12 at 18:07
  • InnoDB does not support this table if id2 is the auto incrementing column, as it is a secondary column in a composite index. MyISAM does. See http://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment – user1442811 Jun 07 '12 at 18:14
  • InnoDB does support this. First create ID1 and make it primary key. Then, add ID2 column and make it a unique key and auto_increment. Then, add ID2 column to the primary key. Or, in your case, just add a unique index on ID2, then make it auto_increment. – Marcus Adams Jun 07 '12 at 18:20
  • Correction: the index on ID2 doesn't have to be unique. – Marcus Adams Jun 07 '12 at 18:26
  • Gasp! This would have saved me a lot of headaches (and still will, moving forward). So the restriction on InnoDB is merely that there be _a_ key with the auto_increment column and only the auto_increment column, and it can then be part of a composite primary index or whatever other additional indexes its heart might desire? – user1442811 Jun 07 '12 at 18:46

0 Answers0