使用序列号 mysql 更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6617056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
updating columns with a sequence number mysql
提问by Dejell
I have a table with the columns: (this is only an example I have 50K records)
我有一个带有列的表：（这只是我有 50K 记录的一个例子）
Name, Number
Joe Null
Michael Null
Moses Null
I to update the number with a sequence number from 1-3 so it will look like this:
我用 1-3 的序列号更新数字，所以它看起来像这样：
Name, Number
Joe 1
Michael 2
Moses 3
How can I do it in SQL for Mysql in one SQL command
如何在一个 SQL 命令中在 SQL for Mysql 中执行此操作
回答by triclosan
SET @rank:=0;
update T
set Number=@rank:=@rank+1;
UPDATE
更新
alternative way with one statement
一个声明的替代方式
UPDATE T
JOIN (SELECT @rank := 0) r
SET Number=@rank:=@rank+1;
回答by Nicolae Albu
You could try setting the Numberto AUTO_INCREMENT so the numbers will be generated:
您可以尝试将 设置Number为 AUTO_INCREMENT 以便生成数字：
ALTER TABLE your_table MODIFY Number INT AUTO_INCREMENT
Other than that, you probably need: a) stored routines b) application code
除此之外，您可能需要：a) 存储例程 b) 应用程序代码
回答by Joining Dots
I struggled to find an answer online and found the following method worked for me, based on some of the info above but with a different update approach. Posting here in case it helps others too.
我努力在网上找到答案，发现以下方法对我有用，基于上面的一些信息，但使用了不同的更新方法。在这里发布以防它也帮助其他人。
Note: for my dataset, the rank is a one-time fixed calculation, it won't change. I wanted to add it as a column to save running a computation each time I pull the data from MySQL into Python.
注意：对于我的数据集，排名是一次性固定计算，不会改变。我想将它添加为一列，以节省每次将数据从 MySQL 提取到 Python 时运行的计算。
Solution I used:
我使用的解决方案：
- set rank to 0 (as above)
- create a temporary table containing the select query to compute the rank
- update the original table with the computed ranks.
- 将等级设置为 0（如上）
- 创建一个包含选择查询的临时表来计算排名
- 用计算出的等级更新原始表。
Sample names used, rowid is the unique identifier for each row, value is the metric that will be used to determine the rank for each row
使用的样本名称，rowid 是每一行的唯一标识符，value 是用于确定每一行排名的指标
SET @rank=0;
CREATE TEMPORARY TABLE rank_temp
AS
(SELECT rowid, value, @rank:=@rank+1 AS rank FROM source_table ORDER BY value ASC);
UPDATE sourcetable a, rank_temp b
SET a.rank = b.rank
WHERE a.rowid = b.rowid;
It's a crude method but has worked on my dataset.
这是一种粗略的方法，但对我的数据集有效。
