插入带有增量变量的INTO [英] INSERT INTO with Increment variable

问题描述

我想进行一个INSERT INTO查询，我更新的字段之一是计算得出的var'mycount'-这将是查询中插入行的编号.

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.

例如: 如果我插入3行，我希望此变量在插入的第一行中为'1'，在第二行中为'2'，依此类推.

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

此代码返回错误. 如何在INSERT INTO查询中声明一个变量，并在插入的每一行中使它递增?

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

重要信息-我不需要AUTO-INCREMENT列-这是计算的一部分，只需要在此特定的INSERT INTO查询中执行，并且仅是计算的一部分. 我真正需要的是(number_of_inserted_row + some_other_calculation)的计算，但出于问题的考虑，我只是对其进行了简化.

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.

推荐答案

好吧，通常为此使用auto_increment列.如果您出于某种原因不想这样做，可以这样做:

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

这篇关于插入带有增量变量的INTO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！