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

I think your problem could best be solved by creating a new table and a trigger to keep track of the newly inserted hash values in the main table.

For example

CREATE TABLE test_table (
    hash VARCHAR(30) NOT NULL PRIMARY KEY,
    fullname VARCHAR(120) NOT NULL
);

CREATE TABLE hash_tracker(
    hash VARCHAR(30) NOT NULL,
    created_at DATETIME NOT NULL
);

DELIMITER $$

CREATE TRIGGER `test_trigger`
AFTER INSERT ON `test_table`
FOR EACH ROW
BEGIN
    INSERT INTO hash_tracker VALUES (NEW.`hash`, NOW());
END$$

DELIMITER ;

Then after each insert on my test_table, i can run the following query

SELECT hash FROM hash_tracker ORDER BY created_at DESC LIMIT 1;

to get the most recently inserted hash.

Answer 2

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.