mysql - "DROP INDEX"语句的 RMySQL 语法错误

Question

我似乎无法弄清楚这里出了什么问题。

我试图在 R 中运行 sql 查询，但出现以下错误:

Error in .local(conn, statement, ...) :    could not run statement: 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 'DROP INDEX `PRIMARY` ON temporary_table' at line 2

以下是我的代码:

library(RMySQL)

con <- dbConnect(MySQL(),
                 user="XXX", password="XXX",
                 dbname="test", host="localhost")

query <- "CREATE TEMPORARY TABLE temporary_table LIKE test.helloworld;
         DROP INDEX `PRIMARY` ON temporary_table;"

rs <- dbSendQuery(con, query) #Where the error occurs

我在 mySQL 中运行了完全相同的查询，它完全正常。

谁能指出我做错了什么？

非常感谢!

Answer 1

分两部分执行查询。第一部分创建表，第二部分删除索引。对于前-

query <- "CREATE TEMPORARY TABLE temporary_table LIKE test.helloworld;"
rs <- dbSendQuery(con, query) #Where the error occurs

query <- "DROP INDEX `PRIMARY` ON temporary_table;"
rs <- dbSendQuery(con, query) #Where the error occurs

我已经在 php 中进行了测试。这在 php 中有同样的问题。运行良好。

<?php
$mysqli = new mysqli("localhost", "root", "root", "test");
$result = $mysqli->query("CREATE TEMPORARY TABLE `temporary_table` LIKE t;");
printf("Errormessage: %s\n", $mysqli->error);
$result = $mysqli->query(" DROP INDEX `PRIMARY` ON temporary_table;");
#$result = $mysqli->query("DROP table temporary_table;");
printf("Errormessage: %s\n", $mysqli->error);
?>
root@smart:/var/www# php test.php
Errormessage: 
Errormessage:

当我同时运行两个查询时，就会出现您所说的错误。

<?php
// mysqli
$mysqli = new mysqli("localhost", "root", "root", "test");
$result = $mysqli->query("CREATE TEMPORARY TABLE `temporary_table` LIKE t;DROP INDEX `PRIMARY` ON temporary_table;");
printf("Errormessage: %s\n", $mysqli->error);
?>
root@smart:/var/www# php test.php
Errormessage: 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 'DROP INDEX `PRIMARY` ON temporary_table' at line 1