一、配置config/database.php

新增mysql连接

  1. 'mysql2'          => [
  2.             'driver'      => 'mysql',
  3.             'host'        => env('DB_HOST', '127.0.0.1'),
  4.             'port'        => env('DB_PORT', '3306'),
  5.             'database'    => '2346168_online',
  6.             'username'    => 'n2346168_online',
  7.             'password'    => '7pOIzzIw09(-*ibXNo5hHVb',
  8.             'unix_socket' => '/var/lib/mysql/mysql.sock',
  9.             'charset'     => 'utf8',
  10.             'collation'   => 'utf8_unicode_ci',
  11.             'prefix'      => '',
  12.             'strict'      => false,
  13.             'engine'      => null,
  14. ],

二、同步方法

  1. public function databaseSync(Request $request)
  2.     {
  3.           //接收要更新的表参数
  4.         $table     = $request->input('table');
  5.         $table_arr = [
  6.             'user_member'        => [
  7.                 'new_table' => 'image_user_member',
  8.                 'column'    => '(`id`, `phone`, `qq`, `wechat`, `nickname`, `avatar`, `token`, `expiry_time`, `regtime`, `status`, `pur_amount`, `pur_times`, `last_pur`, `vip_end_time`, `free_times`, `appid`, `vip_times`, `ext_config`, `qudao`, `order_situation`, `id_photo_times`, `guineas`, `listen_times`, `compositing_times`, `guineas_situation`, `cash_pay`)',
  9.                 'where'     => ['appid' => 4]
  10.             ],
  11.             'order_table'        => [
  12.                 'new_table' => 'image_order_table',
  13.                 'column'    => '(`id`, `order_id`, `group_id`, `user_id`, `amount`, `create_time`, `pay_time`, `pay_status`, `pay_method`, `days`, `vip_times`, `appid`, `qudao`, `from_fun`, `mch_id`, `order_situation`, `renewal_time`, `into_account`, `type`, `payment_type`, `guineas`, `order_specs`)',
  14.                 'where'     => ['appid' => 4]
  15.             ],
  16.             'valid_order'        => [
  17.                 'new_table' => 'image_valid_order',
  18.                 'column'    => '(id, user_id, group_id, order_id,start_time,end_time)',
  19.                 'where'     => []
  20.             ],
  21.             'user_member_op_log' => [
  22.                 'new_table' => 'image_user_member_op_log',
  23.                 'column'    => '(id, op_type, user_id, op_time,op_id)',
  24.                 'where'     => []
  25.             ],
  26.             'order_op_log'       => [
  27.                 'new_table' => 'image_order_op_log',
  28.                 'column'    => '(`id`, `op_type`, `order_id`, `op_time`, `op_id`, `appid`, `user_id`)',
  29.                 'where'     => ['appid' => 4]
  30.             ],
  31.             'product_statistics' => [
  32.                 'new_table' => 'image_product_statistics',
  33.                 'column'    => '(`id`, `user_id`, `appid`, `image_to_word_time`, `photo_translation_time`, `scanning_time`, `recording_time`, `video_to_audio_time`, `audio_clips`, `date_time`, `image_to_text_translation`, `retranslate`, `id_photo`, `image_to_form`, `form_identify`, `audio_segmentation`, `audio_merge`, `audio_format_conversion`, `real_time_transfer`,`voice_to_text`,`handwriting_recognition`,`automatic_segmentation`)',
  34.                 'where'     => ['appid' => 4]
  35.             ],
  36.             'mobile_login_log'   => [
  37.                 'new_table' => 'mobile_login_log',
  38.                 'column'    => '(`id`, `user_id`, `status`, `result`, `create_time`, `appid`)',
  39.                 'where'     => ['appid' => 4]
  40.             ],
  41.             'start_log'          => [
  42.                 'new_table' => 'start_log',
  43.                 'column'    => '(`id`, `user_id`, `appid`, `qudao`, `date`)',
  44.                 'where'     => ['appid' => 4]
  45.             ],
  46.         ];
  48.         //同步用户表
  49.         $list  = DB::connection('mysql2')->table($table)->where($table_arr[$table]['where'])->get(); //旧站数据
  50.         $count = $list->count();//总数
  51.         $list  = $list->toArray();
  52.         $page  = ceil($count / 1000);
  53.         for ($i = 1; $i <= $page; $i++) {
  54.             $str = '';
  55.             for ($l = 0; $l < 1000; $l++) {
  56.                 $values = '';
  57.                 $num    = ($i - 1) * 1000 + $l;
  58.                 if (isset($list[$num])) {
  59.                     if ($l > 0) {
  60.                         $str .= ",";
  61.                     }
  62.                     $values .= '(';
  63.                     foreach ($list[$num] as $k => &$item) {
  64.                         if ($k != 'id') {
  65.                             $values .= ',';
  66.                         }
  67.                         if (is_null($item)) {
  68.                             $item   = 'null';
  69.                             $values .= $item;
  70.                         } else {
  71.                             if (is_string($item)) {
  72.                                 $values .= "'" . addslashes($item) . "'";
  73.                             } else {
  74.                                 $values .= "'{$item}'";
  75.                             }
  76.                         }
  77.                     }
  78.                     $values .= ')';
  79.                     $str    .= $values;
  80.                 } else {
  81.                     break;
  82.                 }
  83.             }
  84.             DB::connection('mysql')->insert("insert ignore into `{$table_arr[$table]['new_table']}` {$table_arr[$table]['column']}  values {$str}");
  85.         }
  86.         echo "<p>$table sync finished</p>";
  87.     }