MySql中LongBlob类型字段内容大约100多M时,直接从数据库中提取LongBlob类型字段的数据内容时,很容易出现内存不足错误,因此对其字段内容可以多次查询获取,其实现方式可以采用SUBSTRING,分多次提取出LongBlob类型字段的数据内容,示例代码如下:
MySqlCommand cmd1 = new MySqlCommand();
cmd1.Connection = con;
cmd1.CommandText = "select LENGTH(ObFile) from observerdatatable where YDOY='" + ydoy + "'AND SName='" + sname + "'";
cmd1.CommandType = CommandType.Text;
object oo = cmd1.ExecuteScalar();
int lenth = Convert.ToInt32(oo); //首先确定blob字段内容长度
int readlenth = 20000000; //每次读取的字节大小
int pos =1;
FileInfo fi = new FileInfo(fname);
var stream = fi.OpenWrite();//创建文件
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
while (true)
{
if (pos + readlenth >= lenth)
{
string where = string.Format("Select SUBSTRING(ObFile,{0}) from observerdatatable where YDOY='{1}' and SName='{2}'",
pos, ydoy, sname);
cmd.CommandText = where;
byte[] buffer = (byte[])cmd.ExecuteScalar();
stream.Write(buffer, 0, buffer.Length);//写入数据
buffer = null;
break;
}
else
{
string where = string.Format("Select SUBSTRING(ObFile,{0},{1}) from observerdatatable where YDOY='{2}' and SName='{3}'",
pos, readlenth,ydoy, sname);
cmd.CommandText = where;
byte[] buffer = (byte[])cmd.ExecuteScalar();
stream.Write(buffer, 0, buffer.Length);//写入数据
buffer = null;
pos += readlenth;
}
}
stream.Close();
stream.Dispose();