为什么MySql多次查询导致语法错误，而单独运行成功? [英] Why do MySql multiple queries cause syntax error, while separately work successfully?

问题描述

我正在尝试使用以下代码从 csv 加载数据.

$loadsql = 'delete from tablecreate where table_name=\''.$tablename.'\';加载数据文件\'/wamp/www/mytools/tablecreate/tables/'.$tablename.'.csv\'INTO TABLE 表创建以 \',\' 结尾的字段由 \'\\r\\n\' 终止的行忽略 2 行;';$result = mysqli_query($con, $loadsql);如果(！$结果){echo "抱歉！！数据无法加载！".mysqli_error($con)."<br>";echo "你的 sql 正在跟踪<br>".$loadsql;}别的 {echo "
"."数据加载成功."."
";}

当我在 phpmyadmin 上运行它时，查询可以正常工作.但是当我在浏览器中运行上面的php代码时，输​​出如下:

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行的LOAD DATA INFILE '/wamp/www/mytools/tablecreate/tables/subjects.csv' INTO TABL'附近使用的正确语法

你的sql正在跟踪

delete from tablecreate where table_name='subjects';加载数据文件'/wamp/www/mytools/tablecreate/tables/subjects.csv'INTO TABLE tablecreate FIELDS TERMINATED BY ','由 '\r\n' 终止的行忽略 2 行；

<块引用>

我添加上面这行只是为了调试它并查看$loadsql的实际内容，无法理解为什么在phpmyadmin中复制粘贴以下sql可以完美工作.

更新

尝试通过注释掉其中一个来分别执行这两个语句.一次一个，它们都可以正常工作，问题是它们不能一起工作.

只是一个想法

在多个查询中，如果一个成功而下一个失败，那么 $result 会返回什么?

解决方案

要在一次调用中执行多个查询，请使用 mysqli_multi_query().虽然从文档中并不完全清楚，但 mysqli_query() 一次只能执行一个查询.

I'm trying to load data from a csv with the following code.

$loadsql = 'delete from tablecreate where table_name=\''.$tablename.'\';
LOAD DATA INFILE \'/wamp/www/mytools/tablecreate/tables/'.$tablename.'.csv\' 
INTO TABLE tablecreate 
FIELDS TERMINATED BY \',\' 
LINES TERMINATED BY \'\\r\\n\' 
IGNORE 2 LINES;';

$result = mysqli_query($con, $loadsql);
if (!$result) { 
    echo "sorry!! data could not be loaded!".mysqli_error($con)."<br>" ;
    echo "your sql is following<br>".$loadsql;
}

else {
    echo "<br>"."data loaded successfully."."<br>" ;
     }

When I run this on phpmyadmin, the query works correctly. But when I run the above php code in the browser, the output is as follows:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOAD DATA INFILE '/wamp/www/mytools/tablecreate/tables/subjects.csv' INTO TABL' at line 1

your sql is following

delete from tablecreate where table_name='subjects';
LOAD DATA INFILE '/wamp/www/mytools/tablecreate/tables/subjects.csv' 
INTO TABLE tablecreate FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n' IGNORE 2 LINES;

I added the above line just to debug it and to see the actual contents of $loadsql, Can't understand why a copy paste of the following sql in phpmyadmin works perfectly.

UPDATE

Tried executing the two statements separately, by commenting out either of them. one at a time, both of them work fine, the problem is they don't work together.

Just a thought

in multiple queries if one is successful and the next fails, what would $result return then?

解决方案

To execute multiple queries in a single call use mysqli_multi_query(). Although it is not entirely clear from the documentation, but mysqli_query() can only execute a single query at a time.

这篇关于为什么MySql多次查询导致语法错误，而单独运行成功?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！