MySQL批量更新数据

使用方法:

<?php
require 'MySQLBatchUpdateData.php';
foreach ($topics as $vvv) {

    $result = DB::query("SELECT heats, articleid FROM `table_name` WHERE `itemid`='$vvv'");
    $topics = DB::fetch_array($result);
    $topics['heats'] += 1;

    if (empty($topics['articleid'])) {
        $topics['articleid'] = $itemid;
    } else {
        $topics['articleid'] .= ',' . $itemid;
    }

    $arr1[] = ['itemid' => $vvv, 'heats' => $topics['heats'], 'articleid' =>     $topics['articleid']];

}

// batchUpdate()方法返回示例
//    "UPDATE `table_name`
//        SET `itemid` = CASE `itemid` 
//            WHEN '4' THEN '4' 
//            WHEN '1' THEN '1' 
//        END,
//        `heats` = CASE `itemid` 
//            WHEN '4' THEN '1' 
//            WHEN '1' THEN '1' 
//        END,
//        `articleid` = CASE `itemid` 
//            WHEN '4' THEN '35' 
//            WHEN '1' THEN '35' 
//        END
//    WHERE `itemid` IN ('4','1') "

DB::query(batchUpdate('table_name', $arr1, 'itemid'));

?>

在 MySQLBatchUpdateData.php 中封装成PHP函数:

<?php
/**
 * 批量更新函数
 *
 * @param   $mytable    string  待更新的数据表名
 * @param   $data       array   待更新的数据，二维数组格式
 * @param   $field      string  值不同的条件，默认为itemid
 * @param   $params     array   值相同的条件，键值对应的一维数组
 * @return  bool|string
 */
function batchUpdate($mytable, $data, $field, $params = [])
{
    if (!is_array($data) || !$field || !is_array($params)) {
        return false;
    }

    $updates = parseUpdate($data, $field);
    $where = parseParams($params);

    // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
    // array_column()函数需要 PHP5.5.0+
    $fields = array_column($data, $field);
    $fields = implode(',', array_map(function ($value) {
        return "'" . $value . "'";
    }, $fields));
    $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $mytable, $updates, $field, $fields, $where);
    return $sql;
}

/**
 * 将二维数组转换成CASE WHEN THEN的批量更新条件
 *
 * @param   $data   array   二维数组
 * @param   $field  string  列名
 * @return  string  sql语句
 */
function parseUpdate($data, $field)
{
    $sql = '';
    $keys = array_keys(current($data));
    foreach ($keys as $column) {

        $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
        foreach ($data as $line) {
            $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
        }
        $sql .= "END,";
    }
    return rtrim($sql, ',');
}

/**
 * 解析where条件
 *
 * @param   $params
 * @return  array|string
 */
function parseParams($params)
{
    $where = [];
    foreach ($params as $key => $value) {
        $where[] = sprintf("`%s` = '%s'", $key, $value);
    }
    return $where ? ' AND ' . implode(' AND ', $where) : '';
}

?>