Geek asks and answers

Custom variable in INSERT query for MySQL

I need to use a custom variable in an INSERT query for MySQL, see the example below:

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 does not work and 0 is inserted. What am I doing wrong?

+3
source share
1 answer

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

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

... if you are not using multiple attachments using this identifier.

In this case, the correct syntax for using a variable is to do this 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);
+12
source

More articles:


All Articles