常规的批量更新方法
UPDATE t_user SET status=1 WHERE user_id>100;
所有user_id>100的记录的status值改成1,这句sql不出意外当然能执行成功。
但是我们有时候想让user_id=101的时候status改成101,user_id=102的时候status改成102……user_id=1001的时候status改成1001,这怎么搞?别急往下看……
“不”常规的批量更新方法
直接看sql
UPDATE t_user WHERE status=
(CASE
WHEN user_id=101 THEN 101
WHEN user_id=102 THEN 102
……
WHEN user_id=1001 THEN 1001
END)
WHERE user_id BETWWEN 100 AND 1001
好了应该都看懂了,但是最重要的一定要加上where 条件,不加where这条sql会去修改所有记录,能匹配到when里面的条件时就改成then后面的值,如果没有匹配到就改成null
既然这么常用,就封装一个吧
提供PHP示例
/**
* 批量更新
* @param string $table 表名,如‘user’
* @param array $rows 字段数据,如array(['id'=>1,'name'=>'liupei','age'=>18],['id'=>1,'name'=>'liupei2','age'=>19])
* @param string $primaryKey 主键字段,更新条件,如'id'
* @return int|string 返回影响行数,或者返回出错信息
*/
function updateBatch($table, $rows, $primaryKey)
{
// dump($rows);
if (empty($table)) {
return 'table不允许为空';
}
if (empty($rows)) {
return 'rows不允许为空';
}
if (empty($primaryKey)) {
return 'primaryKey不允许为空';
}
$sql = "UPDATE $table SET ";
$case = [];
$ids = [];
foreach ($rows as $row) {
$ids[] = $row[$primaryKey];
foreach ($row as $key=>$value) {
if ($key === $primaryKey) {
continue;
}
isset($case[$key]) || $case[$key] = "`{$key}`=(case ";
if (is_string($value)) {
$value = "'{$value}'";
}
$case[$key] .= " when {$primaryKey}=". $row[$primaryKey] . " then $value";
}
}
foreach ($case as &$item) {
$item .= ' end )';
$sql .= $item . ',';
}
// 必须加上搜索条件,否则数据会出错
$sql = rtrim($sql, ',') . " where `{$primaryKey}` in (". implode(',', $ids).')';
// echo $sql;
// 开始执行
$rs = 0;
try {
// 数据库执行,这里是基于tp5的数据操作
$rs = \think\Db::table($table)->execute($sql);
} catch (exception $e) {
return 'sql=' . $sql. ',error_message='.$e->getMessage();
}
return $rs;
}