/*** * 批量更新 * @param string $table 表名 * @param array $values 更新字段(二维数组) * @param string $index key值(主键) * @param array $where where条件 [['user_sn', '=', 2010000760], ['nick', '=', 'jack']] * @return bool */ public static function batchUpdate(string $table, array $values, string $index, array $where = []) { if (sizeof($values) <=0 || empty($index) || empty($table)) { return false; } $sets = $bindings = []; $updateSql = "UPDATE `" . $table . "` SET " ; $data = array_keys($values[0]); //index必须包含数据集合的键里面 if (!in_array($index, $data)) { return false; } $data = array_diff($data, [$index]); foreach ($data as $field) { $setSql = '`' . $field . '` = (CASE '; foreach ($values as $val) { $setSql .= 'WHEN `' . $index . '` = ? THEN ? '; $value = (is_null($val[$field]) ? 'NULL' : $val[$field]); $bindings[] = $val[$index]; $bindings[] = $value; } $setSql .= 'ELSE `'.$field.'` END) '; $sets[] = $setSql; } $updateSql .= implode(', ', $sets); $whereIn = array_column($values, $index); $bindings = array_merge($bindings, $whereIn); $whereIn = rtrim(str_repeat('?,', sizeof($whereIn)),','); //拼接where if (!empty($where)) { $operatorSymbol = ['=', '>', '<', '>=', '<=', '!=']; $whereStr = ''; $valueArr = []; foreach ($where as $v) { if (empty($v[0]) || empty($v[1]) || empty($v[2])) { return false; } if (!in_array($v[1], $operatorSymbol)) { return false; } $whereStr .= ' AND' . ' `' . $v[0] . '` ' . $v[1] . ' ?'; $valueArr[] = $v[2]; } $bindings = array_merge($bindings, $valueArr); $query = rtrim($updateSql,', ') . " WHERE `$index` IN (" . $whereIn . ")" . $whereStr . ";"; } else { $query = rtrim($updateSql,', ') . " WHERE `$index` IN (" . $whereIn . ");"; } return DB::update($query,$bindings); }
标签:string,批量,更新,values,param,array,php,where From: https://www.cnblogs.com/dawuge/p/17361015.html