I've got a mysql table column that acts as a counter (int). Each time I update that column I want the field value to get plussed with 1.
我有一个mysql表列作为计数器(int)。每次我更新该列时,我希望字段值加1。
So if it was 45 I want it to be 46.
所以,如果它是45,我希望它是46。
How could I do that with SQL?
我怎么能用SQL做到这一点?
3 个解决方案
#1
5
MySQL v5.0.2 or higher supports triggers, which are pieces of code that are executed, whenever an insert/update/delete operation is made on one of the rows.
MySQL v5.0.2或更高版本支持触发器,只要在其中一行上执行插入/更新/删除操作,就会执行这些触发器。
for more information about triggers in MySQL check this link
有关MySQL中触发器的更多信息,请查看此链接
#2
11
You can use a query such as this one :
您可以使用以下查询:
update your_table
set your_column = your_column + 1
where identifier = X
Of course, up to you to replace the names of the table and the columns ;-)
And to make sure the condition in the where
clause is OK ;-)
当然,由您来替换表和列的名称;-)并确保where子句中的条件正常;-)
#3
2
Since you want it to happen each time, you could use a trigger:
由于您希望每次都能发生这种情况,您可以使用触发器:
delimiter //
create trigger increment_field before update on your_table
for each row
begin
if new.your_column <> old.your_column then
set new.your_column = new.your_column + 1
end if;
end;//
delimiter ;
I am not entirely sure I understand your question. The above solution will make it so whenever a row is updated such that your_column is given a different value, it will instead make your_column = new_value+1. The use of new and old keywords and the conditional used on each row should be changed to suit your needs.
我不完全确定我理解你的问题。上面的解决方案将使得每当更新一行以使your_column被赋予不同的值时,它将使your_column = new_value + 1。应更改新旧关键字的使用以及每行使用的条件,以满足您的需求。
#1
5
MySQL v5.0.2 or higher supports triggers, which are pieces of code that are executed, whenever an insert/update/delete operation is made on one of the rows.
MySQL v5.0.2或更高版本支持触发器,只要在其中一行上执行插入/更新/删除操作,就会执行这些触发器。
for more information about triggers in MySQL check this link
有关MySQL中触发器的更多信息,请查看此链接
#2
11
You can use a query such as this one :
您可以使用以下查询:
update your_table
set your_column = your_column + 1
where identifier = X
Of course, up to you to replace the names of the table and the columns ;-)
And to make sure the condition in the where
clause is OK ;-)
当然,由您来替换表和列的名称;-)并确保where子句中的条件正常;-)
#3
2
Since you want it to happen each time, you could use a trigger:
由于您希望每次都能发生这种情况,您可以使用触发器:
delimiter //
create trigger increment_field before update on your_table
for each row
begin
if new.your_column <> old.your_column then
set new.your_column = new.your_column + 1
end if;
end;//
delimiter ;
I am not entirely sure I understand your question. The above solution will make it so whenever a row is updated such that your_column is given a different value, it will instead make your_column = new_value+1. The use of new and old keywords and the conditional used on each row should be changed to suit your needs.
我不完全确定我理解你的问题。上面的解决方案将使得每当更新一行以使your_column被赋予不同的值时,它将使your_column = new_value + 1。应更改新旧关键字的使用以及每行使用的条件,以满足您的需求。