MySQL：启用 LOAD DATA LOCAL INFILE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10762239/
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
MySQL: Enable LOAD DATA LOCAL INFILE
提问by Krt_Malta
I'm running Mysql 5.5 on Ubuntu 12 LTS. How should I enable LOAD DATA LOCAL INFILE in my.cnf?
我在 Ubuntu 12 LTS 上运行 Mysql 5.5。我应该如何在 my.cnf 中启用 LOAD DATA LOCAL INFILE？
I've tried adding local-infile in my config at various places but I'm still getting the "The used command is not allowed with this MySQL version"
我已经尝试在我的配置中的各个地方添加 local-infile，但我仍然收到“此 MySQL 版本不允许使用的命令”
回答by dAm2K
From the MySQL 5.5 manual page:
从 MySQL 5.5 手册页：
LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.
LOCAL 仅在您的服务器和客户端都已配置为允许时才有效。例如，如果 mysqld 以 --local-infile=0 启动，则 LOCAL 不起作用。请参见第 6.1.6 节，“LOAD DATA LOCAL 的安全问题”。
You should set the option:
您应该设置选项：
local-infile=1
into your [mysql]entry of my.cnf file or call mysql client with the --local-infileoption:
进入my.cnf 文件的[mysql]条目或使用--local-infile选项调用 mysql 客户端：
mysql --local-infile -uroot -pyourpwd yourdbname
You have to be sure that the same parameter is defined into your [mysqld]section too to enable the "local infile" feature server side.
您必须确保在[mysqld]部分中也定义了相同的参数以启用“本地 infile”功能服务器端。
It's a security restriction.
这是一个安全限制。
回答by hlosukwakha
The my.cnf file you should edit is the /etc/mysql/my.cnffile. Just:
您应该编辑my.cnf文件是/etc/mysql/my.cnf文件。只是：
sudo nano /etc/mysql/my.cnf
Then add:
然后加：
[mysqld]
local-infile
[mysql]
local-infile
The headers [mysqld]and [mysql]are already given, just locate them in the file and add local-infileunderneath each of them.
头文件[mysqld]和[mysql]已经给出，只需在文件中找到它们并在每个文件下添加local-infile。
It works for me on MySQL 5.5 on Ubuntu 12.04 LTS.
它适用于我在 Ubuntu 12.04 LTS 上的 MySQL 5.5。
回答by BeniSK
Replace the driver php5-mysql by the native driver
用原生驱动替换驱动php5-mysql
On debian
在 debian 上
apt-get install php5-mysqlnd
回答by Oleksiy Muzalyev
I solved this problem on MySQL 8.0.11 with the mysql terminal command:
我在 MySQL 8.0.11 上使用 mysql 终端命令解决了这个问题：
SET GLOBAL local_infile = true;
I mean I logged in first with the usual:
我的意思是我先用通常的方式登录：
mysql -u user -p*
After that you can see the status with the command:
之后，您可以使用以下命令查看状态：
SHOW GLOBAL VARIABLES LIKE 'local_infile';
It should be ON. I will not be writing about security issued with loading local files into database here.
它应该是ON。我不会在这里写关于将本地文件加载到数据库中所发出的安全性。
回答by tony gil
in case your flavor of mysql on ubuntu does NOT under any circumstances work and you still get the 1148 error, you can run the load data infilecommand via command line
如果您在 ubuntu 上的 mysql 风格在任何情况下都不起作用并且您仍然收到 1148 错误，您可以load data infile通过命令行运行该命令
open a terminal window
打开终端窗口
run mysql -u YOURUSERNAME -p --local-infile YOURDBNAME
跑 mysql -u YOURUSERNAME -p --local-infile YOURDBNAME
you will be requested to insert mysqluser password
您将被要求插入 mysqluser 密码
you will be running MySQLMonitor and your command prompt will be mysql>
您将运行 MySQLMonitor 并且您的命令提示符将是 mysql>
run your load data infilecommand (dont forget to end with a semicolon ;)
运行您的load data infile命令（不要忘记以分号结尾;）
like this:
像这样：
load data local infile '/home/tony/Desktop/2013Mini.csv' into table Reading_Table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
回答by Jagadeesh Pulamarasetti
See below image...
见下图...
I've added --local-infile=1to normal mysql command mysql -u root -p
我已经添加--local-infile=1到普通的 mysql 命令 mysql -u root -p
So total line would be :
所以总行将是：
mysql --local-infile=1 -u root -p
mysql --local-infile=1 -u root -p
回答by brfox
Also, for other readers, if you are trying to do this in Django AND your server allows local_infile (you can check by typing SHOW VARIABLES via a mysql client) then you can add this to your settings.pyfile (since python MySQLdb doesn't by default read the .my.cnf file):
此外，对于其他读者，如果您尝试在 Django 中执行此操作，并且您的服务器允许 local_infile（您可以通过 mysql 客户端键入 SHOW VARIABLES 进行检查），那么您可以将其添加到您的settings.py文件中（因为 python MySQLdb 没有） t 默认读取 .my.cnf 文件）：
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'mydb',
'USER': 'myname',
'PASSWORD': 'mypass',
'HOST': 'myserver',
'PORT': '3306',
'OPTIONS' : {
'local_infile':1,
},
}
}
回答by sieppl
You have to take care how you establish your mysqli connection. Full credit for this solution goes to Jorge Albarenque, source
您必须注意如何建立 mysqli 连接。此解决方案的全部功劳归功于 Jorge Albarenque，来源
In order to fix it I had to:
为了修复它，我不得不：
- Add local-infile=1 to the [mysqld] and [mysql] sections of my.cnf (as explained in the comments above)
- Use mysqli_real_connect function (PHP documentation).
- 将 local-infile=1 添加到 my.cnf 的 [mysqld] 和 [mysql] 部分（如上面的评论中所述）
- 使用 mysqli_real_connect 函数（PHP 文档）。
The catch is that with that function you can explicitly enable the support for LOAD DATA LOCAL INFILE. For example (procedural style):
问题在于，使用该函数，您可以显式启用对 LOAD DATA LOCAL INFILE 的支持。例如（程序风格）：
$link = mysqli_init();
mysqli_options($link, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($link, $host, $username, $password, $database);
or object oriented
或面向对象
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_LOCAL_INFILE, true);
$mysqli->real_connect($host, $username, $password, $database);
回答by wingoo
if your csv file located samewith db, you need to remove LOCALin LOAD DATA INFILE, or you will get the error
如果您的CSV文件位于同一使用DB，您需要删除本地的LOAD DATA INFILE，否则你会得到错误
The used command is not allowed with this MySQL version
此 MySQL 版本不允许使用所使用的命令
回答by Schalk
Another way is to use the mysqlimportclient program.
另一种方法是使用mysqlimport客户端程序。
You invoke it as follows:
您可以按如下方式调用它：
mysqlimport -uTheUsername -pThePassword --local yourDatabaseName tableName.txt
This generates a LOAD DATAstatement which loads tableName.txtinto the tableNametable.
这将生成一个LOAD DATA加载tableName.txt到tableName表中的语句。
Keep in mind the following:
请记住以下几点：
mysqlimportdetermines the table name from the file you provide; using all text from the start of the file name up to the first period as the table name. So, if you wish to load several files to the same table you could distinguish them like tableName.1.txt, tableName.2.txt,..., etc, for example.
mysqlimport从您提供的文件中确定表名；使用从文件名开头到第一个句点的所有文本作为表名。因此，如果您希望将多个文件加载到同一个表中，您可以区分它们，例如tableName.1.txt, tableName.2.txt,... 等。
