Is there a way to get last inserted id of a NON - auto incremented column in MySQL?

Question

I know how LAST_INSERT_ID() works for auto incremented columns, but I cannot find a way to get the last id I inserted for a non auto incremented column.

Is there a way I can do that?

Answer 1

you can easily do that using the same LAST_INSERT_ID().

INSERT INTO thetable (id, value)
VALUES (LAST_INSERT_ID(126), 'some data');

SELECT LAST_INSERT_ID();  -- returns 126

Answer 2

I'm assuming you want the retrieve this last inserted id at some later point after inserting it, since if you need it right after inserting it you obviously would already know what the id is.

The only way you'll be able to get that is to have another column on the table that can indicate which row was last inserted, such as a timestamp or datetime column. If your ids are unique and increasing, you can just use that column. Then you just select 1 row ordered by that column in descending order.

For example

INSERT INTO my_table (id, timestamp) VALUES (123, NOW())

SELECT id FROM my_table ORDER BY timestamp DESC LIMIT 1

Edit: as per the comments below, you're much better off using an AUTO_INCREMENT column, though this column doesn't have to be the id column, you could add an auto-increment insert_order column of type Int and simply order by that.