php sql导出

码龄9年

关注

转载最新推荐文章于 2026-08-20 14:36:12 发布 138 阅读 GEO检测

composer dump-autoload php artisan db:seed --class=CarTypesSeeder

use CarV2\Constants\TypesConstant;
use Illuminate\Database\Seeder;
use \CarV2\Models\CarTypes;
use \Illuminate\Database\Eloquent\Builder;

// php artisan db:seed --class CarTypesSeeder
class CarTypesSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $this->sqlDump();
        // ...
    }
}

private function sqlDump() {
        /** @var $query Builder */
        $query = CarTypes::where("id", ">", 95);
        $tpl =
"INSERT INTO `life_car_types`(`id`,`type`,`name`,`sort`,`status`,`other`,`created_at`,`updated_at`) ".
"VALUES(%d,%d,'%s',0,1,'','%s','%s');\n";

        $out = fopen(storage_path()."/2022_life_car_types.sql", "w");
        $query->each(function(CarTypes $carType) use ($tpl, $out) {
            $id = $carType->getAttribute("id");
            $type = $carType->getAttribute("type");
            $name = $carType->getAttribute("name");
            /** @var $createdAt \Illuminate\Support\Carbon */
            $createdAt = $carType->getAttribute(CarTypes::CREATED_AT);
            $updatedAt = $carType->getAttribute(CarTypes::UPDATED_AT);
            $sql = sprintf($tpl, $id, $type, $name,
                date("Y-m-d H:i:s", $createdAt->getTimestamp()),
                date("Y-m-d H:i:s", $updatedAt->getTimestamp()));
            fwrite($out, $sql, strlen($sql));
        });
        fclose($out);
        exit(0);
    }

本地新增的条目导出为SQL，发起SQL上线

阿里云登录 - 欢迎登录阿里云，安全稳定的云计算服务平台

方法一

* 安装 navicat for mysql

Ctrl +Q 新建查询

SELECT * FROM W_R_Auth_PaidCourse
WHERE activemodule = 0 AND structtype = 3 AND userid = 27900612;

对结果集复制为 insert语句或 update语句

方法二：

* 安装mysqlworkbench

* 新建查询sql

Copy Row (with names)

* 粘贴到 W_SalesPackages.txt文件中

# guid, pids, name, coursecontent, standard, teacherids, grades, pricedescription, packageimage, servicecontent, improvecontent, opentime, closetime, ispubli\
sh, publishdate, isvalid, inserttime, type, tutortimes, subject
'p000', 'p12345', '测试', '<p>文科：包含高二数学、英语上下学期同步复习课程、高二语文同步复习课程、高中地理同步复习课程一、高中地理同步复习课程二，共7个课程，优惠价1500元（按单科报名原价：2100元）！赠送课程：加赠高一英语上下学期同步复习课程，共2个课程，价值600元！</p>', '2', '凃洁,黎宁,林斌,孟卫东,郑克强,林祖荣,田佩淮', 'g2', '理科,2300,3300/文科,1500,2100', 'edm.jpg', 'service2_img.png', '', '2012-05-16 02:00:00', '2012-05-30 03:15:00', '0', '0000-00-00 00:00:00', '0', '2012-05-16 07:06:00', '0', '0', ''
'P0001', 's187927,s184480', '内部测试打包课', '内部测试打包课请勿发布', '0', '内部测试打包课', '0', ',,/,,/', 'banner_register_20170911.jpg', 'banner_register_20170911.jpg', 'banner_register_20170911.jpg', '0000-00-00 00:00:00', '1970-00-00 00:00:00', '0', '0000-00-00 00:00:00', '0', '2018-01-18 08:56:10', '0', '0', ''
'p1', 'p1', '图片入口', '测试测试', '1', '凃洁,黎宁,林斌,孟卫东,郑克强,林祖荣,田佩淮', 'g1', '优惠价,2300,3300', '20141114huyhi.jpg', '2014020807.png', '', '2012-05-09 00:00:00', '2021-08-31 00:00:00', '1', '0000-00-00 00:00:00', '0', '2012-05-09 06:48:25', '0', '0', ''

* LineProcessor.php

<?php

class LineProcessor {
    /** @var \SplFileObject */
    private $file;

    /** @var int */
    private $linum;

    /** @var  callable */
    protected $handler;

    public function __construct(string $path) {
        $this->file = new \SplFileObject($path, 'r');
        $this->linum = 0;
    }

    public function getline() /* :string */ {
        return $this->file->fgets();
    }

    /**
     * 试着读取$limit行, 对每一行执行$callback
     * @param callable $callback
     * @param int $limit default unlimited
     * @return int 实际读取的行数
     */
    public function forEach(callable $callback, int $limit = 0) {
        // unlimited
        if (0===$limit) {
            while ($this->file->valid()) {
                $line = $this->file->fgets();
                call_user_func($callback, $line, $this->linum);
                $this->linum++;
            }
            return 0;
        }
        for ($i = 0; $i < $limit && $this->file->valid(); $this->linum++) {
            $line = $this->file->fgets();
            if (!empty($line)) {
                call_user_func($callback, $line, $i);
                $i++;
            }
        }
        return $i;
    }

    public function setHandler(callable $cb) {
        if (!$cb) {
            $cb = function($cur, $i) {
                printf("%d %s\n", $i, $cur);
            };
        }
        $this->handler = $cb;
    }

    public function run() {
        $this->forEach($this->handler);
    }

}

* index.php

<?php

function __autoload($className) {
    include $className.'.php';
}

if ($argc < 2) {
    echo 'Usage: php '.__FILE__.' input.txt > output.sql'.PHP_EOL;
    exit;
}
$p = new \LineProcessor($argv[1]);
$a = explode(DIRECTORY_SEPARATOR, $argv[1]);
$filename = array_pop($a);
$parts = explode('.', $filename);
$tableName = $parts[0];

$fs = $p->getline();
$fs = substr($fs, 1);
$fs[strlen($fs)-1] = ' ';
$fields = explode(', ', $fs);

foreach ($fields as &$field) {
    $field = trim($field);
}
$fs = '`'.implode('`,`', $fields).'`';

$sql = "insert into `$tableName`(".$fs.") values";

$p->setHandler(function($cur) use ($sql) {
     // 去掉结尾\n, window \r\n 需要-2
    $cur = substr($cur, 0, strlen($cur)-1);
    if (!empty($cur)) {
        echo $sql.'('.$cur.');'.PHP_EOL;
    }
});

$p->run();