如何将大文件写入到数据库中

最近利用空闲时间自己在写一个文件备份工具，因为我磁盘上的很多文件很重要，例如很多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:TempTemp.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()

}

}

}

本文由作者：陈希章

思路：

读取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中的三行数据

1、首先已管理员身份（通过win+r）进入命令提示符

2、先进入MYSQL安装目录，以我的为例：d:/wamp/bin/mysql/mysql5.5.20/bin 进入该目录的bin目录下，该目录下有个mysql.exe 文件

mysql 导入超大文件到数据库 - queen - 安然

3、为了保证一次性通过，可以先测试一下数据库的是否连接成功 mysql -u root -p (数据库有密码的写上密码)；再测试一下访问的数据库是否能访问 mysql -u root -p (数据库有密码的写上密码) craft(这个就是数据库的名称)；我第二次导入时遇到一个问题，就是我测试数据库连接成功后，直接又测试数据库是否能够访问，出现错误

mysql 导入超大文件到数据库 - queen - 安然

我检查了一遍命令没发现错误啊，没办法了，问我领导，人家三下五除二就搞定了，问了才知道，所有的命令都应该在

mysql 导入超大文件到数据库 - queen - 安然

下执行，包括测试数据库是否连接成功，数据库是否能访问和最后的数据库导入，我的错误就是在检查了数据库是否连接成功后就进入mysql里，没有退出来。好吧，又记了一遍。

4、如果上面的 *** 作没有问题，下一步就进行数据导入 *** 作

mysql -u root -p (数据库有密码的写上密码) craft <d:/craft.sql

导入过程可能会由于其他原因导致错误，我遇到的是在导入过程中可能由于编码的原因出现错误，所以，可以再加上点内容 mysql -u root -p --default-character-set=utf8 craft <d:/craft.sql，敲回车后，出现输入密码提示，如果数据库设置密码，就可以输入密码后再打回车，直到导入 *** 作完成

mysql 导入超大文件到数据库 - queen - 安然

注意：这样就完成了导入，不过中间还可能出现的问题就是要导入文件的权限问题，要保证你所登录的身份有对文件的写 *** 作，所以为了防止万一，我们可以将文件的所有权限都打开：选中文件，点右键，选择属性，选“安全”选项卡，选中你登录的角色，点“编辑”，选中“允许”下面的“完全控制”，点击确定。

---