Question

我想用软件的前端存储图像和.docx/.doc、.pptx/.ppt、.pdf 文件。我不明白如何执行此操作以及如何在表格中插入 BLOB 和 CLOB 文件。请帮助。

我使用Kubuntu 11.04, MySQL5, Qt 4.7.3。

Answer 1

两种方式:

1 - 使用“http://dev.mysql.com/doc/refman/5.1/en/string-forests.html#formation_load-files”>LOAD_FILE 函数 -

INSERT INTO table1 VALUES(1, LOAD_FILE( data.png ));

- 将文件作为十六进制字符串插入文件,例如 -

INSERT INTO table1 VALUES 
  (1, x 89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082 );

Answer 2

INSERT INTO MY_TABLE(id, blob_col) VALUES(1, LOAD_FILE( /full/path/to/file/myfile.png )

LOAD_FILE有许多附加条件。从MySQL文件:

LOAD_ FILE( 文件名称)

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.

此外,LOAD_FILE在Linux中也有虫子。见http://bugs.mysql.com/bug.php?id=38403 ,和工作补差。在Ubuntu 12.04, MySQL 5.5.32,这对我有用:

Copy file to /tmp
Change ownership to mysql user chown mysql:mysql /tmp/yourfile
Log into mysql as mysql root user so you are sure you have FILE privilege
Run your insert statement

Answer 3

或者您可以仅使用 MySQL 工作台, 选择行、最后一行、插入一行而不用布条, 然后单击右键并选择“ 从文件滚动值 ” 。

Answer 4

INSERT INTO table1 VALUES(1, LOAD_FILE(data.png));

工作但不工作,但

INSERT INTO table1 VALUES(1, LOAD_FILE( data.png ));

(假设当地目录中存在数据.png)

Answer 5

对于那些在通过查询保存 Blob 时获得“ Column 图像不可能是空的” 错误的人:-

打开您的 MySql 命令行客户端端端端口, 并用 root 用户和类型登录

mysql> SHOW VARIABLES LIKE "secure_file_priv";

这将显示 MySql 访问文件所使用的安全路径。类似的东西

+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

您可以在此文件夹中粘贴文件, 或者将“ security_ file_priv” 变量值更改为“ 空白字符串 ”, 这样它就可以从任何地方读取文件。

Answer 6

如果您正在使用 Mysql 工作bench, 请在字段( 单元格) 上单击右键, 从文件选项中选择负载值, 然后浏览到文件, 单击打开并单击应用程序。这将自动生成这样的查询。

UPDATE `dbname`.`tablename` SET `columnname` = ? WHERE (`row` =  1 );

友情链接