插入到mysql数据库中，如果记录已经存在，则更新 [英] insert into mysql database, if records already exists, then update

问题描述

可能重复:
如何操作我是否更新(如果存在)，如果不存在，则插入MySQL(又称为upsert或merge)?

Possible Duplicate:
How do I update if exists, insert if not (aka upsert or merge) in MySQL?

如何插入到mysql数据库中，如果记录已经存在，则进行更新...我知道此页面上有解决方案: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

how to insert into mysql database, if records already exists, then update...I know there is a solution on this page: http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

sib_table

+=======================================================+
| sib_id | std_id  |  sib_name  |  sib_sex  |  sib_age  |
+=======================================================+
| 1      | 77      |  Sajjad    |   m       | 5/17/1990 |
| 1      | 77      |  Farah     |   f       | 9/14/1980 |
| 1      | 77      |  Bilal     |   m       | 1/10/1995 |
+=======================================================+

如果我想向该表添加花药兄弟，将会是什么sql.

What sql would be if I want to add anther sibling to this table.

INSERT INTO sib_table
  (std_id,sib_name,sib_sex,sib_age) 
VALUES ('77','Sajjad','m','1/5/2010')  
ON DUPLICATE KEY 
  UPDATE id = LAST_INSERT_ID(id), c = 3;

INSERT INTO sib_table
  (std_id,sib_name,sib_sex,sib_age) 
VALUES 
  ('77','Aamna','f','1/27/2005')  
ON DUPLICATE KEY 
  UPDATE id = LAST_INSERT_ID(id), c = 3;

推荐答案

您已经很接近了，但是您需要像对待UPDATE语句一样对待ON DUPLICATE KEY子句.这意味着您需要设置一个唯一的密钥，以便尝试执行此操作:

You're close, but you need to treat the ON DUPLICATE KEY clause just like an UPDATE statement. That means you need to setup a unique key so that trying to do:

INSERT INTO sib_table (std_id,sib_name,sib_sex,sib_age) 
VALUES ('77','Sajjad','m','1/5/2010')  

...将仅一次起作用.然后添加ON DUPLICATE KEY UPDATE子句以更改其余字段(即不属于键的字段)以匹配.

... will only work once. Then you add the ON DUPLICATE KEY UPDATE clause to change the rest of the fields (i.e. the ones that aren't part of the key) to match.

因此，举例来说，假设我正确地读取了表结构，那么如果将唯一的复合键放在列std_id和sib_name上，这将确保您不能添加两个同名兄弟姐妹.而那意味着当您添加另一个这样的东西时:

So, for example, assuming I read the table structure correctly, if you put a unique composite key on the columns std_id and sib_name, this will ensure that you can't add two siblings of the same name. And that means that when you go to add another like this:

INSERT INTO sib_table (std_id,sib_name,sib_sex,sib_age) 
VALUES ('77','Aamna','f','1/27/2005')  
ON DUPLICATE KEY 
  UPDATE sib_sex = 'f', sib_age = '1/27/2005'

...它将执行以下两项操作之一:

... it will do one of two things:

1. 如果在77号家庭中不存在Aamna，则添加新行.
2. 或更新Aamna的性别和生日(如果之前已经添加过的话).

此结构比MySQL的REPLACE功能更强大，因为它使您可以对冲突的行执行不同的操作，而不仅仅是用尝试插入的内容覆盖它.当然，大多数时候REPLACE的功能是实际需要的.但是最好知道更通用的语句.

This structure is more powerful than MySQL's REPLACE because it lets you do something different to the conflicting row than merely overwrite it with what you tried to insert. Of course, most of the time the functionality of REPLACE is what is actually wanted. But it is better to know the more generic statement.

这篇关于插入到mysql数据库中，如果记录已经存在，则更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！