柔晶美网络工作室

柔晶美网络工作室，倾心于web技术的博客站点

关注我 微信公众号

您现在的位置是： 首页 > 博客日记

将老系统数据迁移到laravel，编写php批量处理附件

2020-10-24 admin php  laravel  1205

因老的OA办公系统免费变为收费，因此准备用Laravel8.0重构一个类似的系统，实现单位内部各部门内部发邮件和文件的功能。

系统开发完成后，接下来的任务更为复杂，就是多年的老数据（包含附件）需要读取出来，迁移到新系统中。其中最关键的是MySQL数据库的迁移。

不过，laravel支持同时操作多个数据库，因此，使用查询构造器来批量处理老数据，再整理格式化后导入数据库。方法记录如下：

在根目录的.env文件中添加新数据库：

#默认信呼数据库
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=
DB_USERNAME=
DB_PASSWORD=
#新数据库
DB_HOST_CENTER=127.0.0.1
DB_PORT_CENTER=3306
DB_DATABASE_CENTER=
DB_USERNAME_CENTER=
DB_PASSWORD_CENTER=

然后在/config/database.php文件中添加数据库(要对应上面的名称)：

        'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => true,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],
        'mysql_center' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST_CENTER', '127.0.0.1'),
            'port' => env('DB_PORT_CENTER', '3306'),
            'database' => env('DB_DATABASE_CENTER', 'forge'),
            'username' => env('DB_USERNAME_CENTER', 'forge'),
            'password' => env('DB_PASSWORD_CENTER', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

查询构造器默认使用第一个数据库，如果要使用第二个数据库，则需要指定：

DB::connection('mysql_center')->table('admin_yhz')

接下来是整理的过程，需要注意几点：一是数组的合并去重处理，二是数据库的分块读取和批量插入。在处理过程中，分两步完成，第一部是切换不同的分类进入导入：

    public function index()
    {
        $receive = ["4","5","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","63","64","65","66","67","68","69","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","114","115","116","117","118","119","120","121","122","123","125","126","127","128","129"];//学校
        //$receive = ["96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", "112"];//县直小学
        //$receive = ["71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94"];//农村小学
        //$receive = ["4", "5", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61"];//农村初中
        //$receive = ["63", "64", "65", "66", "67", "68", "69", "107", "108", "109", "110", "111"];//完中
        //$receive = ["114", "115", "116", "117", "118", "119", "120", "121", "122", "123"];//民办学校
        //$receive = ["125", "126", "127", "128", "129"];//其它学校
        //$receive = ["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38"];//机关股室
        //$receive = ["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38","4","5","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","63","64","65","66","67","68","69","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","114","115","116","117","118","119","120","121","122","123","125","126","127","128","129"];//于都县教育局
        DB::table('xinhu_emailm')->where(['recename'=>'于都县教育局','isfile'=>1])->orderBy('id')->chunk(100, function ($data)use($receive) {
            if(empty($data))exit('无数据');
            foreach($data as $value){
                $id = $value->id;
                $title = $value->title;//标题title
                $content = $value->content;//内容content
                $sendname = $value->sendname;//发件人cjz
                $senddt = $value->senddt;//发件时间created_at  updated_at
                $files = DB::table('xinhu_file')->where(['mtype'=>'emailm','mid'=>$id])->pluck('filepath')->toArray();
                $newfile = array();//一定要先清空
                if($files){
                    foreach($files as $file){
                        $name = basename($file);//处理文件路径
                        $newfile[] = str_replace(['upload',$name],['https://zbcx.dzbfsj.com/uploads/files',$name],$file);
                    }
                }
                $update[] = array('title'=>$title,'content'=>$content,'files'=>json_encode($newfile),'receive'=>json_encode($receive),'cjz'=>$sendname,'created_at'=>$senddt,'updated_at'=>$senddt);
            }
            DB::connection('mysql_center')->table('bgw')->insert($update);
        });
        echo '导入完成！';
    }

第二部分较复杂，需要对数据进行合并去重等处理，导入公告也类似，最后将邮件和公告合并，代码如下：

    //批量导入通知公告
    public function index()
    {
        DB::table('xinhu_infor')->orderBy('id')->chunk(100, function ($data) {
            foreach($data as $value){
                $id = $value->id;
                $title = $value->title;//标题title
                $content = $value->content ?? '见附件';//内容content
                $sendname = $value->optname;//发件人cjz
                $senddt = $value->optdt;//发件时间created_at  updated_at
                $recename = explode(',',$value->recename);
                $yhz = DB::connection('mysql_center')->table('admin_yhz')->whereIn('name',$recename)->pluck('id')->toArray();
                if(in_array('学校',$recename)){
                    $recename = array_merge($yhz,["4","5","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","63","64","65","66","67","68","69","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","114","115","116","117","118","119","120","121","122","123","125","126","127","128","129"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('于都县教育局',$recename)){
                    $recename = array_merge($yhz,["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38","4","5","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","63","64","65","66","67","68","69","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","114","115","116","117","118","119","120","121","122","123","125","126","127","128","129"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('股室',$recename)){
                    $recename = array_merge($yhz,["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('完中',$recename)){
                    $recename = array_merge($yhz,["63", "64", "65", "66", "67", "68", "69", "107", "108", "109", "110", "111"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('县直小学',$recename)){
                    $recename = array_merge($yhz,["96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", "112"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('农村小学',$recename)){
                    $recename = array_merge($yhz,["71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('农村初中',$recename)){
                    $recename = array_merge($yhz,["4", "5", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('其它学校',$recename)){
                    $recename = array_merge($yhz,["125", "126", "127", "128", "129"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }else{
                    $receive = array_values(array_diff($yhz, [1,2,3,6,62,70,95,113,124]));
                }
                if(empty($receive))$receive = ["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38","4","5","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","63","64","65","66","67","68","69","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","114","115","116","117","118","119","120","121","122","123","125","126","127","128","129"];
                $files = DB::table('xinhu_file')->where(['mtype'=>'infor','mid'=>$id])->pluck('filepath')->toArray();
                $newfile = array();
                if($files){
                    foreach($files as $file){
                        $name = basename($file);//处理文件路径
                        $newfile[] = str_replace(['upload',$name],['https://zbcx.dzbfsj.com/uploads/files',$name],$file);
                    }
                }
                $update[] = array('title'=>$title,'content'=>$content,'files'=>json_encode($newfile),'receive'=>json_encode($receive),'cjz'=>$sendname,'created_at'=>$senddt,'updated_at'=>$senddt);
            }
            //dd($update);
            DB::connection('mysql_center')->table('bgw')->insert($update);
        });
        echo '导入完成';
    }
    //批量导入内部邮件
    public function index()
    {
        DB::table('xinhu_emailm')->where('isfile','1')->orderBy('id')->chunk(100, function ($data) {
            foreach($data as $value){
                $id = $value->id;
                $title = $value->title;//标题title
                $content = $value->content;//内容content
                $sendname = $value->sendname;//发件人cjz
                $senddt = $value->senddt;//发件时间created_at  updated_at
                $recename = explode(',',$value->recename);
                $yhz = DB::connection('mysql_center')->table('admin_yhz')->whereIn('name',$recename)->pluck('id')->toArray();
                if(in_array('学校',$recename)){
                    $recename = array_merge($yhz,["4","5","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","63","64","65","66","67","68","69","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","114","115","116","117","118","119","120","121","122","123","125","126","127","128","129"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('于都县教育局',$recename)){
                    $recename = array_merge($yhz,["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38","4","5","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","63","64","65","66","67","68","69","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","114","115","116","117","118","119","120","121","122","123","125","126","127","128","129"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('股室',$recename)){
                    $recename = array_merge($yhz,["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('完中',$recename)){
                    $recename = array_merge($yhz,["63", "64", "65", "66", "67", "68", "69", "107", "108", "109", "110", "111"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('县直小学',$recename)){
                    $recename = array_merge($yhz,["96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106", "112"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('农村小学',$recename)){
                    $recename = array_merge($yhz,["71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('农村初中',$recename)){
                    $recename = array_merge($yhz,["4", "5", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }elseif(in_array('其它学校',$recename)){
                    $recename = array_merge($yhz,["125", "126", "127", "128", "129"]);
                    $receive = array_unique(array_values(array_diff($recename, [1,2,3,6,62,70,95,113,124])));
                }else{
                    $receive = array_values(array_diff($yhz, [1,2,3,6,62,70,95,113,124]));
                }
                if(empty($receive))$receive = ["7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38","4","5","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","63","64","65","66","67","68","69","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","114","115","116","117","118","119","120","121","122","123","125","126","127","128","129"];
                $files = DB::table('xinhu_file')->where(['mtype'=>'emailm','mid'=>$id])->pluck('filepath')->toArray();
                $newfile = array();
                if($files){
                    foreach($files as $file){
                        $name = basename($file);//处理文件路径
                        $newfile[] = str_replace(['upload',$name],['https://zbcx.dzbfsj.com/uploads/files',$name],$file);
                    }
                }
                $update[] = array('title'=>$title,'content'=>$content,'files'=>json_encode($newfile),'receive'=>json_encode($receive),'cjz'=>$sendname,'created_at'=>$senddt,'updated_at'=>$senddt);
            }
            //dd($update);
            DB::connection('mysql_center')->table('bgw')->insert($update);
        });
        echo '导入完成';
    }

记录完毕。

上一篇PHP通用工资查询系统多用户版（支持多个下属单位）

下一篇Laravel8.0在查询数据库时遇到的问题

文章评论


需要 登录 才能发表评论
热门评论
0条评论

暂时没有评论！