批量插入数据时如何查重

问题分析：

1. 需要每次入库数据的某个 column 字段值在整表素有条目中是唯一的，我们先暂定为 name 吧。
2. 单条数入库可以通过 where name = 值 来查看数据表中是否有 name 相同的数据。有的话不入库，反之入库。
3. 多条数据批量入库使用 where name in ('值1','值2' ... ) 使用这种方式作为 是否存在重复 的条件会影响查询速度。

关键词：

1. 去重。
2. 多条批量插入。
3. 查询速度。

解决思路：

1. 增量数据去重：需要去除批量数据中存在的name重复。
2. 存量数据去重：需要去除批量数据在数据表中数据行的name重复。

具体方法：

1. 增量数据：循环批量数据先自查重复，重复的剔除。
2. 存量数据：嫌查询数据库慢，可以采取一次性获取存量数据，但只获取单独一列的数据，比如 name 这一列的所有数据。
   数据量小的话存成一维数组，数据量大的话存入 Redis 的set集合中，然后根据数组或者集合查询新的 name 在存量数中是否已经存在。

在BaseModel添加方法

/**
 * 批量插入或更新表中数据
 * @param array $data 要插入的数据
 * @param string $table 表名
 * @param array $columns 要插入/更新的字段
 * @return array
 */
public static function batchInsertOrUpdate($data, $table = '', $columns = [])
{

    if (empty($data)) {
        //如果传入数据为空 则直接返回
        return [
            'insertNum' => 0,
            'updateNum' => 0
        ];
    }

    //组装sql
    $sql = "insert into " . $table . " (";
    foreach ($columns as $k => $column) {
        $sql .= $column . " ,";
    }
    $sql = trim($sql, ',');
    $sql .= " ) values ";

    foreach ($data as $k => $v) {
        $sql .= "(";
        foreach ($columns as $kk => $column) {
            if ('updated_at' == $column) {
                //如果库中存在，create_at字段会被更新
                $sql .= " '" . date('Y-m-d H:i:s') . "' ,";
            } else {
                $val = '';
                //插入数据中缺少$colums中的字段时的默认值
                if (isset($v[$column])) {
                    $val = $v[$column];
                    //在预定义的字符前添加反斜杠的字符串。
                    $val = addslashes($val);
                }
                $sql .= " '" . $val . "' ,";
            }
        }
        $sql = trim($sql, ',');
        $sql .= " ) ,";
    }
    $sql = trim($sql, ',');
    $sql .= "on duplicate key update ";
    foreach ($columns as $k => $column) {
        $sql .= $column . " = values (" . $column . ") ,";
    }
    $sql = trim($sql, ',');
    $sql .= ';';

    $columnsNum = count($data);
    $retNum = DB::update(DB::raw($sql));
    $updateNum = $retNum - $columnsNum;
    $insertNum = $columnsNum - $updateNum;
    return [
        'insertNum' => $insertNum,
        'updateNum' => $updateNum
    ];
}

使用

$table = 'cost_modifies';
$columns = ['modify_date', 'modify_price', 'account', 'created_at'];
$ret = BaseModel::batchInsertOrUpdate($data, $table, $columns);

假设modify_date和account两个字段相同就认为是相同记录，需要更新，需要这两个字段设置联合唯一索引

可以用 sql 的 on conflict 吗？

两条语句

insert ignore into table_name (`column1`,`column2`) values (1,2),(3,4);//批量插入，如果存在，就忽略

insert into table_name (`column1`,`column2`) values(1,2) on duplicate key update column1=xxx; //批量插入，如果存在就更新column1为xxx

• 上述语句都必须要求填充的字段有主键或者唯一索引,否则不生效
• 对应的laravel扩展包：laravel-upsert
• laravel8应该直接内置了批量更新的方法，就不需要引用这个包了，具体看自己的版本
• 根据我的实际工作经验，上述方法是效率最高的，通常可解决大部分批量插入/更新的功能