使用" INSERT ... ON DUPLICATE KEY UPDATE".插入多个记录 [英] Use" INSERT ... ON DUPLICATE KEY UPDATE" to insert multiple records

问题描述

我的表格结构

表:标记

我的目标: 我想插入或更新条件为

My objective: i want to insert or update multiple records with the condition

我目前正在通过此查询进行检查

i am currently check by this query

第一步

SELECT * FROM `marks` WHERE `student` =115 AND `param` =1

第二步

if 
    records found by matching above criteria i just update record by my new values 
else
    insert new record into my table 

它会很好的工作.但我想减少代码并将其优化为单个查询.有可能吗? 我在MySQL文档 INSERT ... ON DUPLICATE KEY UPDATE 上发现了此问题.如果这是一个解决方案.如何通过查询实现?

It gonna working fine . but i want to reduce code and optimize this into single query . its possible or not ? I found this on MySQL docs INSERT ... ON DUPLICATE KEY UPDATE . if this is a solution . how can i achieve by query ?

注意:我正在使用 Yii框架. Yii的建议也欢迎

Note: i am using the Yii framework . suggestion from Yii also welcome

已 该查询不会更新行.但无法从插入正常工作

Edited: This query does't not update the rows . but escape from insert working correctly

INSERT INTO marks(`student`,`param,mark`,`created`,`lastmodified`,`status`) 
VALUES 
(11,30,10,'00-00-00 00:00:00','00-00-00 00:00:00','U')
ON DUPLICATE KEY UPDATE `mark`=VALUES(`mark`)

推荐答案

检查本文 Yii INSERT ...在重复更新中.他们建议您不要使用此功能.但是我想使用它，所以我从CDbCommand扩展了我自己的组件，并为ON DUPLICATE KEY UPDATE添加了方法:

Check this article Yii INSERT ... ON DUPLICATE UPDATE. They suggest you don't use this feature. But i want it to use, so I extended from CDbCommand my own component and add method for ON DUPLICATE KEY UPDATE:

public function insertDuplicate($table, $columns, $duplicates)
{
    $params=array();
    $names=array();
    $placeholders=array();
    foreach($columns as $name=>$value)
    {
        $names[]=$this->getConnection()->quoteColumnName($name);
        if($value instanceof CDbExpression)
        {
            $placeholders[] = $value->expression;
            foreach($value->params as $n => $v)
                $params[$n] = $v;
        }
        else
        {
            $placeholders[] = ':' . $name;
            $params[':' . $name] = $value;
        }
    }

    $d = array();
    foreach($duplicates as $duplicate)
    {
        $d[] = '`' . $duplicate . '` = VALUES(`'.$duplicate.'`)';
    }
    $sql='INSERT INTO ' . $this->getConnection()->quoteTableName($table)
        . ' (' . implode(', ',$names) . ') VALUES ('
        . implode(', ', $placeholders) . ') ON DUPLICATE KEY UPDATE ' . implode(', ', $d);
    return $this->setText($sql)->execute($params);
}

用法示例:

Yii::app()->db->createCommand()->insertDuplicate('user', [
   'id' => $this->id,
   'token' => $token,
   'updated' => date("Y-m-d H:i:s"),
], ['token', 'updated']);

此命令将使用此参数创建用户，或者如果记录存在，则更新token和updated字段.

This command will create user with this parameters or update token and updated fields if record exists.

这篇关于使用" INSERT ... ON DUPLICATE KEY UPDATE".插入多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！