MySQL查询来重新排序字段值?

I have table like this:

我有这样的桌子:

===============
| rank | name |
===============
|    3 | john |
|    6 |  bob |
|   10 | alex |
|   11 | brad |
|   12 | matt |
|   34 | luke |
|  145 |  ben |
===============

(this table is an example. In reality my table consists of ~5000 rows of data).

(这张表就是一个例子。实际上，我的表由大约5000行数据组成)。

Is there a query to reorder the rank values starting from 1 and going up so it ends up like this:

是否有一个查询来重新排序从1开始的rank值，然后向上，结果是这样的:

===============
| rank | name |
===============
|    1 | john |
|    2 |  bob |
|    3 | alex |
|    4 | brad |
|    5 | matt |
|    6 | luke |
|    7 |  ben |
===============

It would be preferable to do this in 1 or 2 queries, not 1 query for each row since my table has 5000+ rows.

最好是在1或2个查询中执行，而不是对每一行执行1个查询，因为我的表有5000多行。

EDIT: Sorry I wasn't clear. I am trying to UPDATE the values in the database.

编辑:对不起，我不太清楚。我正在尝试更新数据库中的值。

4 个解决方案

#1

This is a little crude but will work in a pinch.

这有点粗糙，但在必要的时候会起作用。

First order your table correctly just incase

先把你的桌子订好，以防万一

ALTER TABLE tablename ORDER BY rank

按等级改变表的表名

Then drop the column

然后把列

ALTER TABLE tablename DROP rank

改变表表名，降低等级

Then add it again, with auto increment

然后再添加一次，自动递增

ALTER TABLE tablename ADD COLUMN rank INT NOT NULL AUTO_INCREMENT FIRST

先修改表tablename，添加列秩INT而不是NULL AUTO_INCREMENT

The auto increment will take care of numbering them in order, plus you don't have to loop through each row.

自动增量将按顺序对它们进行编号，而且您不必对每一行进行循环。

#2

Here is the solution I came up with for this problem:

下面是我提出的解决这个问题的方法:

1.Create a temporary table without any keys

1。创建一个没有任何键的临时表

CREATE TEMPORARY TABLE tempTable (
  id INT(11) NOT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT;

2.Populate the temporary table with data from the original table, ordered by rank

2。用来自原始表的数据填充临时表，按秩排序

INSERT INTO tempTable SELECT id FROM myTable ORDER BY rank;

3.Add auto-incrementing rank column, giving all rows a unique rank, counting up from 1

3所示。添加自动递增的rank列，给出所有行的唯一秩，从1开始计数

ALTER TABLE tempTable
    ADD COLUMN `rank` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ADD PRIMARY KEY (`rank`);

4.Update the original table with a join to the temp table, overriding the original ranks

4所示。使用临时表的连接更新原始表，覆盖原始的级别

UPDATE myTable 
INNER JOIN tempTable
    ON myTable.id = tempTable.id
SET myTable.rank = tempTable.rank;

5.Drop the temp table

5。删除临时表

DROP TABLE tempTable;

#3

An alternative to a strict MySQL solution would be to loop through the rows with a scripting language. Not a great idea if you have a large table, but could be acceptable if this is a one time fix.

严格的MySQL解决方案的替代方案是使用脚本语言循环行。如果您有一个大的表，这不是一个好主意，但是如果这是一个一次性的时间修复，这是可以接受的。

In PHP

在PHP中

$db = mysql_connect('localhost', 'user', 'password');
mysql_select_db('database', $db);

$result = mysql_query("SELECT rank
                        FROM myTable
                        ORDER BY rank");

$i = 1;

while ($row = mysql_fetch_assoc($result)) {

    mysql_query("UPDATE myTable
                SET rank = " . $i++ . "
                WHERE rank = " . $row['rank']);
}

Note that this will only work if rank is unique and you traverse in an order.

请注意，只有当rank是唯一的并且按顺序遍历时，这才有效。

#4

set @a:=(select max(id) from mytable)+1;
update mytable set id=(@a:=@a+1)
order by id;
set @a := 0;
update mytable set id=(@a:=@a+1)
order by id;

simple way, work for me. easy way.

简单的方法，为我工作。简单的方法。

#1