mysql sql blob查询_mysql blob数据的插入与查询

举例将一个本地图片读入到mysql里面的表字段  msg_body，这个字段为blob类型，并将该数据查询并写入到一个图片

代码如下：连接mysql省略，只列重点：

插入数据到表里面，

FILE*imgP;

fopen_s(&imgP,"gj_hover.png","rb");//这里是用二进制读取，read-r；binary-b；因为只弄r结果出错！！弄了后面那个的再来看这个才发现是这个的问题！！

if (imgP == NULL)return ;

fseek(imgP, 0L, SEEK_END);

long size = ftell(imgP);

byte*imgbuf = new byte[size+ 1];

fseek(imgP,0x0L,SEEK_SET);//图片源

fread(imgbuf, sizeof(imgbuf[0]), size, imgP);

const char *szSQL = "insert into msg_info(id,msg_body,date_add) values(?,?,?)";

char id[20]="1";

char date_add[22]={"2017-05-08 12:12:12"};

MYSQL_BIND db[3];

memset(db,0,sizeof(db));

db[0].buffer_type=MYSQL_TYPE_STRING;

db[0].buffer=id;

db[0].buffer_length=strlen(id);

db[0].is_null= 0;

db[0].length= 0;

db[1].buffer_type=MYSQL_TYPE_BLOB;

db[1].buffer=imgbuf;

db[1].buffer_length=size+1;

db[1].is_null= 0;

db[1].length= 0;

db[2].buffer_type=MYSQL_TYPE_STRING;

db[2].buffer=date_add;

db[2].buffer_length=strlen(date_add);

db[2].is_null= 0;

db[2].length= 0;

if (!m_lpDBModule.Execute(szSQL,db))

{

MessageBox(L"insert failer!");

return ;

}

fclose(imgP);

查询数据并写入到图片：

BOOL CMySqlDriver::GetData(const char* szSQL)

{

if (!m_pMySQL)

{

return FALSE;

}

MYSQL_STMT* pSmt = mysql_stmt_init(m_pMySQL);

if (!pSmt)

{

m_szError = mysql_error(m_pMySQL);

return FALSE;

}

int nRet = mysql_stmt_prepare(pSmt,szSQL,strlen(szSQL));

if (nRet != 0)

{

m_szError = mysql_stmt_error(pSmt);

mysql_stmt_close(pSmt);

return FALSE;

}

MYSQL_BIND result = {0};

unsigned long total_length = 0;

result.buffer_type = MYSQL_TYPE_BLOB;

result.length = &total_length;

nRet = mysql_stmt_bind_result(pSmt, &result);

assert(0==nRet);

nRet = mysql_stmt_execute(pSmt);

assert(0==nRet);

nRet = mysql_stmt_store_result(pSmt);

assert(0==nRet);

for (;;)

{

nRet = mysql_stmt_fetch(pSmt);

if (nRet!=0 && nRet!=MYSQL_DATA_TRUNCATED) break;

int start = 0;

char *buf=new char[total_length];

memset(buf,0,sizeof(buf));

printf("total_length=%lu\n", total_length);

while (start

{

result.buffer = (buf+start);

result.buffer_length = 3;  //每次读这么长

nRet = mysql_stmt_fetch_column(pSmt, &result, 0, start);

if (nRet!=0)

{

return FALSE;

}

start += result.buffer_length;

}

CFile f;

if(!f.Open(L"D:\\work\\testbigdata\\test.png", CFile::modeCreate|CFile::modeWrite | CFile::typeBinary))

{

return FALSE;

}

f.Write(buf,total_length);

f.Flush();

f.Close();

}

mysql_stmt_close(pSmt);

return TRUE;

}