在开发过程中,我们可能遇到大批量的数据需要修改的功能,如果一条条修改,性能十分低下,因此,我们考虑,是否可以批量执行sql语句呢?
在ThinkPHP5由于使用的PDO的 execute方法,由于PDO的execute方法只可以执行一条Sql, 因此,我们是否可以通过其他办法实现呢?
接下来我们介绍利用mysql 的 case when 实现 mysql 的批量更新的方法,而且此种方法执行的效率会更高。
首先,我们准备一张表且准备几条数据:
CREATE TABLE `test_batch_update` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`create_time` int(11) DEFAULT NULL,
`update_time` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
接下来,我们用CASE WHEN 来实现数据的批量修改:
UPDATE test_batch_update
SET NAME = CASE id
WHEN 1 THEN
'啦啦啦啦'
WHEN 2 THEN
'呵呵'
END
WHERE
id IN (1, 2)
我们看到,数据已经发生了变化。
接下来,我们使用PHP来实现以上sql的生成,并将其封装成一个方法,供以后使用。
/**
* @param $tableName 表名
* @param $data 数据
* @param $field case 的字段名
* @param array $params 搜索条件
* @return bool|int
*/
public function batchSqlUpdateData($tableName, $data, $field, $params = [])
{
if (!is_array($data) || !$field || !is_array($params)) {
return false;
}
//生成update的数据内容
$updates = $this->parseUpdate($data, $field, $params);
//生成where条件
$where = $this->parseParams($params);
$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", $tableName, $updates, $field, $fields, $where);
return $this->execute($sql);
}
protected function parseUpdate($data, $field, $params)
{
$sql = '';
$keys = array_keys(current($data));
$whereFields = array_keys($params);
$whereFields[] = $field;
foreach ($keys as $column) {
if(!in_array($column, $whereFields)){
$sql .= sprintf('`%s` = CASE `%s` ', $column, $field);
foreach ($data as $line) {
$sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
}
$sql .= 'END ,';
}
}
return rtrim($sql, ',');
}
protected function parseParams($params)
{
$where = [];
foreach ($params as $key => $value) {
$where[] = sprintf("`%s` = '%s'", $key, $value);
}
return $where ? ' AND ' . implode(' AND ', $where) : '';
}