Last_insert_id returns 0 for autoincremented column with letters and numbers

Question

I used a trigger to generate id: ug01, ug02... I want to insert them into the foreign key column but last_insert_id() returns 0 creating an error. How can I get the value of the "last_insert_id" as it is in the primary key column.

Answer 1

As stated in the MySQL documentation, LAST_INSERT_ID() returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column.

In your case, you are inserting the id, so an AUTO_INCREMENT value is not generated, thus LAST_INSERT_ID returns 0.

Cross check this by execute MySql command: SELECT LAST_INSERT_ID();