MySQL加载数据：尚未在准备好的语句协议中支持此命令 [英] MySQL load data: This command is not supported in the prepared statement protocol yet

问题描述

我正在尝试编写MySQL脚本以将数据导入到我的Linux服务器的表中。以下是名为 update.sql 的脚本：

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:

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;

另外，我写了一个名为 main.sh ：


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

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

然后我执行 ./ main.sh France.list 。 France.list 是我要导入数据库的数据文件。

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

但是我收到错误：

However I get an error:

文件中第2行的错误1295（HY000）：'update.sql'：此命令尚未在准备好的声明协议中支持

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

我发现了这个问题：

MySQL - 使用可变路径加载数据Infile

那么，是否意味着无法将参数传递给 LOAD DATA 查询？

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

推荐答案

您不能使用 PREPARE 来运行 LOAD DATA INFILE 。

此页面中记录了可以使用 PREPARE 运行的语句列表： https://dev.mysql.com/doc/refman/5.7/ en / sql-syntax-prepared-statements.html 在准备语句中允许的SQL语法小标题下。请注意，此列表在早期版本的MySQL中可能有所不同。

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 ，你可以通过设置变量并生成动态SQL语句来执行您正在使用的方法。

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.

但是您可以运行 LOAD DATA INFILE 而不使用 PREPARE 。您必须使用shell变量替换将文件名插入到语句中，然后将其作为直接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.

您的update.sql文件可能如下所示：

Your update.sql file might look like this:

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

然后你可以将你的shell变量替换成文件并以这种方式运行结果：

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

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

另一种更简单的方法是使用 mysqlimport ，除非此要求输入文件名与表名相同。您可以重命名输入文件以匹配要加载的表（您调用 tmp ），或者创建符号链接：

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:

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

mysqlimport会忽略.list扩展名，因此您可以使用任何文件扩展名，也可以不使用。

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

这篇关于MySQL加载数据：尚未在准备好的语句协议中支持此命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！