如何使用 LOAD_FILE 将文件加载到 MySQL blob 中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8229951/
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 use LOAD_FILE to load a file into a MySQL blob?
提问by Flex60460
I tried to load a file into a MySQL blob (on a Mac).
我试图将文件加载到 MySQL blob（在 Mac 上）。
My query is
我的查询是
INSERT INTO MyTable VALUES('7', LOAD_FILE('Dev:MonDoc.odt'))
No error appears but the file is not loaded into the blob.
没有出现错误，但文件未加载到 blob 中。
回答by markus
The manualstates the following:
该手册规定如下：
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.
As of MySQL 5.0.19, the character_set_filesystem system variable controls interpretation of file names that are given as literal strings.
LOAD_FILE（文件名）
读取文件并以字符串形式返回文件内容。要使用此功能，文件必须位于服务器主机上，必须指定文件的完整路径名，并且必须具有 FILE 权限。该文件必须可供所有人读取并且其大小小于 max_allowed_packet 字节。如果secure_file_priv 系统变量设置为非空目录名，则要加载的文件必须位于该目录中。
如果文件不存在或由于不满足上述条件之一而无法读取，则该函数返回 NULL。
从 MySQL 5.0.19 开始， character_set_filesystem 系统变量控制以文字字符串形式给出的文件名的解释。
mysql> UPDATE t
SET blob_col=LOAD_FILE('/tmp/picture')
WHERE id=1;
From this, I see more than one thing that could be wrong in your case...
由此，我看到在你的情况下可能出错的不止一件事......
- are you passing the full path?
- are privileges set correctly?
- what does the function return? NULL?
- have you tried it with the query given in the manual?
- 你通过了完整的路径吗？
- 权限设置是否正确？
- 函数返回什么？空值？
- 您是否尝试过使用手册中给出的查询？
回答by rmv
I had the same problem with Linux ...
我在 Linux 上遇到了同样的问题...
select load_file('/tmp/data.blob');
+-----------------------------+
| load_file('/tmp/data.blob') |
+-----------------------------+
| NULL |
+-----------------------------+
Eventually i could load the file successfully after user and group ownership were changed to 'mysql':
最终我可以在用户和组所有权更改为“mysql”后成功加载文件：
sudo chown mysql:mysql /tmp/data.blob
回答by kbc
double escape the slahes in the full path if you're in windows.
如果您在 Windows 中，请在完整路径中双重转义斜线。
回答by Uncle Iroh
I just wanted to add one more caveat that I found in my testing:
我只是想再添加一个我在测试中发现的警告：
when using select load_file('/path/to/theFile.txt');The file that you are loading HAS to be on the machine the sql instance is running on.
使用时select load_file('/path/to/theFile.txt');，您正在加载的文件必须位于运行 sql 实例的机器上。
This bit me in the butt for a long time because I use MySQL workbench to load files all the time into our various sql instances and when using commands like LOAD DATA LOCAL INFILE 'C:/path/to/theFile.csv' INTO TABLEthose would easily grab the file off of my local hard drive and process it into the tables regardless of where the actual sql instance was running. However the load_filecommand doesn't seem to behave at least for me in the same way (Maybe there exists a local_load_file() command I don't know about). MySQL seems to only allow it to look for files from the local system where the sql instance is running.
这让我困扰了很长时间，因为我一直使用 MySQL 工作台将文件加载到我们的各种 sql 实例中，并且当使用LOAD DATA LOCAL INFILE 'C:/path/to/theFile.csv' INTO TABLE这些命令时，很容易从我的本地硬盘驱动器中获取文件并将其处理到表中实际 sql 实例运行的位置。然而，该load_file命令似乎至少对我来说并没有以同样的方式运行（也许存在一个我不知道的 local_load_file() 命令）。MySQL 似乎只允许它从运行 sql 实例的本地系统中查找文件。
So if you're like me and you can't figure out why load_file is always returning NULL have no fear...upload the files to the sql server instance and then use that path from your Query browser and all will be well.
因此，如果您像我一样无法弄清楚为什么 load_file 总是返回 NULL，请不要担心...将文件上传到 sql server 实例，然后使用查询浏览器中的该路径，一切都会好起来的。
回答by William Choy
Thanks.
谢谢。
The user that is running mysql, needs to OWN the file. My mistake was, I thought it just needed to be able to READ or EXECUTE the file.
运行 mysql 的用户需要拥有该文件。我的错误是，我认为它只需要能够读取或执行文件。
