如果记录已存在,如何插入或更新?
我有一个包含列 record_id
(auto inc)、sender
、sent_time
和 status
的表。
如果没有特定发件人的任何记录,例如“sender1”,我必须插入一条新记录,否则我必须更新属于“user1”的现有记录。
因此,如果没有存储任何记录,我将执行
# record_id is AUTO_INCREMENT field
INSERT INTO messages (sender, sent_time, status)
VALUES (@sender, time, @status)
否则我将执行 UPDATE 语句。
无论如何..有谁知道如何组合这两个语句以便在没有任何字段发送者值为“user1”的记录的情况下插入新记录,否则更新现有记录?
I have a table with columns record_id
(auto inc), sender
, sent_time
and status
.
In case there isn't any record of a particular sender, for example "sender1", I have to INSERT a new record otherwise I have to UPDATE the existing record which belongs to "user1".
So if there isn't any record already stored, I would execute
# record_id is AUTO_INCREMENT field
INSERT INTO messages (sender, sent_time, status)
VALUES (@sender, time, @status)
Otherwise I would execute UPDATE statement.
Anyway.. does anyone know how to combine these two statements in order to insert a new record if there isn't any record where the field sender value is "user1" otherwise update the existing record?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(6)
MySQL 支持 insert-on-duplicate 语法,铁:
MySQL supports the insert-on-duplicate syntax, f.e.:
如果您对表有严格的约束,那么您还可以使用
替换为
为此。这是 MySQL 的引用:语法与
INSERT INTO
基本相同,只是将INSERT
替换为REPLACE
。请
注意,这是 MySQL 特定的命令,不会出现在其他数据库中,因此请记住可移植性。
If you have solid constraints on the table, then you can also use the
REPLACE INTO
for that. Here's a cite from MySQL:The syntax is basically the same as
INSERT INTO
, just replaceINSERT
byREPLACE
.would then be
Note that this is a MySQL-specific command which doesn't occur in other DB's, so keep portability in mind.
正如其他人提到的,您应该使用“插入...重复键更新”,有时称为“upsert”。但是,在您的特定情况下,您不想在更新中使用静态值,而是希望使用传递到插入语句的值子句的值。
具体来说,我认为如果该行已经存在,您想要更新两列:
为了做到这一点,您将使用像这样的“upsert”语句(使用您的示例):
As others have mentioned, you should use "insert...on duplicate key update", sometimes referred to as an "upsert". However, in your specific case you don't want to use a static value in the update, but rather the values you pass in to the values clause of the insert statement.
Specifically, I think you want to update two columns if the row already exists:
In order to do this, you would use an "upsert" statement like this (using your example):
查看“重复密钥更新时插入”。
Check out "Insert on Duplicate Key Update".
一种选择是使用重复更新语法
http:// dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
其他选项是执行 select 以确定记录是否存在,然后相应地执行插入/更新。请注意,如果您使用事务选择,则不会显式终止事务,因此使用它是安全的。
One options is using on duplicate update syntax
http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html
Other options is doing select to figure out if record exists and then doind inser/update accordingly. Mind that if you're withing transaction select will not explicitly terminate the transaction so it's safe using it.