INSERT INTO with Increment variable

问题:

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.

回答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..);

回答2:

you can do this using add new filed which you want to insert as like count_number and define auto increment this filed and not need to insert in this query

 SET @test=1;
 INSERT INTO test (`test`,`test2`,`test3`)
 SELECT (@test := @test +1) AS `test`,`test2`,`test3`
 FROM test

if you want to add new file then then check this code

SET @count_value=0;
 INSERT INTO test (`count_value`,`test`,`test2`,`test3`)
 SELECT (@count_value := @count_value +1) AS `count_value`,`test`,`test2`,`test3`
 FROM test

in my_table1 add field count_number and type int and add auto increment then it will work