PHP程序，将csv转成sql文件并导入数据库

时间：2022-10-17 14:08:12

PHP程序，将csv转成sql文件并导入数据库

代码的由来

最近接手一个老项目，经过了多名程序员开发，因为公司不大，之前的交接做的也不好，很多业务逻辑都没有书面描述，一些需要功能还需要程序员手工处理，所以新员工的培训成本太高，且交接不方便，所以和业务商量了一下，对代码进行整合优化，本文就是其中的一小难点。

首先介绍一下我们的系统，这是一个俱乐部平台，用户是通过内部系统进行添加，我们的系统没有添加的权限和功能，然而大部分的业务却又在在我们的系统中进行，所以需要用户数据同步，但是上级系统出于安全考虑不提供实时接口，只是每天提供相关的excel，我们需要比对后存入数据库。

原始的处理流程

我们首先分别在两台电脑上将excel转成csv文件，然后移到另一台linux电脑用Python程序处理为sql文件，移到线上命令行导入数据库，在运行php进行比对更新。
很繁琐，且离不开程序员，而且局限性很大。因为Python版本的问题，首先放弃了原Python处理csv的部分，准备改为php处理。

那如何把数据读出来存入数据库

按照早期入门时编写习惯，对于csv的处理代码，编写如下：

<?php
echo date('Y-m-d H:i:s').'|store<br />';
set_time_limit(0);
$file_name = 'store.csv';
$file = fopen($file_name,'rb');
$mysql_url = 'localhost';
$mysql_user = 'root';
$mysql_password = '123456';
$con = mysql_connect($mysql_url,$mysql_user,$mysql_password) or die('数据库连接失败');
mysql_select_db('test_db', $con);
mysql_query('set names utf8'); 
while (($data = fgetcsv($file, filesize($file_name), ",")) !== FALSE)
{
    foreach($data as $k2=>$v)
    {
        $data[$k2] = addslashes(trim($v));
    }
    $str = 'INSERT INTO `store` VALUES(\''.join('\',\'',$data).'\');';
    mysql_query($str); 
}
echo date('Y-m-d H:i:s').'|store<br />';

fgetcsv 解析读入的行并找出 CSV 格式的字段然后返回一个包含这些字段的数组。【手册】

store表的结构为54个字段，如图
PHP程序，将csv转成sql文件并导入数据库

csv文件包含39000+条数据，如图
PHP程序，将csv转成sql文件并导入数据库

上面代码的运行情况如下
PHP程序，将csv转成sql文件并导入数据库
程序运行了16秒+，对于时间而已还是在可接受的范围内，但是随着数据量的增加，时间还会增加，为了减少资源消耗，我决定采用和之前一样的思路，写sql文件

读取csv写入sql文件

<?php
echo date('Y-m-d H:i:s').'|store<br />';
set_time_limit(0);
$file_name = 'store.csv';
$file = fopen($file_name,'rb');
$getfile_name = 'store.sql';
$getfile = fopen($getfile_name,'a');
while (($data = fgetcsv($file, filesize($file_name), ",")) !== FALSE)
{
    foreach($data as $k2=>$v)
    {
        $data[$k2] = addslashes(trim($v));
    }
    $str = 'INSERT INTO `store` VALUES(\''.join('\',\'',$data).'\');';
    fwrite($getfile,$str."\n");
}
fclose($file);
fclose($getfile);
echo date('Y-m-d H:i:s').'|store<br />';

写到这问题就出现。

如何在程序最后将生成好的sql文件导入数据库

首先我想到了，MySQL导入sql文件用到的命令source，既然mysql_query()能“set names utf8“，那我猜这样应该也可以，但是结果却是失败的。数据没有导入。
于是我去网上找解决方案，但网上的方案都是将sql文件读出并逐行输入，这样的话还不如之前的直接入库。有点多此一举的架势。
这些方案不可行，我将视线转向了Linux，在优化之前，导入的操作就是用Linux的命令cat，这个在这就不细讲了。
既然是用cat导入，而php可以exec()函数去执行外部命令，那么代码修改如下：

<?php
echo date('Y-m-d H:i:s').'|store<br />';
set_time_limit(0);
$file_name = 'store.csv';
$file = fopen($file_name,'rb');
$getfile_name = 'store.sql';
$getfile = fopen($getfile_name,'a');
while (($data = fgetcsv($file, filesize($file_name), ",")) !== FALSE)
{
    foreach($data as $k2=>$v)
    {
        $data[$k2] = addslashes(trim($v));
    }
    $str = 'INSERT INTO `store` VALUES(\''.join('\',\'',$data).'\');';
    fwrite($getfile,$str."\n");
}
fclose($file);
fclose($getfile);
exec('cat /var/www/test/store.sql | mysql -u root --default-character-set=utf8 -p123456 test_db');
echo date('Y-m-d H:i:s').'|store<br />';

运行结果如下
PHP程序，将csv转成sql文件并导入数据库

最终代码

从图上可以看出，代码有明显的优化，时间为11秒+，是原来程序运行的时间的三分之二，但还有更快的速度，如下代码

<?php
echo date('Y-m-d H:i:s').'|store<br />';
set_time_limit(0);
$file_name = 'store.csv';
$file = fopen($file_name,'rb');
$getfile_name = 'store.sql';
$getfile = fopen($getfile_name,'a');
$k = 0;
while (($data = fgetcsv($file, filesize($file_name), ",")) !== FALSE)
{
    /****为了实现插入多行 start*****/
    if($k%3000==0&&$k>0)
    {
        $str = ';';
        fwrite($getfile,$str."\n");
        $str = 'INSERT INTO `store` VALUES';
        fwrite($getfile,$str."\n");
    }
    elseif($k>0)
    {
        $str = ',';
        fwrite($getfile,$str."\n");
    }
    else
    {
        $str = 'INSERT INTO `store` VALUES';
        fwrite($getfile,$str."\n");
    }
    /****为了实现插入多行 end*****/
    foreach($data as $k2=>$v)
    {
        $data[$k2] = addslashes(trim($v));
    }
    $str = '(\''.join('\',\'',$data).'\')';
    fwrite($getfile,$str);
    $k++;
}
$str = ';';
fwrite($getfile,$str."\n");
fclose($file);
fclose($getfile);
exec('cat /var/www/test/store.sql | mysql -u root --default-character-set=utf8 -p123456 test_db');
echo date('Y-m-d H:i:s').'|store<br />';

代码的运行结果如下
PHP程序，将csv转成sql文件并导入数据库
程序现在的执行时间是9秒+，比之前的又有提升，到此为止，我的野路子就描述完了，实现了将csv文件转成sql文件，并直接导入数据库。

补充，导入命令还有以下写法

<?php
exec('mysql -u root -p123456 --default-character-set=utf8 test_db < /var/www/test/store.sql');
标签：

相关文章