User-defined variable in an INSERT query for MySQL

insert, mysql, user-defined, variables

Solution

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);

Problem

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?

Original source