resume = rst.getBlob("image"); break; } // (2)get the stream is = resume.getBinaryStream();
// (3)write to local file byte[] b = new byte[1024]; int len = 0; os = new FileOutputStream("C:/xajh/2.jpg"); while ((len = is.read(b)) != -1) { os.write(b, 0, len); }
另一个方法是：shell> mysqlbinlog hostname-bin.000001 > /tmp/statements.sqlshell> mysqlbinlog hostname-bin.000002 >> /tmp/statements.sqlshell> mysql -e "source /tmp/statements.sql"mysqlbinlog产生的输出可以不需要原数据文件即可重新生成一个LOAD DATA INFILE操作。mysqlbinlog将数据复制到一个临时文件并写一个引用该文件的LOAD DATA LOCAL INFILE语句。由系统确定写入这些文件的目录的默认位置。要想显式指定一个目录，使用--local-load选项。因为mysqlbinlog可以将LOAD DATA INFILE语句转换为LOAD DATA LOCAL INFILE语句(也就是说，它添加了LOCAL)，用于处理语句的客户端和服务器必须配置为允许LOCAL操作。参见5.6.4节，“LOAD DATA LOCAL安全问题”。警告：为LOAD DATA LOCAL语句创建的临时文件不会自动删除，因为在实际执行完那些语句前需要它们。不再需要语句日志后应自己删除临时文件。文件位于临时文件目录中，文件名类似original_file_name-#-#。--hexdump选项可以在注释中产生日志内容的十六进制转储：shell> mysqlbinlog --hexdump master-bin.000001上述命令的输出应类似：/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;# at 4#051024 17:24:13 server id 1 end_log_pos 98# Position Timestamp Type Master ID Size Master Pos Flags# 00000004 9d fc 5c 43 0f 01 00 00 00 5e 00 00 00 62 00 00 00 00 00# 00000017 04 00 35 2e 30 2e 31 35 2d 64 65 62 75 67 2d 6c |..5.0.15.debug.l|# 00000027 6f 67 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |og..............|# 00000037 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|# 00000047 00 00 00 00 9d fc 5c 43 13 38 0d 00 08 00 12 00 |.......C.8......|# 00000057 04 04 04 04 12 00 00 4b 00 04 1a |.......K...|# Start: binlog v 4, server v 5.0.15-debug-log created 051024 17:24:13# at startupROLLBACK;十六进制转储的输出包含下面的元素：· Position: The byte position within the log file.· Timestamp: The event timestamp. In the example just shown, '9d fc 5c 43' is the representation of '051024 17:24:13' in hexadecimal.· Type: The type of the log event. '0f' means that the example event is a FORMAT_DESCRIPTION_EVENT. The types are:· 00 UNKNOWN_EVENT· This event should never be present in the log.· 01 START_EVENT_V3· This indicates the start of a log file written by MySQL 4 or earlier.· 02 QUERY_EVENT· The most common type of events. These contain queries executed· on the master.· 03 STOP_EVENT· Indicates that master has stopped.· 04 ROTATE_EVENT· Written when the master switches to a new log file.· 05 INTVAR_EVENT· Used mainly for AUTO_INCREMENT values and if the LAST_INSERT_ID()· function is used in the statement.· 06 LOAD_EVENT· Used for LOAD DATA INFILE in MySQL 3.23.· 07 SLAVE_EVENT· Reserved for future use.· 08 CREATE_FILE_EVENT· Used for LOAD DATA INFILE statements. This indicates the start· of execution of such a statement. A temporary file is created· on the slave. Used in MySQL 4 only.· 09 APPEND_BLOCK_EVENT· Contains data for use in a LOAD DATA INFILE statement. The· data is stored in the temporary file on the slave.· 0a EXEC_LOAD_EVENT· Used for LOAD DATA INFILE statements. The contents of the· temporary file is stored in the table on the slave.· Used in MySQL 4 only.· 0b DELETE_FILE_EVENT· Rollback of LOAD DATA INFILE statement. The temporary file· should be deleted on slave.· 0c NEW_LOAD_EVENT· Used for LOAD DATA INFILE in MySQL 4 and earlier.· 0d RAND_EVENT· Used to send information about random values if the RAND()· function is used in the query.· 0e USER_VAR_EVENT· Used to replicate user variables.· 0f FORMAT_DESCRIPTION_EVENT· This indicates the start of a log file written by MySQL 5 or later.· 10 XID_EVENT· Event indicating commit of XA transaction· 11 BEGIN_LOAD_QUERY_EVENT· Used for LOAD DATA statements in MySQL 5 and later.· 12 EXECUTE_LOAD_QUERY_EVENT· Used for LOAD DATA statements in MySQL 5 and later.· 13 TABLE_MAP_EVENT· Reserved for future use· 14 WRITE_ROWS_EVENT· Reserved for future use· 15 UPDATE_ROWS_EVENT· Reserved for future use· 16 DELETE_ROWS_EVENT· Reserved for future use· Master ID: The server id of the master that created the event.· Size: The size in bytes of the event.· Master Pos: The position of the event in the original master log file.· Flags: 16 flags.· 01 LOG_EVENT_BINLOG_IN_USE_F· Log file correctly closed (Used only in FORMAT_DESCRIPTION_EVENT)· If this flag is set (if the flags are e.g. '01 00') in an· FORMAT_DESCRIPTION_EVENT, then the log file has not been· properly closed. Most probably because of a master crash (for· example, due to power failure).· 02 Reserved for future use.· 04 LOG_EVENT_THREAD_SPECIFIC_F· Set if the event is dependent on the connection it was· executed in (example '04 00'), e.g. if the event uses· temporary tables.· 08 LOG_EVENT_SUPPRESS_USE_F· Set in some circumstances when the event is not dependent on· the current database其它标志保留用于将来使用。在以后的版本中十六进制转储输出的格式可能会改变。 你试试！
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestQuery {
/**
* 测试从mysql读取二进制大字段,以jpg格式为例
*/
public void testQuery() {
// 1.create sql ;
String sql = "select * from user where id = ?";
// 2.get connection
Connection conn = null;
PreparedStatement psmt = null;
// query result
ResultSet rst = null;
// the image
Blob resume = null;
// local file
OutputStream os = null;
// the image stream
InputStream is = null;
try {
conn = JdbcHelper.getConnection();
// 3.prepare sql
psmt = conn.prepareStatement(sql);
// 4.set params
psmt.setLong(1, 8L);
// 5.query db
rst = psmt.executeQuery();
// 6.
// (1)get the image
while (rst.next()) {
resume = rst.getBlob("image");
break;
}
// (2)get the stream
is = resume.getBinaryStream();
// (3)write to local file
byte[] b = new byte[1024];
int len = 0;
os = new FileOutputStream("C:/xajh/2.jpg");
while ((len = is.read(b)) != -1) {
os.write(b, 0, len);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// (4)close file stream
try {
if (os != null)
os.close();
if (is != null)
is.close();
} catch (IOException e1) {
e1.printStackTrace();
}
// 7.close db
try {
if (psmt != null)
psmt.close();
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
TestQuery test = new TestQuery();
test.testQuery();
}
}
你试试！
#include <stdlib.h>
#include <mysql/mysql.h>
#include <string.h>
#include <unistd.h>
#include <sys/stat.h>/*把文件读入内存，成功返回长度，不成功返回-1*/
int GetFile(const char *p_szFileName, char *p_buff)
{
struct stat l_stat;
FILE *ifp;
memset(&l_stat, 0, sizeof(struct stat)); if (stat(p_szFileName, &l_stat) == - 1)
{
return - 1;
} ifp = fopen(p_szFileName, "rb"); if (ifp == NULL)
{
return - 1;
}
if (fread(p_buff, l_stat.st_size, 1, ifp) < 1)
{
fclose(ifp);
return - 1;
} fclose(ifp);
return l_stat.st_size;
}/*把文件从内存写入硬盘，成功返回0，不成功返回-1*/
int WriteFile(const char *p_szFileName, const char *p_buff, const int p_iSize, int flag = 1)
{
int ret;
FILE *fp; ret = access(p_szFileName, F_OK);
if (ret == 0 && flag != 1)
{
return - 1;
} if ((fp = fopen(p_szFileName, "wb")) == NULL)
{
return - 1;
} if (fwrite(p_buff, p_iSize, 1, fp) < 1)
{
fclose(fp);
unlink(p_szFileName);
return - 1;
} fclose(fp);
return 0;
}//-------------------------------------------------------------------------
//
//-------------------------------------------------------------------------int main()
{
MYSQL my_con;
MYSQL_STMT *stmt;
MYSQL_BIND bind;
char l_buff[1024 *1024];
int l_errno;
char l_sql[1024];
unsigned long l_flen; if (mysql_init(&my_con) == NULL)
{
fprintf(stderr, "No enough memory!\n");
return EXIT_FAILURE;
} if (mysql_real_connect(&my_con, "192.168.33.66", "root", "xxxxxx", "test", 3306, NULL, 0) == NULL)
{
fprintf(stderr, "Connection failed\n");
if ((l_errno = mysql_errno(&my_con)) != 0)
{
fprintf(stderr, "Connection:\t<%d>\t%s\n", l_errno, mysql_error(&my_con));
}
return EXIT_FAILURE;
}
mysql_query(&my_con, "SET NAMES GBK");
memset(l_sql, 0, sizeof(l_sql));
sprintf(l_sql, "INSERT INTO PERSON VALUES(6, '我爱北京喆汎袆祎', ?, 'F', 23)"); stmt = mysql_stmt_init(&my_con); if (stmt == NULL)
{
goto err_exit;
} if (mysql_stmt_prepare(stmt, l_sql, strlen(l_sql)) != 0)
{
fprintf(stderr, "mysql_stmt_prepare failed!\n");
goto stmt_exit;
} l_flen = GetFile("./6.jpg", l_buff);
if (l_flen == - 1)
{
fprintf(stderr, "Read file error!\n");
mysql_stmt_close(stmt);
mysql_close(&my_con);
return EXIT_FAILURE;
} memset(&bind, 0, sizeof(MYSQL_BIND));
bind.buffer_type = MYSQL_TYPE_BLOB;
bind.buffer_length = sizeof(l_buff);
bind.is_null = 0;
bind.length = &l_flen;
bind.buffer = l_buff; if (mysql_stmt_bind_param(stmt, &bind) != 0)
{
fprintf(stderr, "BIND ERROR!\n");
goto stmt_exit;
}
fprintf(stdout, "%d\n", l_flen);
if (mysql_stmt_execute(stmt) != 0)
{
fprintf(stderr, "EXECUTE ERROR!\n");
goto stmt_exit;
} mysql_stmt_close(stmt);
mysql_close(&my_con);
return EXIT_SUCCESS; stmt_exit: fprintf(stderr, "%s\n", mysql_stmt_error(stmt));
mysql_stmt_close(stmt);
err_exit: mysql_close(&my_con);
return EXIT_FAILURE;
}
#include <stdlib.h>
#include <mysql/mysql.h>
#include <string.h>
#include <unistd.h>
#include <sys/stat.h>
#include <sys/types.h>/*把文件读入内存，成功返回长度，不成功返回-1*/
int GetFile(const char *p_szFileName, char *p_buff)
{
struct stat l_stat;
FILE *ifp;
memset(&l_stat, 0, sizeof(struct stat)); if (stat(p_szFileName, &l_stat) == - 1)
{
return - 1;
} ifp = fopen(p_szFileName, "rb"); if (ifp == NULL)
{
return - 1;
}
if (fread(p_buff, l_stat.st_size, 1, ifp) < 1)
{
fclose(ifp);
return - 1;
} fclose(ifp);
return l_stat.st_size;
}/*把文件从内存写入硬盘，成功返回0，不成功返回-1，flag=1覆盖*/
int WriteFile(const char *p_szFileName, const char *p_buff, const int p_iSize, int flag = 1)
{
int ret;
FILE *fp; ret = access(p_szFileName, F_OK);
if (ret == 0 && flag != 1)
{
return - 1;
} if ((fp = fopen(p_szFileName, "wb")) == NULL)
{
return - 1;
} if (fwrite(p_buff, p_iSize, 1, fp) < 1)
{
fclose(fp);
unlink(p_szFileName);
return - 1;
} fclose(fp);
return 0;
}//-------------------------------------------------------------------------
//
//-------------------------------------------------------------------------
int main()
{
MYSQL my_con;
MYSQL_STMT *stmt;
MYSQL_BIND bind;
MYSQL_RES *res;
unsigned long paranum;
char l_sql[1024];
char l_picbuff[1024 *1024];
my_bool is_null;
unsigned long l_piclen;
int row;
char l_picname[20];
int l_errno; /*初始化mysql库*/
if (mysql_init(&my_con) == NULL)
{
fprintf(stderr, "No enough memory!\n");
return EXIT_FAILURE;
} /*连接数据库*/
if (mysql_real_connect(&my_con, "192.168.33.66", "root", "xxxxxx", "test", 3306, NULL, 0) == NULL)
{
fprintf(stderr, "Connection failed\n");
if ((l_errno = mysql_errno(&my_con)) != 0)
{
fprintf(stderr, "Connection:\t<%d>\t%s\n", l_errno, mysql_error(&my_con));
}
return EXIT_FAILURE;
}
/*填充查询语句*/
memset(l_sql, 0, sizeof(l_sql));
snprintf(l_sql, sizeof(l_sql) - 1, "select picture from PERSON"); if ((stmt = mysql_stmt_init(&my_con)) == NULL)
{
fprintf(stderr, "mysql_stmt_init out of memory!\n");
goto err_exit;
} if (mysql_stmt_prepare(stmt, l_sql, strlen(l_sql)))
{
fprintf(stderr, "mysql_stmt_prepare(), SELECT failed\n");
goto stmt_exit;
} if ((paranum = mysql_stmt_param_count(stmt)) != 0)
{
fprintf(stderr, "mysql_stmt_param_count() failed!\n");
goto stmt_exit;
} if ((res = mysql_stmt_result_metadata(stmt)) == NULL)
{
fprintf(stderr, "mysql_stmt_result_metadata() failed!\n");
goto stmt_exit;
} if (mysql_stmt_execute(stmt) != 0)
{
fprintf(stderr, "mysql_stmt_execute() failed!\n");
mysql_free_result(res);
goto stmt_exit;
} memset(&bind, 0, sizeof(MYSQL_BIND)); bind.buffer_type = MYSQL_TYPE_BLOB;
bind.buffer = l_picbuff;
bind.buffer_length = sizeof(l_picbuff);
bind.is_null = &is_null;
bind.length = &l_piclen; if (mysql_stmt_bind_result(stmt, &bind))
{
fprintf(stderr, "mysql_stmt_bind_result() failed\n");
mysql_free_result(res);
goto stmt_exit;
} if (mysql_stmt_store_result(stmt))
{
fprintf(stderr, "mysql_stmt_store_result() failed\n");
mysql_free_result(res);
goto stmt_exit;
}
row = 0;
while (!mysql_stmt_fetch(stmt))
{
row++;
fprintf(stdout, "test_%d.jpg\n", row);
if (!is_null)
{
memset(l_picname, 0, sizeof(l_picname));
sprintf(l_picname, "./test_%02d.jpg", row); if (WriteFile(l_picname, l_picbuff, l_piclen) == - 1)
{
fprintf(stderr, "WriteFile failed!\n");
mysql_free_result(res);
mysql_stmt_close(stmt);
mysql_close(&my_con);
return EXIT_FAILURE;
}
}
else
{
fprintf(stdout, "The %d picture is null\n", row);
}
} mysql_free_result(res);
mysql_stmt_close(stmt);
mysql_close(&my_con);
return EXIT_SUCCESS; stmt_exit: fprintf(stderr, "%s\n", mysql_stmt_error(stmt));
mysql_stmt_close(stmt);
err_exit: mysql_close(&my_con);
return EXIT_FAILURE;
}