问题描述
我想使用我的软件的前端存储图像和.docx /.doc，.pptx /.ppt，.pdf文件。我不明白如何实现它以及如何将BLOB和CLOB文件插入表中。请帮忙。
我正在使用Kubuntu 11.04，MySQL5，Qt 4.7.3。
最佳解决思路
两种方式：
1 – 使用LOAD_FILE功能 –
INSERT INTO table1 VALUES(1, LOAD_FILE('data.png'));
2 – 将文件插入十六进制字符串，例如 –
INSERT INTO table1 VALUES
(1, x'89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082');
次佳解决思路
INSERT INTO MY_TABLE(id, blob_col) VALUES(1, LOAD_FILE('/full/path/to/file/myfile.png')
LOAD_FILE附带了许多条件。来自MySQL documentation：
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.
If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.
此外，Linux中存在LOAD_FILE错误。有关错误，请参阅http://bugs.mysql.com/bug.php?id=38403;有关解决方法，请参阅MySQL LOAD_FILE returning NULL。在Ubuntu 12.04，MySQL 5.5.32上，这对我有用：
-
将文件复制到/tmp
-
将所有权更改为mysql用户
chown mysql:mysql /tmp/yourfile -
以mysql root用户身份登录mysql，确保您拥有FILE权限
-
运行insert语句
第三种解决思路
或者您只能使用MySQL Workbench，选择行，最后一行，插入没有blob的行，然后右键单击并选择“从文件加载值”。