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.

Answer 3

The easiest way I found to do this is to set a variable.

Unlike using LAST_INSERT_ID which only returns and INT this way you can use other unique identifiers.

SET @id = UUID();

INSERT INTO users (
  id
)
VALUES (
  @id
);

SELECT * FROM users WHERE id = @id;

Answer 4

I assume that you need the ID to find your just inserted row, rather to find the last inserted row. In a web application, you can never be sure that the last inserted row is the one you have just created.

You could use a GUID as id in this case. A GUID is usually stored as a string of length 36 or as a 16byte blob. The GUID can be created before inserting the row, and then can be stored while inserting the row.

Since the id is not auto incremented as you stated, you have to generate it anyway before inserting the row. The safest way to do this is to create a GUID which should be unique enough. Otherwise you would have to determine the last unused ID, what can be tricky and risky.

Answer 5

If you want to get a custom last_inserted ID, you must implement a procedure that will make the insert statment on your DB. At the end, just print the ID and use the PHP (if PHP is your main script) sender to return the generated row.

EXAMPLE:

DROP PROCEDURE IF EXISTS insert_row;
DELIMITER $$

CREATE PROCEDURE insert_row(IN _row_id VARCHAR(255), IN _description VARCHAR(255))
BEGIN
    SET @last_inserted_id = _row_id;
    SET @sql = CONCAT("INSERT INTO test VALUES ('", _row_id, "','",_description,"')");
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SELECT @last_inserted_id AS LAST_INSERT_ID;
END;
$$

DELIMITER ;
#
#
#
#------- HOW TO USE IT ? ---------------
CALL insert_row('Test001','the first test line');

Answer 6

This worked for me in XAMPP

$qry = $con->query("INSERT INTO test_table(tbl_id, txt) VALUES(last_insert_id('15'), 'test value')");
print_r($con->insert_id);

Answer 7

No.

There is no inherent ordering of relations, no "last-inserted record". This is why the AUTO_INCREMENT field exists, after all.

You'd have to look in logs or cache the value yourself inside your application.

Answer 8

There's no way with mysql. But you can to do it programmatically. Without an auto-incrementing ID column there's no way for the database to know which records were inserted last.

One way to do is use such as a column containing timestamp or datetime values. and get id of latest value of tmestamp to get last inserted record