php如何读取CSV大文件并且将其导入数据库示例

思路：

读取csv文件，每读取一行数据，就插入数据库

示例

文件夹结构

/

 file.csv    //csv大文件，这里只模拟三行数据，不考虑运行效率（PS：csv文件格式很简单，文件一般较小，解析很快，运行效率的瓶颈主要在写入数据库 *** 作）

 index.php    //php文件

file.csv

singi,20

lily,19

daming,23

index.php

/**

 * 读取csv文件，每读取一行数据，就插入数据库

 */

//获取数据库实例

$dsn = 'mysql:dbname=testhost=127.0.0.1'

$user = 'root'

$password = ''

try {

    $db = new PDO($dsn, $user, $password)

} catch (PDOException $e) {

    echo 'Connection failed: ' . $e->getMessage()

}

//读取file.csv文件

if (($handle = fopen("file.csv", "r")) !== FALSE) {

    while (($row = fgetcsv($handle, 1000, ",")) !== FALSE) {

        //写入数据库

        $sth = $db->prepare('insert into test set name=:name,age=:age')

        $sth->bindParam(':name',$row[0],PDO::PARAM_STR,255)

        $sth->bindParam(':age',$row[1],PDO::PARAM_INT)

        $sth->execute()

    }

    fclose($handle)

}

数据表

CREATE TABLE `test` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` VARCHAR(255) NULL DEFAULT '' COLLATE 'utf8mb4_bin',

`age` INT(10) NULL DEFAULT '0',

PRIMARY KEY (`id`)

)

COLLATE='utf8mb4_bin'

ENGINE=InnoDB

运行结束后，数据库中会插入csv中的三行数据

最近利用空闲时间自己在写一个文件备份工具，因为我磁盘上的很多文件很重要，例如很多PPT和讲义。所以需要经常备份，而且因为这些文件很多，所以需要增量备份。

我尝试用过windows自带的ntbackup工具，但感觉不是很爽。它不支持压缩备份，而且界面也有点复杂。

为了响应伟大领袖的“自力更生，丰衣足食”的号召，咱决定自己写一个工具，专门备份到数据库。支持压缩，支持加密，支持增量。

本文分享一下其中一些重点的技术细节

其中一个关键的技术就是将文件使用二进制的方式存放在数据库的varbinary(max)的字段中。该字段最大允许的长度为2GB。

对于一些小文件，我们可以一次性读取它的所有字节，然后一次提交到数据库

/// <summary>

/// 这个方法演示了如何一次提交所有的字节。这样导致的结果是：应用程序立即需要申请等同于文件大小的内存

/// </summary>

static void SubmitFileByOnce() {

string file = @"F:\功夫熊猫.rmvb"//文件大小为519MB

byte[] buffer = File.ReadAllBytes(file)

using (SqlConnection conn = new SqlConnection("server=(local)database=demointegrated security=true")) {

using (SqlCommand cmd = conn.CreateCommand())

{

cmd.CommandText = "INSERT INTO Files(FileName,FileContents) VALUES(@fileName,@fileContents)"

cmd.Parameters.AddRange(

new[]

{

new SqlParameter("@fileName",file),

new SqlParameter("@fileContents",buffer)

})

conn.Open()

cmd.ExecuteNonQuery()

conn.Close()

}

}

}

但是，上面的方法有几个问题，主要体现在如果文件比较大的话

1. 它需要一次性很大的内存，具体数据等同于文件大小。因为File.ReadAllBytes方法是将所有字节全部读入到内存。

2. 它会导致提交失败，就是因为数据太大了。数据库也会拒绝。

那么，我就对这个方法做了一下改进，将文件拆分为5MB一段，也就是说，此时每次申请的内存只有5MB。这就大大地提高了可用性。

/// <summary>

/// 这个方法是将文件切分为5MB的块，每次只是提交5MB，所以可能多次提交，但内存占用就比较小

/// </summary>

static void SubmitFileStepByStep() {

string file = @"F:\功夫熊猫.rmvb"//以这个文件为例，大小为519MB，一共需要的时间大约94秒。还是有点慢的，所以还可能需要进行压缩

FileStream fs = new FileStream(file, FileMode.Open)

byte[] buffer = new byte[5 * 1024 * 1024]

int readCount

using (SqlConnection conn = new SqlConnection("server=(local)database=demointegrated security=true"))

{

conn.Open()

while ((readCount = fs.Read(buffer, 0, buffer.Length)) >0)

{

using (SqlCommand cmd = conn.CreateCommand())

{

cmd.CommandText = "INSERT INTO Files(FileName,FileContents) VALUES(@fileName,@fileContents)"

cmd.Parameters.AddRange(

new[]

{

new SqlParameter("@fileName",file),

new SqlParameter("@fileContents",buffer)

})

cmd.ExecuteNonQuery()

}

}

conn.Close()

}

}

这样的话，有一个后果就是一个文件，可能在数据库中会有多条记录。所以在读取的时候，我们需要对其进行合并

static void DownloadFile() {

string file = @"F:\功夫熊猫.rmvb"

string destfile = @"E:\Temp\Temp.wmv"

using (SqlConnection conn = new SqlConnection("server=(local)database=demointegrated security=true"))

{

using (SqlCommand cmd = conn.CreateCommand())

{

cmd.CommandText = "SELECT FileContents FROM Files WHERE FileName=@fileName"

cmd.Parameters.AddRange(

new[]

{

new SqlParameter("@fileName",file),

})

conn.Open()

SqlDataReader reader = cmd.ExecuteReader()

FileStream fs = new FileStream(destfile, FileMode.Append, FileAccess.Write)

while (reader.Read())

{

byte[] buffer = (byte[])reader[0]

fs.Write(buffer, 0, buffer.Length)

}

fs.Close()

reader.Close()

conn.Close()

}

}

}

本文由作者：陈希章

---