mysql - 使用 LOCAL_INFILE 设置从 MySQL 在 Rails 中加载数据时出错

Question

当我尝试加载 .cvs 文件时出现此错误

Mysql2::Error: The used command is not allowed with this MySQL version: load data local infile 'db/fixtures/devel_countries.csv'

我检查了 mysql 服务器配置并设置了 LOCAL_INFILE = 'ON'

这是我的配置中的一些数据:

• MySQL 服务器版本:5.5.16 MySQL Community Server (GPL)
• Ruby:1.9.2p136(2010-12-25 修订版 30365)[x86_64-darwin10.8.0]
• rails :3.1.1

我用来执行这个的命令是:

ActiveRecord::Base.connection.execute( "load data local infile 'db/fixtures/devel_regions.csv' into table regions fields terminated by ',' enclosed by '\"' lines terminated by '\n' (id, iso_code, name, country_id, created_at, updated_at);")

有什么问题吗？

Answer 1

查看您的 MySQL 命令:

load data local infile 'db/fixtures/devel_regions.csv' 
into table regions 
fields terminated by ',' enclosed by '\"' 
lines terminated by '\n' 
(id, iso_code, name, country_id, created_at, updated_at);

总的来说，这个命令对我来说是正确的。

我注意到您将 LOCAL 命令作为其中的一部分。根据文档:

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

可能是您尝试运行的应用程序无法处理命令的 local 部分，尽管看起来您正在正确使用它。

我尝试的第一件事是从数据库控制台运行命令并查看其工作原理。您可以通过运行 bundle exec rails db 获得它。