mysql UPDATE后通过mysql_affected_rows判断是否成功，没有则INSERT ，结果出问题了

程序员文章站 2022-04-02 09:46:42

...

mysql UPDATE后通过mysql_affected_rows判断是否成功，没有则INSERT ，结果出问题了，表行数成倍的增长，代码如下，求解

//数据写入数据库
   function save_db($currencyname_en,$currencyname_cn,$buyingrate,$sellingrate,$middlerate)
   {
        //更新
        $query_update = ' UPDATE '. TABLENAME .' SET '
                       .' buyingrate=' .$buyingrate .','
                       .' sellingrate=' .$sellingrate .','
                       .' middlerate=' .$middlerate .','
                       .' lastupdatetime="' .date('Y-m-d H:i:s',time()) .'"'
                       .' WHERE currencyname_en="' .$currencyname_en .'"';
        mysql_query($query_update);

        if(mysql_affected_rows()!=1)//更新不成功，尝试插入        
        {
            $query_insert = 'INSERT INTO ' . TABLENAME . '(currencyname_en, currencyname_cn, buyingrate, sellingrate, middlerate, lastupdatetime) VALUES (                   
                                            "'.$currencyname_en.'",
                                            "'.$currencyname_cn.'",
                                            "'.$buyingrate.'",
                                            "'.$sellingrate.'",
                                            "'.$middlerate.'",
                                            "'.date('Y-m-d H:i:s',time()).'"
            )';
            mysql_query($query_insert);
        }

        if(mysql_affected_rows()!=1)
        {
            $err .= "
插入或者更新".$currencyname_en."-".$key."currencyname_cn";
        }

   }

回复内容：

mysql UPDATE后通过mysql_affected_rows判断是否成功，没有则INSERT ，结果出问题了，表行数成倍的增长，代码如下，求解

//数据写入数据库
   function save_db($currencyname_en,$currencyname_cn,$buyingrate,$sellingrate,$middlerate)
   {
        //更新
        $query_update = ' UPDATE '. TABLENAME .' SET '
                       .' buyingrate=' .$buyingrate .','
                       .' sellingrate=' .$sellingrate .','
                       .' middlerate=' .$middlerate .','
                       .' lastupdatetime="' .date('Y-m-d H:i:s',time()) .'"'
                       .' WHERE currencyname_en="' .$currencyname_en .'"';
        mysql_query($query_update);

        if(mysql_affected_rows()!=1)//更新不成功，尝试插入        
        {
            $query_insert = 'INSERT INTO ' . TABLENAME . '(currencyname_en, currencyname_cn, buyingrate, sellingrate, middlerate, lastupdatetime) VALUES (                   
                                            "'.$currencyname_en.'",
                                            "'.$currencyname_cn.'",
                                            "'.$buyingrate.'",
                                            "'.$sellingrate.'",
                                            "'.$middlerate.'",
                                            "'.date('Y-m-d H:i:s',time()).'"
            )';
            mysql_query($query_insert);
        }

        if(mysql_affected_rows()!=1)
        {
            $err .= "
插入或者更新".$currencyname_en."-".$key."currencyname_cn";
        }

   }

INSERT INTO table
(...)
VALUES
(...)
ON DUPLICATE KEY UPDATE field1 = ?, field2 = ?, ...

这样会不会简单一点？