无法使用LOAD DATA INFILE将数据加载到MySQL数据库 [英] Cannot load data to MySQL database with LOAD DATA INFILE

问题描述

我正在Ubuntu 12.04中使用LAMP.我在MySQL(myserver @ localhost)中创建了一个新用户，并将数据库上的ALL授予了该用户.有一个文本文件，其权限设置为所有人读取.但是，当我尝试将数据从该文本文件加载到数据库时，它显示用户'myserver'@'localhost'的访问被拒绝(使用密码:是)"

I am using LAMP in Ubuntu 12.04. I created a new user in MySQL (myserver@localhost) and granted ALL on a database to that user. There is a text file the permission of which is set to read for everyone. But when I try to load the data from that text file to the database, it says "Access denied for user 'myserver'@'localhost' (using password: YES)"

我使用的查询是:

LOAD DATA INFILE "~/text/member_info.txt" INTO TABLE member FIELDS TERMINATED BY '|';

我可以想到在PHP中有一些循环的解决方法，但是为什么"LOAD DATA INFILE"不起作用?

I can think of a workaround with some loops in PHP but why doesn't the 'LOAD DATA INFILE' work?

谢谢.

推荐答案

这是由于您在Ubuntu LTS 12.04中使用的MYSQL 5.5版本:

This is due the MYSQL 5.5 version you are using in Ubuntu LTS 12.04 : mysql doc

来自mysql doc评论:

From mysql doc comment :

您可以在不使用PHP的最新版本的情况下使用LOAD DATA LOCAL 重新编译PHP.

You can use LOAD DATA LOCAL with recent versions of PHP without recompiling PHP.

将128(CLIENT_LOCAL_FILES常量的值)作为 mysql_connect()的第五个参数启用LOAD DATA LOCAL 客户端.

Passing 128 (the value of the CLIENT_LOCAL_FILES constant) as the fifth parameter to mysql_connect () enables LOAD DATA LOCAL on the client side.

示例:$ dbh = mysql_connect($ server，$ user，$ pass，false，128);

Example: $dbh = mysql_connect($server, $user, $pass, false, 128);

对于PHP 4.3及更高版本.

For PHP 4.3 and above.

但是实际上，mysql_connect()已经过时了，我找不到使用mysqli的解决方案.

But in fact, mysql_connect() is obsolete, and I can't find a solution using mysqli.

这就是为什么我最终使用这个来解决的原因问题.

That's why I finally use this who solve the problem.

grant file on *.* to mydatabase@localhost identified by 'myuser';

现在$ mysqli-> query("LOAD DATA INFILE'".$ proxy_file_name_path.'IGNORE INTO TABLE mytable(myfield)"

Now $mysqli->query("LOAD DATA INFILE '".$proxy_file_name_path."' IGNORE INTO TABLE mytable (myfield)"

这篇关于无法使用LOAD DATA INFILE将数据加载到MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！