最近开发中须将csv文件导入到mysql中,听说用load data local infile导入效率较高,所以放弃使用fgetcsv等方法,而且这个方法不用我去open这个csv文件。但是按照用法在phpstorm+wamp环境中试了半天都不行,之前怀疑我的参数写错了,又排除了字段匹配、字符编码兼容等问题,
我的sql语句如下:
load data local infile $csv IGNORE into table $table CHARACTER SET utf8 fields terminated by ',' optionally enclosed by '\"' escaped by ','lines terminated by '\r\n' ignore 1 lines ;
在phpstorm中始终无法调试通过,奇怪的是当我换成phpmyadmin中用SQL功能测试,或者mysql>控制台测试,该语句都能成功执行。查看了很多网上的资料,仍然没有找到答案。
最后看到一个国外的论坛,按照其办法改进了才搞定了。期间被国内某些帖子误导了一下走了些弯路,现在说下解决办法:
1.在my.ini文件中,或者my.conf中在[mysql]和[mysqld]两个地方加上local-infile=1,如下:
[mysql] local-infile=1 [mysqld] local-infile=1
2.在程序中进行如下配置:重启服务器
我采用了面向对象的写法:$conn = mysqli_init(); mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true); mysqli_real_connect($conn,server,user,code,database);
$this->mysqli = new mysqli($this->host, $this->user, $this->password); $result = $this->mysqli->set_opt(MYSQLI_OPT_LOCAL_INFILE, true); $result = $this->mysqli->real_connect($this->host,$this->user,$this->password,$this->database); if($this->mysqli->connect_error) { die("Connect Server Failed:".$this->mysqli->error); }3.文件的路径写法phpstorm和SQL控制台和phpmyadmin有点区别,不能使用‘\’或者双斜杠‘\\’,而只能用‘/’,例如:
load data local infile 'C:/wamp64/Info.csv' REPLACE into table info12345 fields terminated by ',' optionally enclosed by '\"' escaped by '\"' lines terminated by '\r\n' ignore 1 lines"
ok,就这样搞定了。希望对也遇到这个问题的朋友有帮助
参考贴:
https://stackoverflow.com/questions/16739757/load-data-local-infile-command-not-allowed