用MySQL中的整型数字序列填充列

Question

我必须修改我的表，添加一个列，我必须用整型数序列填充它。有没有人知道我该怎么填这一栏？我试过这个：

set @i = 1;
Update db.table set new_column=(@i := @i+1) where id>0;

但这只是用数字2填充所有行。=(

Answer 1

您可以轻松地做到这一点：

UPDATE db.table 
CROSS JOIN ( SELECT @myid := 0) AS parameter
SET new_column = @myid := (@myid +1);

Answer 2

试一试

Update your_table
join
(
   select id, @i := @i+1 as rank
   from your_table
   cross join (select @i := 0) ia
   where id > 0
   order by id
) tmp on tmp.id = your_table.id
set your_table.new_column = tmp.rank

Answer 3

所有这些答案对我都是有效的。然后我又做了一次

delimiter #
create procedure fill_column()
begin
  declare v_max int unsigned default 83;-- number of registers that I have
  declare v_counter int unsigned default 0;
  declare a int default 0;
    start transaction;
    while v_counter < v_max do
       update db.tb_table set new_column= (@a := @a+1) where id> 0; 
       set v_counter=v_counter+1;
    end while;
 commit;
end #
call fill_column();

谢谢你们=)