Menu
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?
331
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
There are two ways to assign a value to a user-defined variable. You can use either := or = as the assignment operator in the SET statement. For example, the statement assigns number 100 to the variable @counter. The second way to assign a value to a variable is to use the SELECT statement.
Mysql also supports the concept of User-defined variables, which allows passing of a value from one statement to another. A user-defined variable in Mysql is written as @var_name where, var_name is the name of the variable and can consist of alphanumeric characters, ., _, and $.
User variables are written as @ var_name , where the variable name var_name consists of alphanumeric characters, . , _ , and $ . A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var' , @"my-var" , or @`my-var` ). User-defined variables are session specific.
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);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
