标签归档:ignore insert

MySQL 插入数据之replace和ignore

MySQL中处理常规的insert into之外,也提供了replace insert和insert ignore into语句。

当需要插入不重复的值(有唯一索引),常规的插入就必须先判断是否存在,然后再进行插入,这个在需要批量插入数据时,需要循环查询判断,如果使用replace insert和insert ignore into语句就不需要这样做,insert ignore into很好理解,对比唯一索引,如果存在直接跳过,而replace insert是指存在时,对存在的数据行进行更新,准确来说应该是对存在的数据行进行删除,然后插入新的。

所以使用replace insert要特别小心,它是先删除,再插入,比如插入一个已经存在的行,它会返回受影响的行是2,如果新的行没有包含原来的全部数据,那么这部分数据将丢失,如果设置了id为自动增长的,就可以看到,id将不会连续(先删除后插入的缘故)。

以下是一个trait,用来扩展Laravel ORM模型以支持insertReplace和insertIgnore这样的语法:

<?php

namespace Ebt\ModelExtend;

trait InsertReplaceable 
{
    public static function insertReplace(array $attributes = []) 
    {
        return static::executeQuery ( 'replace', $attributes );
    }
    
    public static function insertIgnore(array $attributes = []) 
    {
        return static::executeQuery ( 'insert ignore', $attributes );
    }
    
    protected static function executeQuery($command, array $attributes) 
    {
        $prefix = \DB::connection()->getTablePrefix();
        if (! count ( $attributes )) {
            return true;
        }
        $model = new static ();
        if ($model->fireModelEvent ( 'saving' ) === false) {
            return false;
        }
        $attributes = collect ( $attributes );
        $first = $attributes->first ();
        if (! is_array ( $first )) {
            $attributes = collect ( [ 
                $attributes->toArray () 
            ] );
        }
        $keys = collect ( $attributes->first () )->keys ()->transform ( function ($key) {
            return "`" . $key . "`";
        } );
        $bindings = [ ];
        $query = $command . " into " .  $prefix . $model->getTable () . " (" . $keys->implode ( "," ) . ") values ";
        $inserts = [ ];
        foreach ( $attributes as $data ) {
            $qs = [ ];
            foreach ( $data as $value ) {
                $qs [] = '?';
                $bindings [] = $value;
            }
            $inserts [] = '(' . implode ( ",", $qs ) . ')';
        }
        $query .= implode ( ",", $inserts );
        \DB::connection ( $model->getConnectionName () )->insert ( $query, $bindings );
        $model->fireModelEvent ( 'saved', false );
    }
}

用法:

$data = [
    [
        "name" => 'ifeeline',
        "note" => "xx"
    ],
    [
        "name" => 'ifeeline2',
        "note" => "yy"
    ],
];
//\App\TestTest::insertReplace($data);
\App\TestTest::insertIgnore($data);