php构造mysql批量更新数据

最新推荐文章于 2024-02-26 13:58:30 发布

原创最新推荐文章于 2024-02-26 13:58:30 发布 · 560 阅读

0 ·

CC 4.0 BY-SA版权

PHP 同时被 2 个专栏收录

103 篇文章

订阅专栏

Mysql

42 篇文章

订阅专栏

本文介绍了一种使用PHP和SQL批量更新数据库中特定用户邮箱的方法，通过解析字符串并构造动态SQL语句实现高效的数据更新。

原理为：

UPDATE account SET `email` = CASE `user_name` 
WHEN 'a' THEN 'aa@hotmail.com' 
WHEN 'b' THEN 'bb@hotmail.com' 
WHEN 'c' THEN 'cc@163.com' 
END WHERE `user_name` IN ('a','b','c')

代码格式表现为：

public function actionUpdateemail(){

        $str = 'a	aa@163.com
b	bb@hotmail.com
c	cc@163.com';

        if (isset($_REQUEST['str'])) $str = $_REQUEST['str'];
        $dataArr = preg_split('/\s/',$str);
        $dataArr = array_filter($dataArr);
        $dataArrs = array_chunk($dataArr,2);

        $datas = [];
        $i = 0;
        foreach ($dataArrs as $arrs){
            foreach ($arrs as $kk=>$arr){
                if ($kk == 0){
                    $datas[$i]['user_name'] = $arr;
                }else{
                    $datas[$i]['email'] = $arr;
                }
            }
            $i++;
        }

        function parseUpdate($data, $field)
        {
            $sql = '';
            $keys = array_keys(current($data));
            array_shift($keys);
            foreach ($keys as $column) {

                $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
                foreach ($data as $line) {
                    $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
                }
                $sql .= "END,";
            }

            return rtrim($sql, ',');
        }

        function parseParams($params)
        {
            $where = [];
            foreach ($params as $key => $value) {
                $where[] = sprintf("`%s` = '%s'", $key, $value);
            }

            return $where ? ' AND ' . implode(' AND ', $where) : '';
        }

        function batchUpdate($data, $field, $params = [])
        {
            if (!is_array($data) || !$field || !is_array($params)) {
                return false;
            }

            $updates = parseUpdate($data, $field);
            $where = parseParams($params);

            // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
            $fields = array_column($data, $field);
            $fields = implode(',', array_map(function($value) {
                return "'".$value."'";
            }, $fields));

            $table = EbayAccount::staticModel()->tableName();
            $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $table, $updates, $field, $fields, $where);

            return $sql;
        }

        $sql = batchUpdate($datas, 'user_name');

        if ($_REQUEST['print']){
            echo $sql;exit();
        }

        $res = Account::staticModel()->getDbConnection()->createCommand($sql)->execute();
        var_dump($res);