INSERT INTO with Increment variable

Question

I would like to have an INSERT INTO query which one of the fields I update is a calculated var 'mycount' - which will be the number of the inserted row in the query.

For example: If I insert 3 rows, I'd like this var to be '1' for the first row inserted, '2' for the second, and so forth.

SET @mycount=0;
INSERT INTO my_table
(@mycount,field1,field2,field3...)
SET @mycount=@mycount+1;
SELECT @mycount,field1,field2,field3..
FROM my_table
WHERE id IN (id1,id2,id3..);

This code returns an error. How can I declare a variable inside an INSERT INTO query and have it incremented with every row inserted ?

IMPORTANT - I do not need an AUTO-INCREMENT column - this is a part of a calculation that needs to be performed only in this specific INSERT INTO query, and it is only part of the calculation. What I need is really a calculation of (number_of_inserted_row+some_other_calculation) but I just simplified it for the sake of the question.

Answer 1

Well, usually an auto_increment column is used for this. If you don't want to for whatever reason, you can do it like this:

INSERT INTO my_table
(your_quasi_auto_increment_column, field1, field2, field3...)
SELECT (@mycount := @mycount + 1) + <other_calculation>, field1, field2, field3..
FROM my_table
, (SELECT @mycount := 0) var_init_query_alias
WHERE id IN (id1,id2,id3..);