目录

• insert select
• 以一个表的结果作为另一个表的字段

(?<=`) .* 通过正则替换表结构中的无用数据只留下字段名

insert select

INSERT INTO assets (
`ip`,
`region`,
`key`,
`mac`,
`mac_factory`,
`os`,
`os_type`,
`os_version`,
`device`,
`sub_device`,
`factory`,
`os_cpe_info`,
`ip_form`,
`source`,
`ipint`,
`first_time`,
`created_at`,
`updated_at`,
`ip_type`,
`ip_status`,
`desc`,
`important_assets`,
`jt_status`,
`device_owner`,
`device_owner_mail` 
) SELECT
`ip`,
`region`,
`key`,
`mac`,
`mac_factory`,
`os`,
`os_type`,
`os_version`,
`device`,
`sub_device`,
`factory`,
`os_cpe_info`,
`ip_form`,
`source`,
`ipint`,
'2020-04-01 00:00:00',
'2020-04-01 00:00:00',
'2020-04-01 00:00:00',
`ip_type`,
`ip_status`,
`desc`,
`important_assets`,
`jt_status`,
`device_owner`,
`device_owner_mail` 
FROM
    assets;

Artisan::command('inspire', function () {

    $ips = [
        '172.24.193.127',
        '172.24.193.126',
        '172.24.193.125',
        '172.24.193.35',
        '172.24.193.33',
        '172.24.193.49',
        '172.24.193.23',
        '172.24.193.84',
        '172.24.236.179',
        '172.24.236.162',
    ];

    $sql = '';

    foreach ($ips as $ip) {

        $ipint  = ip2long($ip);
        $region = 'default';
        $key    = "{$ip}-{$region}";

        $replace = '';
        $replace .= "'{$ip}'," . PHP_EOL;
        $replace .= "'{$ipint}'," . PHP_EOL;
        $replace .= "'{$region}'," . PHP_EOL;
        $replace .= "'{$key}'," . PHP_EOL;

        $sql .=<<<EOF
INSERT INTO `asset_jt_share` (
  `ip`,
  `ipint`,
  `region`,
  `key`,
  `path`,
  `cur_conn`,
  `name`,
  `max_conn`,
  `created_at`,
  `updated_at`,
  `data_pool_source`,
  `data_pool_created_at`
) SELECT

{$replace}
  `path`,
  `cur_conn`,
  `name`,
  `max_conn`,
  '2020-06-24 00:00:00',
  '2020-06-24 00:00:00',
  `data_pool_source`,
  `data_pool_created_at`

  FROM
    asset_jt_share limit 10;
EOF;

        $sql .= PHP_EOL;
        $sql .= PHP_EOL;
        $sql .= PHP_EOL;
    }

    file_put_contents('tmpsql', $sql);

})->describe('Display an inspiring quote');

以一个表的结果作为另一个表的字段

select count(*) as applies_num, (select count(*) from assets) as assets_num from asset_applies