MySQL 一次更新/增加多行上的单列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5207267/
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
Update/Increment a single column on multiple rows at once
提问by Jordan Feldstein
I'm trying to add rows to a column, keeping the orderof the newest column set to one, and all other rows counting up from there.
我正在尝试向一列添加行，将order最新列的设置为一，所有其他行从那里开始计数。
In this case, I add a new row with order=0, then use this query to update all the rows by one.
在这种情况下，我添加了一个 order=0 的新行，然后使用此查询将所有行更新一。
"UPDATE favorits SET order = order+1"
However, what happens is that all the rows are updated to the same value. I get a stack of favorites, all with order 6 for example, when it should be one with 1, the next with 2 and so on.
但是，发生的情况是所有行都更新为相同的值。我得到一堆收藏夹，例如所有的订单都是 6，当它应该是 1 的时候，下一个是 2，依此类推。
How do I update these rows in a way that orders them the way they should be?
如何以应有的方式对这些行进行排序？
Thanks,
~Jordan
谢谢
~乔丹
回答by Joseadrian
SET @a = 0;
UPDATE favorits SET order = @a:=@a+1;
回答by Simon
What you are telling the DB to do it update EVERY record in the table by incrementing its order field by one. So EVERY record will always have the same value. I beleive you are trying to set the latest record to 1 and the oldest record set to (no records+1).
您告诉数据库执行的操作是通过将其 order 字段增加 1 来更新表中的每条记录。因此，每条记录将始终具有相同的值。我相信您正在尝试将最新记录设置为 1，并将最旧记录设置为（无记录 + 1）。
So maybe you can try this:
所以也许你可以试试这个：
set @count = (SELECT COUNT(ID) from favorits);
UPDATE favourits SET order = @count-ID+1
Now this is assuming that no records are deleted. In that case you would have to adjust for this and set the latest record to 1 and then increment the order value for each previous record sorted by ID.
现在假设没有记录被删除。在这种情况下，您必须为此进行调整并将最新记录设置为 1，然后增加按 ID 排序的每个先前记录的顺序值。
So this would be the approach:
所以这将是方法：
set @i=0;
set @Count=(SELECT COUNT(*) from favorits);
UPDATE favorits SET `order` = @Count-(@i:=@i+1)+1;
回答by Brian Gottier
I got intereted, so I came up with the following solution. Consider the following table:
我很感兴趣，所以我想出了以下解决方案。考虑下表：
CREATE TABLE `placements` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`user` varchar(12) NOT NULL,
`place` tinyint(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `placements` (`id`, `user`, `place`) VALUES
(1, 'Adam', 1),
(2, 'Bill', 2),
(3, 'Carl', 3),
(4, 'Doug', 4),
(5, 'Eddy', 5),
(6, 'Frank', 6),
(7, 'George', 7),
(8, 'Harry', 8),
(9, 'Ian', 9),
(10, 'John', 10);
So, lets say you have John go up against Adam for the #1 place and John wins:
因此，假设您让约翰与亚当争夺第一名，而约翰获胜：
UPDATE placements
SET place = place +1
WHERE user != "John";
UPDATE placements
SET place = 1
WHERE user = "John";
John is now in first place, and everybody else was bumped down a position. Now lets say that George goes up against Carl for Carl's position (currently placement #4). George wins: That means George is now place #4, and Carl place #5. What happens to Georges old position of #8?
约翰现在排在第一位，其他人都被淘汰了。现在让我们说乔治与卡尔争夺卡尔的位置（目前排名第 4 位）。乔治获胜：这意味着乔治现在排名第四，卡尔排名第五。乔治#8 的旧位置会怎样？
UPDATE placements
SET place = place +1
WHERE place > 3
AND place < 9
AND user != "George";
UPDATE placements
SET place = 4
WHERE user = "George";
So, it's really not that hard to do. You just have to know the current placement of some of your users, and adjust your MySQL queries as needed.
所以，做起来真的不难。您只需要知道一些用户的当前位置，并根据需要调整您的 MySQL 查询。
If you paste these queries into your terminal or phpMyAdmin (or whatever you use), and follow along, you'll see that it works.
如果您将这些查询粘贴到您的终端或 phpMyAdmin（或您使用的任何东西）中，然后继续操作，您将看到它有效。
