MySQL如何通过自动增加列来更新表 [英] mysql how to update a table by auto-incrementing a column

问题描述

具有10万行的MySql 5.3表具有一个主键.

A MySql 5.3 table with 100K rows has a primary key.

还有一个整数列，它不是键的一部分.我想更新此列以包含表的唯一编号.例如.对于第一个记录，它应包含1，对于第二个记录，应包含2，依此类推.

There is also an integer column which is not part of the key. I would like to update this column to contain a unique number for the table. E.g. for the first record it should contain 1, for the second 2 etc.

这也可以是自动递增的列，但是MySql不允许在非关键列上自动递增.我不希望此列成为键的一部分，因为它是从文件等中填充的方式.

This could as well be an auto-increment column, but MySql does not allow auto-increment on non-key columns. I don't want this column to be part of the key, because of the way it gets populated from a file etc.

那么这样的查询看起来如何?

So how such a query would look like?

推荐答案

我不知道您为什么要这样做，但是可能的解决方法是:

I don't know why do you want to do something like this, but a possible solution is this:

set @rownum:=0;
update <table> set column = @rownum:=rownum+1 order by <field>

这篇关于MySQL如何通过自动增加列来更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！