慎用,虽然可以进行批量更新,但总感觉写完之后怪怪的;
/**
* @param ActiveRecord $model 模型类
* @param array $attributesList 批量更新数据二维数组如[['Key1' => 'AValue1','Key2' => 'AValue2'],['Key1' => 'BValue1', 'Key2' => 'BValue2']];(数组中包括键值$id)
* @param array $updateKey 需要更新的键值,一维数组 如['Key1','Key2'];(包括$id)
* @param string $id 唯一索引的值一般是以id来进行主键约束
* @param int $size 一次插入最多条数
* @return array|null
* @throws Exception
* @author: ChuYubo
*/
public function batchUpdateAll(ActiveRecord $model, array $attributesList,array $updateKey,string $id,int $size = 1000): ?array
{
if ($model === null || empty($attributesList)) {
return null;
}
#获取该模型下所有字段
$columnList = $model->attributes();
#根据主键进行批量更新,判断传入是否是主键
if (!in_array($id,$model::primaryKey())) {
throw new Exception($id.'不为主键不能进行批量更新');
}
#进行判断 键值是否是attribute中的值
foreach ($updateKey as $item) {
if (!in_array($item,$columnList,false)) {
throw new Exception($item.'键不能进行批量更新');
}
}
# 根据 id 值 进行判断表中是否存在
$primaray = [];
foreach ($attributesList as $item) {
if ($item[$id]) {
$primaray[] = $item[$id];
}else {
throw new Exception('存在'.$id,'为空');
}
}
if (count($primaray) !== count($model::find()->where(['id' => $primaray])->all())) {
throw new Exception('存在不在记录中的列');
}
#去除二维数组外围键值,要根据SIZE 进行分组
$attributesList = array_values($attributesList);
#根据size进行分组进行批量插入
$attributesList = array_chunk($attributesList, $size);
$executeRowList = [];
#根据SIZE 进行循环
foreach ($attributesList as &$dataChunkList) {
#重新对键值进行排序
$dataChunkList = array_values($dataChunkList);
$sql = 'INSERT';
$sql .= ' INTO ' . $model::tableName() . '(`' . implode('`,`', $updateKey) . '`) values';
foreach ($dataChunkList as $key => $data) {
$sql .= '(';
foreach ($updateKey as $column) {
if (!isset($data[$column])) {
throw new Exception("批量更新失败!传入的数据中不存在字段{$column}!", [], 500);
}
$sql .= ":{$column}_{$key},";
}
$sql = substr($sql, 0, -1) . '),';
}
$sql = substr($sql, 0, -1);
$sql .= ' ON DUPLICATE KEY UPDATE ';
foreach ($updateKey as $column) {
$sql .= "`{$column}`=values(`{$column}`),";
}
$sql = substr($sql, 0, -1);
$command = Yii::$app->db->createCommand($sql);
//参数绑定
foreach ($dataChunkList as $key => $data) {
foreach ($updateKey as $column) {
$command->bindParam(":{$column}_{$key}", $data[$column]);
}
}
$executeRowList[] = $command->execute();
}
return $executeRowList;
}