User-defined variable in an INSERT query for MySQL

Question

I need to use user-defined variable in an INSERT query for MySQL, see an example bellow:

INSERT INTO `posts`(`id`) VALUES(NULL);
SET @last_insert_id = LAST_INSERT_ID();
INSERT INTO `comments`(`id`, `post_id`) VALUES(NULL, "@last_insert_id");

This example doesn't work and inserted 0. What am I doing wrong?

Michael Berkowski · Accepted Answer · 2012-05-22 15:01:05Z

19

There is no need to store it in a variable. You can just call LAST_INSERT_ID() inside the following INSERT statement.

INSERT INTO `comments`(`id`, `post_id`) VALUES (NULL, LAST_INSERT_ID());

... unless you have multiple inserts to perform using that id.

In that case, the proper syntax for using the variable is to do so without quotes:

INSERT INTO `posts`(`id`) VALUES (NULL);
SET @last_insert_id = LAST_INSERT_ID();
/* Several new entries using the same @last_insert_id */
INSERT INTO `comments`(`id`, `post_id`) VALUES (NULL, @last_insert_id);
INSERT INTO `comments`(`id`, `post_id`) VALUES (NULL, @last_insert_id);
INSERT INTO `comments`(`id`, `post_id`) VALUES (NULL, @last_insert_id);
INSERT INTO `comments`(`id`, `post_id`) VALUES (NULL, @last_insert_id);
INSERT INTO `comments`(`id`, `post_id`) VALUES (NULL, @last_insert_id);

answered May 22, 2012 at 15:01

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ihor Timoshenko Over a year ago

I need to use user-defined variable. Above was a simple example.

shareef Over a year ago

is it consistent enough i mean if some one else or cron is doing some inserts will the SET @last_insert_id = LAST_INSERT_ID(); takes the last id from which auto increment of which table ?!

Michael Berkowski Over a year ago

@shareef LAST_INSERT_ID() returns a value of the last ID inserted from the current database connection. Another user or another cron or similar would be operating on from a different database connection with its own last_insert_id values, and so the values would be consistent.

Collectives™ on Stack Overflow

User-defined variable in an INSERT query for MySQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related