MySQL 使用递增变量更新字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16644551/
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
MySQL update a field with an incrementing variable
提问by Upeksha
I have a table named 'textile_events' in one of my databases.
我的一个数据库中有一个名为“textile_events”的表。
mysql> describe textile_events;
+-------------+--------------+-----+---------+----------------+
| Field | Type | Key | Default | Extra |
+-------------+--------------+-----+---------+----------------+
| id | int(11) | PRI | NULL | auto_increment |
| token | varchar(20) | | NULL | |
| reg_time | datetime | | NULL | |
| eid | varchar(20) | | NULL | |
| fname | varchar(20) | | NULL | |
| lname | varchar(20) | | NULL | |
| paid | varchar(10) | | NULL | |
| seq_no | int(11) | | NULL | |
+-------------+--------------+-----+---------+----------------+
8 rows in set (0.00 sec)
mysql> select count(*) from textile_events;
+----------+
| count(*) |
+----------+
| 9325 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from textile_events where eid = 'headsup' ;
+----------+
| count(*) |
+----------+
| 2553 |
+----------+
1 row in set (0.01 sec)
'seq_no' field was introduced to the above table yesterday.
'seq_no' 字段昨天被引入到上表中。
Now, I need to assign an incrementing number to 'seq_no' field for all 'headsup' events where paid field is equal to 'paid'.
现在，我需要为所有“headsup”事件的“seq_no”字段分配一个递增数字，其中付费字段等于“已付费”。
In other way, I am looking for something like this,
换句话说，我正在寻找这样的东西，
$i = 250
while( true ):
$i++
UPDATE textile_events SET 'seq_no' = $i
WHERE eid = 'headsup' AND paid = 'paid'
endwhile;
How do I assign an incrementing number to a newly introduced field only to recods that satify a given condition?
如何将递增数字分配给新引入的字段，仅用于满足给定条件的记录？
What are the available options and what is the most efficient way to accomplish this?
有哪些可用的选项以及实现此目的的最有效方法是什么？
回答by peterm
回答by Developer
Simple query would be, just set a variable to some number you want. Then update the column you need by incrementing 1 from that number.
简单的查询是，只需将变量设置为您想要的某个数字。然后通过从该数字增加 1 来更新您需要的列。
Ex:: Query to update all the rows where a column is null. it'll update each row id by incrementing 1
例如：：查询以更新列为空的所有行。它将通过增加 1 来更新每一行 id
SET @a = 50000835 ;
UPDATE `civicrm_contact` SET external_identifier = @a:=@a+1
WHERE external_identifier IS NULL;
回答by Pank
Maybe this will help you...
也许这会帮助你...
DELIMITER $$
DROP PROCEDURE IF EXISTS manual_increment_count$$
CREATE PROCEDURE manual_increment_count()
BEGIN
DECLARE count INT DEFAULT 0;
SELECT COUNT(*) INTO count FROM textile_events
WHILE count > 0
SET count = count + 1;
update textile_events
set seq_no = count where eid = 'headsup' AND paid = 'paid';
END WHILE;
END$$
DELIMITER ;
