如何在 MySQL 中插入 BLOB 和 CLOB 文件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10729824/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert BLOB and CLOB files in MySQL?
提问by user1411472
I want to store images and .docx/.doc, .pptx/.ppt, .pdf files using the front end of my software. I don't understand how to implement this and how to insert the BLOB and CLOB files into the table. Please help.
我想使用我的软件前端存储图像和 .docx/.doc、.pptx/.ppt、.pdf 文件。我不明白如何实现这一点以及如何将 BLOB 和 CLOB 文件插入表中。请帮忙。
I am using Kubuntu 11.04, MySQL5, Qt 4.7.3.
我使用的是 Kubuntu 11.04、MySQL5、Qt 4.7.3。
回答by Devart
Two ways:
两种方式：
1 - Use a LOAD_FILEfunction -
1 - 使用LOAD_FILE函数 -
INSERT INTO table1 VALUES(1, LOAD_FILE('data.png'));
2 - Insert file as hex string, e.g. -
2 - 将文件作为十六进制字符串插入，例如 -
INSERT INTO table1 VALUES
(1, x'89504E470D0A1A0A0000000D494844520000001000000010080200000090916836000000017352474200AECE1CE90000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA8640000001E49444154384F6350DAE843126220493550F1A80662426C349406472801006AC91F1040F796BD0000000049454E44AE426082');
回答by lreeder
INSERT INTO MY_TABLE(id, blob_col) VALUES(1, LOAD_FILE('/full/path/to/file/myfile.png')
LOAD_FILE has many conditions attached to it. From the MySQL documentation:
LOAD_FILE 有许多附加条件。从MySQL 文档：
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.
LOAD_FILE（文件名）
读取文件并以字符串形式返回文件内容。要使用此功能，文件必须位于服务器主机上，必须指定文件的完整路径名，并且必须具有 FILE 权限。该文件必须可供所有人读取并且其大小小于 max_allowed_packet 字节。如果secure_file_priv 系统变量设置为非空目录名，则要加载的文件必须位于该目录中。
如果文件不存在或由于不满足上述条件之一而无法读取，则该函数返回 NULL。
Also, there there are bugs with LOAD_FILE in Linux. See http://bugs.mysql.com/bug.php?id=38403for the bug, and MySQL LOAD_FILE returning NULLfor workarounds. On Ubuntu 12.04, MySQL 5.5.32, this works for me:
此外，Linux 中的 LOAD_FILE 也存在错误。有关该错误，请参阅http://bugs.mysql.com/bug.php?id=38403，以及MySQL LOAD_FILE 返回 NULL以获取解决方法。在 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
- 将文件复制到 /tmp
- 将所有权更改为 mysql 用户
chown mysql:mysql /tmp/yourfile - 以 mysql root 用户身份登录 mysql，确保您拥有 FILE 权限
- 运行插入语句
回答by Rodd Johnson
Or you could merely use the MySQL Workbench, select the rows, last rows, insert a row without the blob, then just right click and select "Load Value From File".
或者您可以仅使用 MySQL Workbench，选择行，最后一行，插入没有 blob 的行，然后右键单击并选择“从文件加载值”。
回答by Chris Hobbs
INSERT INTO table1 VALUES(1, LOAD_FILE(data.png));
won't work but
不会工作，但
INSERT INTO table1 VALUES(1, LOAD_FILE('data.png'));
should (assuming data.png exists in the local directory)
应该（假设 data.png 存在于本地目录中）
回答by Ravi.Dudi
for those People who are getting "Column 'image' cannot be null" error while saving Blob through query :-
对于那些在通过查询保存 Blob 时遇到“列'图像'不能为空”错误的人：-
Open your MySql Command Line Client and login with root user and type
打开您的 MySql 命令行客户端并使用 root 用户登录并输入
mysql> SHOW VARIABLES LIKE "secure_file_priv";
this will show you the secure path used by MySql to access the files. something like
这将显示 MySql 用于访问文件的安全路径。就像是
+------------------+-----------------------+
| Variable_name | Value |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
you can either paste files inside this folder or change the "secure_file_priv" variable value to "empty string" so that it can read file from anywhere.
您可以将文件粘贴到此文件夹中，也可以将“secure_file_priv”变量值更改为“空字符串”，以便它可以从任何地方读取文件。
