MySQL: Get last inserted primary key without auto_increment

Question

I removed my record ID while I'm using unique hashes as a primpary key. This primary key obviously cannot auto increment.

Now my question is how to retreive the last inserted primary key? MySQL returns 0 on LAST_INSERT_ID() while it's not an auto increment column.

Answer 1

The fact

There's no equivalent to LAST_INSERT_ID() returning a non integer value.

One can simply

1. The easy approach

Add an integer column which can either be auto incremented or non auto incremented.

To have it auto incremented correctly one has at least to implement an algorithm in MySQL itself or with a language of their choice to fill the existing records with the new IDs.

2. The more complex approach

https://stackoverflow.com/a/53481729/2323764 (@kellymandem)

Add a second table managing the ID and triggered by the origin table without IDs.

One cannot

I found this very promising Q/A.

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

It's mentioned there to use LAST_INSERT_ID() in the INSERT statement already.

But

INSERT INTO `table` ( `non_integer_column` ) VALUES ( LAST_INSERT_ID( 42 ) );
SELECT LAST_INSERT_ID( );
-> 42

INSERT INTO `table` ( `non_integer_column` ) VALUES ( LAST_INSERT_ID( 'a0b1c2d3e4f5' ) );
SELECT LAST_INSERT_ID( );
-> 0

Non integer values will be ignored.