laravel的批量插入或更新
在项目中常常有些需求是需要将大量的数据导入库中,如果库中不存在该条数据插入,存在则更新,典型应用场景:更新报表数据,有这些报表的数据归因时间长达28天,也就是28内的数据都会更新,每天还会产生新的数据,这时就需要对新的数据插入,老数据进行更新。在laravel中有批量插入,批量更新的方法,也有对单条数据的插入或更新方法,却没有对批量数据的插入或更新的方法。
需求:
批量插入数据,如果表中存在则更新。
实现条件:
- 在表中设置唯一索引,通过唯一索引判断该条数据在库中是否存在
- 如有created_at字段则设置该字段为TIMESPACE ,默认为:CURRENT_TIMESTAMP (插入数据时不填该字段,默认值为当前时间)
原理:
sql语句:
insert into ads_daily_campaign_report
(apple_id ,date ,campaign_id ,campaign ,installs ,spent ,updated_at )
values
( '591888' , '2019-11-11' , '123456' , 'campaign_name_1' , '12' , '34' , '2019-08-11 05:54:03' ) ,
( '591888' , '2019-11-11' , '123457' , 'campaign_name_2' , '123' , '344' , '2019-08-11 05:54:03' )
on duplicate key update apple_id =
values (apple_id) ,date = values (date) ,campaign_id = values (campaign_id) ,campaign = values (campaign) ,installs = values (installs) ,spent = values (spent) ,updated_at = values (updated_at) ;
实现方法
/**
* 批量插入或更新表中数据
*
* @param $data 要插入的数据,元素中的key为表中的column,value为对应的值
* @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
];
}
建表语句
CREATE TABLE `myLaravel`.`ads_daily_campaign_report` (
`id` INT NOT NULL AUTO_INCREMENT,
`apple_id` INT NOT NULL DEFAULT 0,
`date` DATETIME NOT NULL,
`campaign_id` INT NOT NULL DEFAULT 0,
`campaign` VARCHAR(45) NOT NULL,
`installs` INT NOT NULL DEFAULT 0,
`spent` DOUBLE NOT NULL DEFAULT 0.00,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE INDEX `apple_id-date-campaign_id` (`apple_id` ASC, `date` ASC, `campaign_id` ASC));
注:记得加唯一索引
执行语句
$data = [
['apple_id' => '591888','date' => '2019-11-11','campaign_id' => '123456','campaign' => 'campaign_name_1','installs' => '12', 'spent' => '34'],
['apple_id' => '591888','date' => '2019-11-11','campaign_id' => '123457','campaign' => 'campaign_name_2','installs' => '123', 'spent' => '344'],
];
$table = 'ads_daily_campaign_report';
$columns = ['apple_id','date','campaign_id','campaign','installs','spent','updated_at'];
$ret = BaseModel::batchInsertOrUpdate($data,$table,$columns);
dd($ret);
执行结果:
运行结果:
array:2 [▼
"insertNum" => 2
"updateNum" => 0
]
mysql 查询结果
mysql> SELECT * FROM myLaravel.ads_daily_campaign_report;
+----+----------+---------------------+-------------+-----------------+----------+-------+---------------------+---------------------+
| id | apple_id | date | campaign_id | campaign | installs | spent | created_at | updated_at |
+----+----------+---------------------+-------------+-----------------+----------+-------+---------------------+---------------------+
| 1 | 591888 | 2019-11-11 00:00:00 | 123456 | campaign_name_1 | 12 | 34 | 2019-08-11 16:40:42 | 2019-08-11 08:40:42 |
| 2 | 591888 | 2019-11-11 00:00:00 | 123457 | campaign_name_2 | 123 | 344 | 2019-08-11 16:40:42 | 2019-08-11 08:40:42 |
+----+----------+---------------------+-------------+-----------------+----------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)
面向对象的方式实现
上大学时教软件工程的老师,在上课时非常严肃的问过我们一个问题,至今记忆犹新。“你们知道是什么人在推动这个世界进步么?” “科学家、天才、疯子……”我们思考良久也没有回答正确这个问题,最后还是老师给了我们一个令人信服的答案:懒人。
思考:
在调用时每次都要输入表名和表的字段,很麻烦,还容易出错,这是不是有优化空间呢?表名和表的字段也基本是一一对应的(特殊情况可能只更新个别字段),用面向对象的方式好像刚好可以解决这个问题,下面用面向对象的思想解决这个问题。
思想:
基类:实现具体方法
子类:设置属性,调用方法,
代码实现:
基类:
<?php
namespace App\Model;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
class BaseModel extends Model{
/**
* 与模型关联的表名
*
* @var string
*/
protected $table = '';
/**
* 用来向表中插入数据的字段
*
* @var array
*/
protected $tableColumns = [];
/**
* 批量插入或更新表中数据
*
* @param $data 要插入的数据,元素中的key为表中的column,value为对应的值
* @param string $table 要插入的表
* @param array $columns 要更新的的表的字段
* @return array
*/
public function batchInsertOrUpdate($data,$table = '',$columns = []){
if(empty($data)){//如果传入数据为空 则直接返回
return [
'insertNum' => 0,
'updateNum' => 0
];
}
empty($table) && $table = $this->getTable(); //如果未传入table则通过对象获得
empty($columns) && $columns = $this->getTableColumns(); //如果未传入table则通过对象获得
//拼装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
];
}
/**
* 返回表中字段
*
* @return array
*/
public function getTableColumns(){
if(empty($this->tableColumns)){
return [];
}
return $this->tableColumns;
}
}
子类:
<?php
namespace App\Model;
class AdsDailyCampaignReport extends BaseModel{
/**
* 与模型关联的表名
*
* @var string
*/
protected $table = 'ads_daily_campaign_report';
/**
* 用来向表中插入数据的字段
*
* @var array
*/
protected $tableColumns = [
'apple_id',
'date',
'campaign_id',
'campaign',
'installs',
'spent',
'updated_at'
];
}
<?php
namespace App\Model;
class AdsDailyCountryCampaignReport extends BaseModel{
/**
* 与模型关联的表名
*
* @var string
*/
protected $table = 'ads_daily_country_campaign_report';
/**
* 用来向表中插入数据的字段
*
* @var array
*/
protected $tableColumns = [
'apple_id',
'date',
'country',
'campaign_id',
'campaign',
'installs',
'spent',
'updated_at'
];
}
建表语句
CREATE TABLE `myLaravel`.`ads_daily_country_campaign_report` (
`id` INT NOT NULL AUTO_INCREMENT,
`apple_id` INT NOT NULL DEFAULT 0,
`date` DATETIME NOT NULL,
`country` VARCHAR(2) NOT NULL,
`campaign_id` INT NOT NULL DEFAULT 0,
`campaign` VARCHAR(45) NOT NULL,
`installs` INT NOT NULL DEFAULT 0,
`spent` DOUBLE NOT NULL DEFAULT 0.00,
`created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE INDEX `apple_id-date-country-campaign_id` (`apple_id` ASC, `date` ASC,`country` ASC, `campaign_id` ASC));
执行语句:
$data = [
['apple_id' => '591888','date' => '2019-11-12','campaign_id' => '123456','campaign' => 'campaign_name_1','installs' => '12', 'spent' => '34'],
['apple_id' => '591888','date' => '2019-11-11','campaign_id' => '123457','campaign' => 'campaign_name_2','installs' => '1231', 'spent' => '3441'],
];
$adsDailyCampaign = new AdsDailyCampaignReport();
$ret = $adsDailyCampaign->batchInsertOrUpdate($data);
dd($ret);
执行结果:
array:2 [▼
"insertNum" => 1
"updateNum" => 1
]
mysql> SELECT * FROM myLaravel.ads_daily_campaign_report;
+----+----------+---------------------+-------------+-----------------+----------+-------+---------------------+---------------------+
| id | apple_id | date | campaign_id | campaign | installs | spent | created_at | updated_at |
+----+----------+---------------------+-------------+-----------------+----------+-------+---------------------+---------------------+
| 1 | 591888 | 2019-11-11 00:00:00 | 123456 | campaign_name_1 | 12 | 34 | 2019-08-11 16:40:42 | 2019-08-11 08:40:42 |
| 2 | 591888 | 2019-11-11 00:00:00 | 123457 | campaign_name_2 | 1231 | 3441 | 2019-08-11 16:40:42 | 2019-08-11 09:26:39 |
| 3 | 591888 | 2019-11-12 00:00:00 | 123456 | campaign_name_1 | 12 | 34 | 2019-08-11 17:26:39 | 2019-08-11 09:26:39 |
+----+----------+---------------------+-------------+-----------------+----------+-------+---------------------+---------------------+
3 rows in set (0.00 sec)
执行语句:
$data = [
['apple_id' => '591888','date' => '2019-11-12','country' => 'US','campaign_id' => '123456','campaign' => 'campaign_name_1','installs' => '12', 'spent' => '34'],
['apple_id' => '591888','date' => '2019-11-11','country' => 'CN','campaign_id' => '123457','campaign' => 'campaign_name_2','installs' => '1231', 'spent' => '3441'],
];
$adsDailyCountryCampaign = new AdsDailyCountryCampaignReport();
$ret = $adsDailyCountryCampaign->batchInsertOrUpdate($data);
dd($ret);
执行结果:
array:2 [▼
"insertNum" => 2
"updateNum" => 0
]
mysql> SELECT * FROM myLaravel.ads_daily_country_campaign_report;
+----+----------+---------------------+---------+-------------+-----------------+----------+-------+---------------------+---------------------+
| id | apple_id | date | country | campaign_id | campaign | installs | spent | created_at | updated_at |
+----+----------+---------------------+---------+-------------+-----------------+----------+-------+---------------------+---------------------+
| 1 | 591888 | 2019-11-12 00:00:00 | US | 123456 | campaign_name_1 | 12 | 34 | 2019-08-11 17:33:32 | 2019-08-11 09:33:32 |
| 2 | 591888 | 2019-11-11 00:00:00 | CN | 123457 | campaign_name_2 | 1231 | 3441 | 2019-08-11 17:33:32 | 2019-08-11 09:33:32 |
+----+----------+---------------------+---------+-------------+-----------------+----------+-------+---------------------+---------------------+
2 rows in set (0.01 sec)