Output primary key after insert a row in Mysql

Question

I have created a trigger to help me to make the primary key when I insert a row. I want to get the primary key I just insert. But because the primary key is not auto_increment I cannot use

SELECT LAST_INSERT_ID();

So how can I get the primary key?

Here is the trigger

CREATE TRIGGER before_insert_user_info
BEFORE INSERT ON USER_INFO
FOR EACH ROW
SET new.uID = CONCAT('U', ((SELECT MAX(CAST(SUBSTRING(uID, 2, length(uID)) AS UNSIGNED)) FROM USER_INFO)+1));

And here is the insert

INSERT INTO USER_INFO(name) VALUES ('Peter');

http://stackoverflow.com/questions/1685860/php-how-to-get-last-inserted-id-of-a-table — Lauri Elias, Jan 08 '14 at 12:41
It only can use in AUTO_INCREMENT, but my primary key is not AUTO_INCREMENT. — We Rub Chan, Jan 08 '14 at 12:47
In your trigger, only set the value if it's null, and when you need the id, generate it yourself. — Marcus Adams, Jan 08 '14 at 14:15

score 0 · Answer 1 · answered Jan 08 '14 at 13:25

It is kind of unusual to rely on a trigger to generate a primary key. There is no way to retreive data back from a trigger (e.g. it has no return value). But you can reuse your tigger's logic to retreive the generated value:

INSERT INTO user_info VALUE (@newUID, ...);
SELECT MAX(CAST(SUBSTRING(@newUID, 2, LENGTH(@newUID)) AS UNSIGNED)) AS last_insert_id
FROM user_info; -- this is the generated value

Wrap these two statements in a transaction to make sure a new user is not inserted in between.

As an alternative, I would create another INT AUTO_INCREMENT column, so that you can retreive your new row after insertion with LAST_INSERT_ID();

And while we are at it, I would make this new field the (surrogate) primary key. The same trigger could still generate the "public" user ID, but then we would be back to a more usual architecture.

Last food for thoughts: do you really need to store your user ID's with the U prefix? Perhaps you could just store a plain INT value, and preppend the U on selection.

Output primary key after insert a row in Mysql

1 Answers1