MySQL的:如果值存在更新否则插入 [英] MySql: if value exists UPDATE else INSERT

问题描述

我有一些看起来像这样的代码.我必须保留表中的一个自动递增字段(在其他表中使用).我想简化和优化这段代码.

I have some code that looks like this. There is also an autoincrement field in the table that I must retain (it is used in other tables). I would like to simplify and optimize this code.

$query ="SELECT * FROM models WHERE col1 = 'foo'";
$testResult = mysql_query($query) or die('Error, query failed');    

if(mysql_fetch_array($testResult) == NULL){
    //insert...
    $query ="INSERT INTO models (col1, col2, col3)
    VALUES ('foo', 'bar', 'alph')";
    $result = mysql_query($query) or die('Error, query failed');
}else{
    //update...
    $query = "UPDATE models
        SET col1='foo', col2='bar', col3='alph'
        WHERE col1='foo' AND col2='bar'";
        $result = mysql_query($query) or die('Error, query failed');        
}

主键ID是自动递增的字段.我永远都不想改变这一点.但是，当另一个字段重复时，这就是我要更新该记录的时间.

The primary key id is the field that is auto incremented. I never want to alter this. However , when another field(s) is/are duplicated, this is when I want to update that record.

推荐答案

REPLACE INTO怎么样:

How about REPLACE INTO:

REPLACE INTO models
( col1, col2, col3 )
VALUES
( 'foo', 'bar', 'alpha' )

假设col1是您的主键，如果已经存在值'foo'的行，它将更新其他两列.否则，它将插入新行.

Assuming col1 is your primary key, if a row with the value 'foo' already exists, it will update the other two columns. Otherwise it will insert a new row.

这篇关于MySQL的:如果值存在更新否则插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！