八戒常用之mysql批量操作（创建，更新）

最新推荐文章于 2023-09-18 17:30:09 发布

不是二师兄的八戒

最新推荐文章于 2023-09-18 17:30:09 发布

阅读量591

点赞数

分类专栏： php 文章标签： mysql 数据库 php

本文链接：https://blog.csdn.net/wolf23151269/article/details/121352870

版权

php 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

为避免 因批量操作时采用for循环连接数据库创建或更新从而影响性能较低效率 的问题 批量处理数据就是一个必要选择。 
这也是后端性能优化常用之一，避免一个请求中频繁进行数据库连接
（还有要注意的是习惯性的对与性能方面三思而行）

虽然批量创建批量更新一定程度上减少了性能上的浪费，但是注意下单次的更新条数
public function batchCreate($rows)  批量创建 

public function batchUpdate($identifies, $updateColumnsList, $identifyColumn = 'id')批量更新//限制是跟新的数据结构必须一制

下面是具体实现。结尾有使用实例

public function batchCreate($rows)
{
    if (empty($rows)) {
        return array();
    }

    $columns = array_keys(reset($rows));
    $this->db()->checkFieldNames($columns);
    $columnStr = implode(',', $columns);

    $count = count($rows);
    $pageSize = 1000;
    $pageCount = ceil($count / $pageSize);

    for ($i = 1; $i <= $pageCount; ++$i) {
        $start = ($i - 1) * $pageSize;
        $pageRows = array_slice($rows, $start, $pageSize);

        $params = array();
        $sql = "INSERT INTO {$this->table} ({$columnStr}) values ";
        foreach ($pageRows as $key => $row) {
            $marks = str_repeat('?,', count($row) - 1).'?';

            if (0 != $key) {
                $sql .= ',';
            }
            $sql .= "({$marks})";

            $params = array_merge($params, array_values($row));
        }

        $this->db()->executeUpdate($sql, $params);
        unset($params);
    }

    return true;
}

public function batchUpdate($identifies, $updateColumnsList, $identifyColumn = 'id')
{
    $updateColumns = array_keys(reset($updateColumnsList));

    $this->db()->checkFieldNames($updateColumns);
    $this->db()->checkFieldNames(array($identifyColumn));

    $count = count($identifies);
    $pageSize = 500;
    $pageCount = ceil($count / $pageSize);

    for ($i = 1; $i <= $pageCount; ++$i) {
        $start = ($i - 1) * $pageSize;
        $partIdentifies = array_slice($identifies, $start, $pageSize);
        $partUpdateColumnsList = array_slice($updateColumnsList, $start, $pageSize);
        $this->partUpdate($partIdentifies, $partUpdateColumnsList, $identifyColumn, $updateColumns);
    }
}
/**
 * @param $identifies
 * @param $updateColumnsList
 * @param $identifyColumn
 * @param $updateColumns
 *
 * @return int
 */
private function partUpdate($identifies, $updateColumnsList, $identifyColumn, $updateColumns)
{
    $sql = "UPDATE {$this->table} SET ";

    $updateSql = array();

    $params = array();
    foreach ($updateColumns as $updateColumn) {
        $caseWhenSql = "{$updateColumn} = CASE {$identifyColumn} ";

        foreach ($identifies as $identifyIndex => $identify) {
            $caseWhenSql .= ' WHEN ? THEN ? ';
            $params[] = $identify;
            $params[] = $updateColumnsList[$identifyIndex][$updateColumn];
            if ($identifyIndex === count($identifies) - 1) {
                $caseWhenSql .= " ELSE {$updateColumn} END";
            }
        }

        $updateSql[] = $caseWhenSql;
    }

    $sql .= implode(',', $updateSql);

    $marks = str_repeat('?,', count($identifies) - 1).'?';
    $sql .= " WHERE {$identifyColumn} IN ({$marks})";
    $params = array_merge($params, $identifies);

    return $this->db()->executeUpdate($sql, $params);
}

具体使用

批量创建
$create = [];
foreach ($data as $value){
  $create[] = [
       'userId' => 0,
       'checkinNum' => 0,
       'requestTime' => time(),
       'courseId' => 0,
       'chatNum' => 0,
       'answerNum' => 0,
     ];
   }
if(!empty($create)){
  XXXXXXXXXX->batchCreate($create);
}

批量更新
foreach ($data as $value){
    $update[$value['id']] = [
        'progressStatus' => 'test'
    ];
 }

  if(!empty($update)){
  XXXXXXXXXXX->batchUpdate(array_keys($update), $update, 'id');
 }