1、使用SpreadsheetReader把数据写到txt,再用load data local 导入mysql
2、使用mysql load data local
$sql = "load data local infile '$txt_path' ignore into table 表名 character set gbk fields terminated by '_' lines terminated by '|'(`name`,`code`,`size`,`xingbie`,`type`,`names`,`lingshoujia`,`price`,`zaikushu`,`datetime`,`type_show`,`bili`);";
Db::execute($sql);
配置文件
'break_reconnect'
=>
true
,
//断线重连
'params' => [PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_EMULATE_PREPARES => true, PDO::MYSQL_ATTR_LOCAL_INFILE => true ],
3、mysql开启local_infile,修改配置文件
添加或修改 local_infile=1
4、示例
$filePath = "D:/ng/wl/public/uploads/20230103/222.csv";
$dbc = [
// 这里是数据库信息
'type' => Env::get('database.type', 'mysql'),
// 服务器地址
'hostname' => Env::get('database.hostname', '127.0.0.1'),
// 数据库名
'database' => Env::get('database.database', 'wl1'),
// 用户名
'username' => Env::get('database.username', 'root'),
// 密码
'password' => Env::get('database.password', 'root'),
// 端口
'hostport' => Env::get('database.hostport', '3306'),
//断线重连
'break_reconnect' => true,
// 数据库连接参数 可能需要这些参数 这里是在 TP 框架中需要设置的参数
'params' => [PDO::ATTR_CASE => PDO::CASE_LOWER,PDO::ATTR_EMULATE_PREPARES => true,PDO::MYSQL_ATTR_LOCAL_INFILE => true],
];
$rs = Db::connect($dbc)->execute("LOAD DATA LOCAL INFILE '{$filePath}' INTO TABLE fa_express_kuaidiniao CHARACTER SET gbk fields terminated by ',' IGNORE 1 LINES");