MySQL加载数据:这个命令在准备好的语句协议中还不支持

I'm trying to write a MySQL script to import data into a table for my Linux server. Here is the script named update.sql:

我正在编写一个MySQL脚本，将数据导入到我的Linux服务器的表中。下面是名为update.sql的脚本:

SET @query = CONCAT("LOAD DATA LOCAL INFILE '", @spaceName, "' INTO TABLE tmp FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"); 
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

And also, I write a bash script named main.sh:

此外，我还编写了一个名为main.sh的bash脚本:

mysql -h "localhost" -u "root" "-pmypassword" "mydb" -e "set @spaceName=\"$1\";source update.sql;"

Then I execute ./main.sh France.list. The France.list is the data file that I'm trying to import into my database.

然后我主要执行。/。sh France.list。法国。list是我要导入到数据库中的数据文件。

However I get an error:

但是我得到了一个错误:

ERROR 1295 (HY000) at line 2 in file: 'update.sql': This command is not supported in the prepared statement protocol yet

错误1295 (HY000)在文件第2行:'更新。sql':这个命令还不支持准备语句协议

I've found this question:
MySQL - Load Data Infile with variable path

我发现了这个问题:MySQL -加载数据文件，带有可变路径

So, does it mean that there is no way to pass arguments to LOAD DATA query?

那么，这是否意味着无法通过传递参数来加载数据查询呢?

1 个解决方案

#1

You can't use PREPARE to run LOAD DATA INFILE.

不能使用“准备”来在文件中运行“加载数据”。

The list of statements that you can run with PREPARE are documented in this page: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html under the subheading "SQL Syntax Allowed in Prepared Statements". Note this list may be different in earlier versions of MySQL.

可以使用PREPARE运行的语句列表在这个页面中有文档说明:https://dev.mysql.com/doc/refman/5.7/en/sql语法准备状态。注意，在MySQL的早期版本中，这个列表可能有所不同。

Because you can't use PREPARE, you can't do the method you're using by setting a variable and making a dynamic SQL statement.

因为您不能使用PREPARE，所以您不能通过设置变量和创建动态SQL语句来完成您正在使用的方法。

But you can run LOAD DATA INFILE without using PREPARE. You have to interpolate the filename into the statement using shell variable substitution and then run it as a direct SQL statement.

但是，您可以在不使用PREPARE的情况下运行载入数据的文件。您必须使用shell变量替换将文件名插入到语句中，然后以直接SQL语句的形式运行它。

Your update.sql file might look like this:

你的更新。sql文件可能是这样的:

LOAD DATA LOCAL INFILE '%spacename%' INTO TABLE tmp 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Then you can substitute your shell variable into the file and run the result this way:

然后，您可以将shell变量替换为文件，并以这种方式运行结果:

sed s/%spacename%/$1/ update.sql | 
  mysql -h "localhost" -u "root" "-pmypassword" "mydb"

Another simpler way is to use mysqlimport, except this requires that the input filename be the same as your table name. You can either rename your input file to match the table you want to load into (which you call tmp), or else create a symbolic link:

另一种更简单的方法是使用mysqlimport，但这要求输入文件名与表名相同。您可以重命名输入文件以匹配要加载到的表(您称之为tmp)，或者创建一个符号链接:

ln -s $1 /tmp/tmp.list
mysqlimport --local -h "localhost" -u "root" "-pmypassword" "mydb" /tmp/tmp.list
rm -f /tmp/tmp.list

The ".list" extension is ignored by mysqlimport, so you can use any file extension, or none.

”。列表“扩展名被mysqlimport忽略，因此您可以使用任何文件扩展名，也可以不使用任何文件扩展名。

#1