数据库-批量更新

正常批量更新数据 例如 更新用户状态

update  user  set status = 1;

如果遇到每个人的状态都不一样 只能一个一个更新

update  user  set status = 1 and uid =1 ;
update  user  set status = 0 and uid =2 ;
...

可用用join方式实现

UPDATE `user` a JOIN 
(
SELECT 1 AS id, 1 AS `status`
UNION 
SELECT 2 AS id, 0 AS `status`
) b USING(id)
SET a.status=b.status;

php 类实现

<?php

/**
 * 批量更新
 *
 * @author Lin
 */
class UpdateAll {

    /**
     * 获取批量更新
     * @param string $table 表名
     * @param array $where 更新条件 array('id')
     * @param array $data 更新数据（包含更新条件的数据）二维数组
     * @return string   获取更新sql语句
     */
    public static function execute($table, $where, $data) {
        $sql = self::getSql($table, $where, $data);
        if ($sql) {
            //自己根据需求完成更新代码
        }
        return $sql;
    }

    /**
     * 获取批量更新语句
     * 原理https://www.cnblogs.com/AaronCui/p/10968893.html
     * @param string $table 表名
     * @param array $where 更新条件 array('id')
     * @param array $data 更新数据（包含更新条件的数据）二维数组
     * @return string   获取更新sql语句
     */
    public static function getSql($table, $where, $data) {

        if (empty($table) || empty($where) || empty($data) || !is_array($where) || !is_array($data)) {

            return false;
        }
        $where_string = self::where($where);
        $update = self::update($data);
        $set = self::set($where, $data);
        if (!$update || !$set || !$where) {
            return false;
        }
        return "UPDATE `{$table}` a JOIN ( {$update}  ) b USING({$where_string})  SET {$set}";
    }

    /**
     * 获取where
     * @param type $where
     */
    protected static function where($where) {

        $string = '';
        foreach ($where as $value) {
            $string .= '`' . $value . '`,';
        }
        return substr($string, 0, strlen($string) - 1);
    }

    protected static function update($data) {
        if (!isset($data[0])) {
            return false;
        }
        $array_keys = array_keys($data[0]);
        $count = count($array_keys);
        $string = '';
        foreach ($data as $key => $value) {
            if (count($value) != $count) {
                return false;
            }
            $string2 = ' SELECT ';
            foreach ($array_keys as $value2) {
                if (!isset($value[$value2])) {
                    $string2 .= " null " . ' AS `' . $value2 . '`,';
                }else{
                    $string2 .= "'".$value[$value2]."'" . ' AS `' . $value2 .'`,';
                }
            }
            $string2 = substr($string2, 0, strlen($string2) - 1);

            $string .= $string2 . ' UNION';
        }
        return substr($string, 0, strlen($string) - 5);
    }

    /**
     * 
     * @param type $where
     * @param type $data
     */
    protected static function set($where, $data) {
        if (!isset($data[0])) {
            return false;
        }
        $array_keys = array_keys($data[0]);

        foreach ($array_keys as $key => $value) {
            if (in_array($value, $where)) {
                unset($array_keys[$key]);
            };
        }
        if (empty($array_keys)) {
            return FALSE;
        }
        $string = '';
        foreach ($array_keys as $value) {
            $string .= 'a.`' . $value . '`=b.`' . $value . '` ,';
        }
        return substr($string, 0, strlen($string) - 1);
    }

}

调用方式

// 测试用例 更新会员表的名称
$sql = UpdateAll::getSql( 'user', ['id'], [
                ['id' => '1', 'name' => 'test1'],
                ['id' => '2', 'name' => 'test2'],
                ['id' => '3', 'name' => 'test3'],
                ['id' => '4', 'name' => 'test4'],
       ]);
echo $sql;

本作品采用《CC 协议》，转载必须注明作者和本文链接