[PHP知识库]PHP 批量更新多个字段

有一张表users，存在字段id（主键）、name、age

现需要一条sql批量更新不同的字段，也就是

UPDATE `users` SET `name` = (CASE id WHEN '1' THEN '张三A' WHEN '3' THEN '王五A' WHEN '4' THEN '小明A' end), `age` = (CASE id WHEN '1' THEN '15' WHEN '3' THEN '16' WHEN '4' THEN '17' end) where id in(1,3,4)

PHP 拼接sql

 /**
     * 批量更新数据
     * @param string $table_name
     * @param array $data [[],[],[]] 二维数组
     * @param string $PRI 主键
     * @return bool|false|int
     */
    function db_batch_update_sql($table_name = '', $data = [], $PRI = '')
    {
        if (!$table_name || !$data || !$PRI) {
            return false;
        } else {
            $sql = "UPDATE `{$table_name}`";
        }
        $con = array();
        $con_sql = array();
        $fields = array();
        foreach ($data as $key => $value) {
            $x = 0;
            foreach ($value as $k => $v) {
                if ($k != $PRI && !$con[$x] && $x == 0) {
                    $con[$x] = " SET `{$k}` = (CASE {$PRI} ";
                } elseif ($k != $PRI && !$con[$x] && $x > 0) {
                    $con[$x] = " `{$k}` = (CASE {$PRI} ";
                }
                if ($k != $PRI) {
                    $temp = $value[$PRI];
                    $con_sql[$x] .= " WHEN '{$temp}' THEN '{$v}' ";
                    $x++;
                }
            }
            $temp = $value[$PRI];
            if (!in_array($temp, $fields)) {
                $fields[] = $temp;
            }
        }
        $num = count($con) - 1;
        foreach ($con as $key => $value) {
            foreach ($con_sql as $k => $v) {
                if ($k == $key && $key < $num) {
                    $sql .= $value . $v . ' end),';
                } elseif ($k == $key && $key == $num) {
                    $sql .= $value . $v . ' end)';
                }
            }
        }
        $str = implode(',', $fields);
        $sql .= " where {$PRI} in({$str})";
        return $sql;
    }
	


$arr = [
    ['id' => 1, 'name' => '张三A', 'age' => '15'],
    ['id' => 3, 'name' => '王五A', 'age' => '16'],
    ['id' => 4, 'name' => '小明A', 'age' => '17'],
];
$sql = db_batch_update_sql('users', $arr, 'id');

创建表结构和数据sql

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(10) NOT NULL DEFAULT '0' COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `users` VALUES ('1', '张三', '15');
INSERT INTO `users` VALUES ('2', '李四', '18');
INSERT INTO `users` VALUES ('3', '王五', '20');
INSERT INTO `users` VALUES ('4', '小明', '25');