- A+
在开发过程中,我们可能遇到大批量的数据需要修改的功能,如果一条条修改,性能十分低下,因此,我们考虑,是否可以批量执行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) : ''; }