各位大虾,我这段时间一直在弄用mysql存储doc和html文件,总是出现问题。过程如下,请各位大虾帮忙。//创建表
mysql> create table test(id int not null,context blob not null);
Query OK, 0 rows affected (0.09 sec)//存储文件
mysql> insert into test(id,context) values(1,load_file("d:/a.doc"));
Query OK, 1 row affected (0.03 sec)mysql> insert into test(id,context) values(2,load_file("d:/a.htm"));
Query OK, 1 row affected (0.03 sec)//查询并保存文件
当我用mysql> select context from test where id=1(或者id=2)查询保存的文件内容的时候,查询出来的结果前面和后面都有很多的------如果用如下语句保存的文件时,doc文件无法打开,htm文件用ie打开后前面有很多空行。
mysql> select context from test where id=1 into outfile "d:/a2.doc";
Query OK, 1 row affected (0.03 sec)mysql> select context from test where id=2 into outfile "d:/a2.htm";
Query OK, 1 row affected (0.02 sec)这个问题已经困扰我很长时间了,请问各位老大如何解决。
mysql> create table test(id int not null,context blob not null);
Query OK, 0 rows affected (0.09 sec)//存储文件
mysql> insert into test(id,context) values(1,load_file("d:/a.doc"));
Query OK, 1 row affected (0.03 sec)mysql> insert into test(id,context) values(2,load_file("d:/a.htm"));
Query OK, 1 row affected (0.03 sec)//查询并保存文件
当我用mysql> select context from test where id=1(或者id=2)查询保存的文件内容的时候,查询出来的结果前面和后面都有很多的------如果用如下语句保存的文件时,doc文件无法打开,htm文件用ie打开后前面有很多空行。
mysql> select context from test where id=1 into outfile "d:/a2.doc";
Query OK, 1 row affected (0.03 sec)mysql> select context from test where id=2 into outfile "d:/a2.htm";
Query OK, 1 row affected (0.02 sec)这个问题已经困扰我很长时间了,请问各位老大如何解决。
解决方案 »
- MyISAM表,如果只select、insert的话,并发性如何?
- 求一条SQL语句,请高手赐教!
- mysql 总是提示 shutdown failed; error: 'Access denied 错误
- MYsql的procedure里面一个查询sql放在一个变量里面 怎么执行它
- Mysql::Error: #HY000Can't create/write to file 'E:\mysql\data\--install\#sql_ba8_0.MYI' (Errcode: 2): SHOW FIELDS FROM `composer
- 求助c#如何连接postgreSQL数据库,帮找错误
- MySql 能不能支持存储过程?
- msyql存储过程查询出某条数据在数据库所有表的表名以及所在表的行数
- 请问是什么问题?(如图)
- 拟开发一个php+mysql的gnu项目!愿意参与的请发帖!
- show tables获得的表名结果怎样用于其他查询语句,类似子查询那样?
- linux环境下,mysql-dba常用的维护命令
它与load data infile是对应的,想想,多了这么多控制符,当然内容不一样了。所以,你的解决办法应该是去除这些限制。mysql> select binary context from test where id=1 into outfile 'c:/output.doc' fields TERMINATED BY '' escaped by '' LINES TERMINATED BY '';这样就能去除默认的几个控制符转义。你再试试。
to:iihero
按照你的方法,确实导出的文件可以打开使用了,但是文件的大小却变化很多,我的a.doc文件是39K,但是导出的是64K,而且按照你的方法导出的a。htm文件还是有问题,大小也还是64K。请问这个怎么解决。
mysql> show variables like 'char%';
+--------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | D:\mysql-5.5.8-win32\share\charsets\ |
+--------------------------+--------------------------------------+
8 rows in set (0.00 sec)mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| innodb_version | 1.1.4 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.5.8 |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | x86 |
| version_compile_os | Win32 |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)mysql> insert into test values(2, load_file('c:/test.doc'));
Query OK, 1 row affected (0.05 sec)mysql> select length(context) from test where id=2;
+-----------------+
| length(context) |
+-----------------+
| 58880 |
+-----------------+
1 row in set (0.00 sec)mysql> select binary context from test where id=2 into outfile 'c:/test_out.doc' fields TERMINATED BY '' escaped by '' LINES TERMINATED BY '';
Query OK, 1 row affected (0.02 sec)mysql>看test_out.doc,仍然是58880字节。没有问题。
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | utf8
|
| character_set_connection | utf8
|
| character_set_database | utf8
|
| character_set_filesystem | binary
|
| character_set_results | utf8
|
| character_set_server | utf8
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.05 sec)
mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name | Value |
+-------------------------+------------------------------+
| protocol_version | 10 |
| version | 5.1.51-community |
| version_comment | MySQL Community Server (GPL) |
| version_compile_machine | ia32 |
| version_compile_os | Win32 |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)
mysql> select length(context) from test where id=2;
+-----------------+
| length(context) |
+-----------------+
| 36675 |
+-----------------+
1 row in set (0.00 sec)
查询到的也是36K。
mysql> select binary context from test where id=2 into outfile 'c:/test_out.doc'
fields TERMINATED BY '' escaped by '' LINES TERMINATED BY '';
Query OK, 1 row affected (0.00 sec)
导出来的确实64K
Directory of c:\02/19/2011 10:53 PM 58,880 test.doc
02/27/2011 11:36 PM 58,880 test_out.doc
02/27/2011 04:59 PM 58,880 test_out1.doc
3 File(s) 176,640 bytes
0 Dir(s) 28,935,954,432 bytes free
一个字节不差的。
已经结贴。