Home > mysql > Use variables in SQL

Use variables in SQL

February 27th, 2007 Leave a comment Go to comments

What was that ?

Yes.. I mean use variables in SQL. This can be quite interesting, though there may be a lot of people who might comment, that this kills readability, or does not induce even a ray of benefit, well I am not into an argument. And the method I am testing only with MySQL. In several situations, it has proven to be more faster, and as of recent times, we have started to send the whole of the code as a single operation letting mysql to handle the query splitting.

Suppose we need to insert first into a login table, to generate the user_id, which is auto-increment, and then into a set of tables, normally, we would go around insert into the table, then use mysql_insert_id(), to get the last inserted id, to process the rest of the tables. Now think if we used

insert into [table1] (fieldnames) values (values);
select last_insert_id() into @ii;
insert into [table2] (fkId,fieldnames) values (@ii,values);
insert into [table3] (fkId,fieldnames) values (@ii,values);

There are several other example uses for selecting into a variable, but the limitations are that you can select only a single field and single row.

Any comments :

Categories: mysql Tags:
  1. No comments yet.
  1. No trackbacks yet.

− four = five