LOAD_FILE()函数的替代方法? [英] Alternative to LOAD_FILE() function?

问题描述

我已经搜索并找到了这篇文章(http://stackoverflow.com/questions/1814297/cant-load-file-data-in-the-mysql-directory)，但它对我不起作用.

I have searched and found this post (http://stackoverflow.com/questions/1814297/cant-load-file-data-in-the-mysql-directory) but it is not working for me.

我是Ubuntu 12.04，MySQL版本是5.5.22-0ubuntu1

i am un Ubuntu 12.04 and MySQL version is 5.5.22-0ubuntu1

我已经以root用户身份登录MySQL，因此授予权限应该没问题:

I have logged into MySQL as root and so grants should all be okay:

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

我正在尝试将文本文件中的一些数据插入MySQL数据库，而LOAD_FILE函数似乎无法正常工作

I am trying to insert some data from a text file into a MySQL database and the LOAD_FILE function doesn't seem to work properly

我创建了一个测试文件，权限为777，并复制到安装的根目录(我尝试将owner/group更改为root:root和mysql:mysql，但仍然效果不佳):

I created a test file, permissions of 777 and copied to root of the install (I tried changing owner/group to root:root and mysql:mysql and still no good):

mysql> select load_file('/test.txt');
+------------------------+
| load_file('/test.txt') |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

但是，如果我尝试这样做:

But if I try this:

mysql> select load_file('/etc/hosts');

工作正常.如果我将测试文件复制到/etc，它仍然会失败.

It works fine. If I copy the test file into /etc it still fails.

以前有没有人见过这个，或者也许可以指出我另一种加载到数据库中的方法?

has anyone seen this before or can perhaps point me to another way to load into the database?

推荐答案

要使用load_file，必须满足以下条件(来自

To use load_file, the following conditions must be met (from the documentation):

1. 该文件必须位于服务器主机上
2. 您必须指定文件的完整路径名，并且必须具有FILE特权.
3. 该文件必须全部可读，并且其大小小于max_allowed_packet个字节.
4. 如果secure_file_priv系统变量设置为非空目录名称，则要加载的文件必须位于该目录中.

1. The file must be located on the server host
2. You must specify the full path name to the file, and you must have the FILE privilege.
3. The file must be readable by all and its size less than max_allowed_packet bytes.
4. 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.

如果文件包含要执行的SQL语句，则更简单的方法可能是将其通过管道输入:

If the file contains SQL statements that you want to execute, an easier approach might be to pipe it in:

mysql -u foo -p dbname < filename.sql

这篇关于LOAD_FILE()函数的替代方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！