PHP生成MYSQL批量更新的SQL语句

    private function updateBatchSql($table, $data, $key) {
        if (empty($data)) {
            throw new Exception('data format is error');
        }
        $ids = array_column($data, $key);
        $_ids = implode("','", $ids);
        $cols = array_keys($data[0]);
        $sql = "UPDATE {$table} SET %s WHERE `{$key}` IN ('{$_ids}')";
        $update = [];
        foreach ($cols as $v) {
            if ($v == $key) continue;
            $_str = "`{$v}`= ( CASE `{$key}` %s ELSE `{$v}` END)";
            $_when = [];
            foreach ($data as $row) {
                $_when[] = "WHEN '{$row[$key]}' THEN '{$row[$v]}'";
            }
            $_str = sprintf($_str, implode(' ', $_when));
            $update[] = $_str;
        }
        $sql = sprintf($sql, implode(', ', $update));
        return $sql;
    }

版权属于：Joyber

本文链接：https://blog.qqvbc.com/default/311.html

转载时须注明出处及本声明