MySQL 将数据库字段增加 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2762851/
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
Increment a database field by 1
提问by Matt
With MySQL, if I have a field, of say logins, how would I go about updating that field by 1 within a sql command?
使用 MySQL，如果我有一个字段，比如登录，我将如何在 sql 命令中将该字段更新为 1？
I'm trying to create an INSERT query, that creates firstName, lastName and logins. However if the combination of firstName and lastName already exists, increment the logins by 1.
我正在尝试创建一个 INSERT 查询，它创建 firstName、lastName 和登录名。但是，如果 firstName 和 lastName 的组合已经存在，则将登录名增加 1。
so the table might look like this..
所以桌子可能看起来像这样..
firstName----|----lastName----|----logins
John Jones 1
Steve Smith 3
I'm after a command that when run, would either insert a new person (i.e. Tom Rogers) or increment logins if John Jones was the name used..
我正在执行一个命令，该命令在运行时会插入一个新人（即 Tom Rogers），或者如果使用的名字是 John Jones，则增加登录次数。
回答by Sampson
Updating an entry:
更新条目：
A simple increment should do the trick.
一个简单的增量应该可以解决问题。
UPDATE mytable
SET logins = logins + 1
WHERE id = 12
Insert new row, or Update if already present:
插入新行，或更新（如果已经存在）：
If you would like to update a previously existing row, or insert it if it doesn't already exist, you can use the REPLACEsyntaxor the INSERT...ON DUPLICATE KEY UPDATEoption (As Rob Van Damdemonstrated in his answer).
如果您想更新以前存在的行，或者在它不存在时插入它，您可以使用REPLACE语法或INSERT...ON DUPLICATE KEY UPDATE选项（正如Rob Van Dam在他的回答中所展示的那样）。
Inserting a new entry:
插入新条目：
Or perhaps you're looking for something like INSERT...MAX(logins)+1? Essentially you'd run a query much like the following - perhaps a bit more complex depending on your specific needs:
或者，也许您正在寻找类似的东西INSERT...MAX(logins)+1？本质上，您将运行类似于以下内容的查询 - 根据您的特定需求，可能会更复杂一些：
INSERT into mytable (logins)
SELECT max(logins) + 1
FROM mytable
回答by Rob Van Dam
If you can safely make (firstName, lastName) the PRIMARY KEY or at least put a UNIQUE key on them, then you could do this:
如果您可以安全地将 (firstName, lastName) 设为 PRIMARY KEY 或至少在其上放置一个 UNIQUE 键，那么您可以这样做：
INSERT INTO logins (firstName, lastName, logins) VALUES ('Steve', 'Smith', 1)
ON DUPLICATE KEY UPDATE logins = logins + 1;
If you can't do that, then you'd have to fetch whatever that primary key is first, so I don't think you could achieve what you want in one query.
如果你不能这样做，那么你必须首先获取主键是什么，所以我认为你无法在一个查询中实现你想要的。
回答by Serg Gulko
I not expert in MySQL but you probably should look on triggers e.g. BEFORE INSERT. In the trigger you can run select query on your original table and if it found something just update the row 'logins' instead of inserting new values. But all this depends on version of MySQL you running.
我不是 MySQL 专家，但您可能应该查看触发器，例如 BEFORE INSERT。在触发器中，您可以在原始表上运行选择查询，如果发现某些内容，只需更新“登录”行而不是插入新值。但这一切都取决于您运行的 MySQL 版本。
回答by oucil
This is more a footnote to a number of the answers above which suggest the use of ON DUPLICATE KEY UPDATE, BEWAREthat this is NOTalways replication safe, so if you ever plan on growing beyond a single server, you'll want to avoid this and use two queries, one to verify the existence, and then a second to eitherUPDATEwhen a row exists, or INSERTwhen it does not.
这更是一个注脚一些高于该建议使用的答案ON DUPLICATE KEY UPDATE，当心这是不是总是复制安全的，所以如果你在成长超出了单个服务器曾经计划，你要避免这一点，并使用两个查询，一个验证的存在，再进行第二次到要么UPDATE当一个行存在，或者INSERT当它没有。
回答by Ken Bloom
You didn't say what you're trying to do, but you hinted at it well enough in the comments to the other answer. I think you're probably looking for an auto increment column
你没有说你想要做什么，但你在另一个答案的评论中暗示了它。我想你可能正在寻找一个自动增量列
create table logins (userid int auto_increment primary key,
username varchar(30), password varchar(30));
then no special code is needed on insert. Just
那么插入时不需要特殊代码。只是
insert into logins (username, password) values ('user','pass');
The MySQL API has functions to tell you what userid was created when you execute this statement in client code.
MySQL API 具有告诉您在客户端代码中执行此语句时创建的用户 ID 的功能。
