SQL 出现语法错误，但我没有看到 [英] SQL giving a syntax error, yet I don't see one
问题描述
我编写了一个 PHP 脚本来将主题和帖子从旧论坛导入到新论坛中，我在 SQL 语句的第 2 行收到语法错误，但我不知道错误可能是什么.
I wrote a PHP script to import topics and posts into a new forum from an old one, I am getting a syntax error on line 2 of my SQL statement, yet I don't see what the error could be.
INSERT INTO `uh46v_chronoengine_forums_topics` (`id`,`forum_id`,`title`,`alias`,`user_id`,`published`,`locked`,`created`,`modified`,`hits`,`params`,`post_count`,`last_post`,`reported`,`has_attachments`,`announce`,`sticky`) VALUES (`33`,`2`,`DS4Windows rebranding`,`DS4Windows-rebranding`,`2`,`1`,`0`,`2015-02-04 22:10:57`,``,`0`,`{\"uid\":\"4ad6a17c-c7e0-4626-95ea-1a248caaf964\"}`,`24`,`540`,`0`,`0`,`0`,`0`);
INSERT INTO `uh46v_chronoengine_forums_posts` (`id`,`topic_id`,`forum_id`,`subject`,`text`,`user_id`,`created`,`modified`,`published`,`params`) VALUES (`172`,`33`,`2`,`DS4Windows rebranding`,`So it was suggested in out old forum that a rebranding was in order for us, mainly to differentiate ourselves from Jays distribution. With the building of forums and more traffic now coming to the site I believe splitting not only from the DS4Windows name, but also from the DSDCS brand would be a good idea sometime in the near future. Not being a very good "marketing guy" myself, im not quite sure what to rename the project. So I will open the floor on the subject and hope we have somebody in the community good with such things.\n\nGeneral guidelines:\nMust not violate any copyrights or trademarks\nKeep in mind this application may not alwayse be limited to DS4 -> X360 mapping\nPrefer the .com of the name be available`,`2`,`2015-02-04 22:10:57`,``,`1`,`{\"author_address\":\"96.58.100.87\"}`);
这会产生同样的错误
INSERT INTO `uh46v_chronoengine_forums_topics` (`id`,`forum_id`,`title`,`alias`,`user_id`,`published`,`locked`,`created`,`modified`,`hits`,`params`,`post_count`,`last_post`,`reported`,`has_attachments`,`announce`,`sticky`) VALUES ('33','2','DS4Windows rebranding','DS4Windows-rebranding','2','1','0','2015-02-04 22:10:57','','0','{\"uid\":\"0832fbee-506c-4fac-b2f6-eda324c54580\"}','24','540','0','0','0','0');
INSERT INTO `uh46v_chronoengine_forums_posts` (`id`,`topic_id`,`forum_id`,`subject`,`text`,`user_id`,`created`,`modified`,`published`,`params`) VALUES ('172','33','2','DS4Windows rebranding','So it was suggested in out old forum that a rebranding was in order for us, mainly to differentiate ourselves from Jays distribution. With the building of forums and more traffic now coming to the site I believe splitting not only from the DS4Windows name, but also from the DSDCS brand would be a good idea sometime in the near future. Not being a very good "marketing guy" myself, im not quite sure what to rename the project. So I will open the floor on the subject and hope we have somebody in the community good with such things.\n\nGeneral guidelines:\nMust not violate any copyrights or trademarks\nKeep in mind this application may not alwayse be limited to DS4 -> X360 mapping\nPrefer the .com of the name be available','2','2015-02-04 22:10:57','','1','{\"author_address\":\"96.58.100.87\"}');
错误:
您的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本以使用正确的语法'INSERT INTO uh46v_chronoengine_forums_posts 附近(id,topic_id,forum_id,`subje' 在第 2 行
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 'INSERT INTO
uh46v_chronoengine_forums_posts(id,topic_id,forum_id,`subje' at line 2
仅供参考但不太可能相关，因为错误是 MySQL 错误，这是我用来完成此操作的 PHP 脚本.
For reference but not likely related since the error is a MySQL one, here is the PHP script I am using to accomplish this.
<?PHP
function GUID()
{
if (function_exists('com_create_guid') === true)
{
return trim(com_create_guid(), '{}');
}
return sprintf('%04X%04X-%04X-%04X-%04X-%04X%04X%04X', mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(16384, 20479), mt_rand(32768, 49151), mt_rand(0, 65535), mt_rand(0, 65535), mt_rand(0, 65535));
}
include_once('configuration.php');
$config = new JConfig;
$servername = $config->host;
$username = $config->host;
$password = $config->host;
$sourceForumID = 7;
$destinationForumID = 2;
// Create connection
$db = new mysqli($config->host, $config->user, $config->password, $config->db);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
$topicsTableA = "phpbb_topics";
$topicsTableB = $config->dbprefix."chronoengine_forums_topics";
$postsTableA = "phpbb_posts";
$postsTableB = $config->dbprefix."chronoengine_forums_posts";
$topicsSQLA = <<<SQL
SELECT *
FROM `$topicsTableA`
WHERE forum_id = $sourceForumID
SQL;
if(!$topicsResultA = $db->query($topicsSQLA)){
die('There was an error running the query [' . $db->error . ']');
}
$topicsSQLB = "";
while($topicsRowA = $topicsResultA->fetch_assoc()){
$id = $topicsRowA["topic_id"];
$postsSQLA = <<<SQL
SELECT *
FROM `$postsTableA`
WHERE topic_id = $id
SQL;
if(!$postsResultA = $db->query($postsSQLA)){
die('There was an error running the query [' . $db->error . ']');
}
$id = $topicsRowA["topic_id"];
$forum_id = $destinationForumID;
$title = mysqli_real_escape_string($db,$topicsRowA["topic_title"]);
$alias = preg_replace("/[^A-Za-z0-9]/", '-', $topicsRowA["topic_title"]);
$user_id = $topicsRowA["topic_poster"];
$published = 1;
$locked = 0;
$created = (new DateTime("@".$topicsRowA["topic_time"]))->format('Y-m-d H:i:s');
$modified = null;
$hits = 0;
$params = mysqli_real_escape_string($db,'{"uid":"'.strtolower(GUID()).'"}');
$post_count = $postsResultA->num_rows;
$last_post = $topicsRowA["topic_last_post_id"];
$reported = 0;
$has_attachments = 0;
$announce = 0;
$sticky = 0;
$topicsSQLB .= "INSERT INTO `$topicsTableB` (`id`,`forum_id`,`title`,`alias`,`user_id`,`published`,`locked`,`created`,`modified`,`hits`,`params`,`post_count`,`last_post`,`reported`,`has_attachments`,`announce`,`sticky`) VALUES ('$id','$forum_id','$title','$alias','$user_id','$published','$locked','$created',NULL,'$hits','$params','$post_count','$last_post','$reported','$has_attachments','$announce','$sticky'); \n\r";
while($postsRowA = $postsResultA->fetch_assoc()){
$Tid = $postsRowA["post_id"];
$topic_id = $id;
$subject = mysqli_real_escape_string($db,$postsRowA["post_subject"]);
$text = mysqli_real_escape_string($db,$postsRowA["post_text"]);
$user_id = $postsRowA["poster_id"];
$created = (new DateTime("@".$postsRowA["post_time"]))->format('Y-m-d H:i:s');
$modified = null;
$published = 1;
$params = mysqli_real_escape_string($db,'{"author_address":"'.$postsRowA["poster_ip"].'"}');
$topicsSQLB .= "INSERT INTO `$postsTableB` (`id`,`topic_id`,`forum_id`,`subject`,`text`,`user_id`,`created`,`modified`,`published`,`params`) VALUES ('$Tid','$topic_id','$forum_id','$subject','$text','$user_id','$created',NULL,'$published','$params'); \n\r";
}
}
//if ($db->query($topicsSQLB) === true) {
// echo "New record created successfully";
//} else {
// echo "Error: " . $sql . "<br>" . $db->error . "<br>";
//}
echo $topicsSQLB;
推荐答案
PHP Mysqli 允许使用 multi_query() 函数.
PHP Mysqli permits multiple queries with the multi_query() function.
以下内容是在一般情况下添加到对话中，并避免在多个查询块在彼此之上运行时因无休止的不同步错误而造成的悲痛.或者一个多之后的非多.
The following is to add on to the conversation in general terms, and avoid grief from endless Out of Sync errors when blocks of multi queries are run on top of one another. Or a non-multi after a multi.
问题在执行 multi_query() 之后就开始了，如果没有清除结果集就继续下一个查询.错误将是底部标记为 Note1 的错误.但是在这个答案中避免了.
The trouble begins after the execution of multi_query() if one then proceeds to the next query without clearing the result set. The error would be the one denoted as Note1 at the bottom. But it is avoided in this Answer.
您的特定问题与 \r\n 或 \n\r 无关.作为这项工作的一部分，他们接受了测试，但被排除在外，以免将下一个人与他们不同的问题混淆.
Your particular problem had nothing to do with \r\n or \n\r. They were tested as part of this effort, but left out not to confuse the next person coming in with their problem, different.
<?php
//mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
mysqli_report(MYSQLI_REPORT_ALL);
error_reporting(E_ALL); // report all PHP errors
ini_set("display_errors", 1);
echo "start<br/>";
try {
$mysqli= new mysqli('hostname', 'dbuser', 'pwd', 'dbname');
if ($mysqli->connect_error) {
die('Connect Error (' . $mysqli->connect_errno . ') '
. $mysqli->connect_error);
}
echo "I am connected and feel happy.<br/>";
$query = "INSERT INTO `table1`(`thing`) values ('aaa')";
$mysqli->query($query);
// works fine
// Note the concat below
$query = "INSERT INTO `table1`(`thing`) values ('bbb1'); ";
$query .=$query; // double it up with concat (in other words two insert)
// we have a multi query so call it the right way:
$mysqli->multi_query($query);
// we need to clear the protocol to avoid Out of Sync errors
// http://stackoverflow.com/a/21754463
do {
$mysqli->use_result();
}while( $mysqli->more_results() && $mysqli->next_result() );
// if you remark out the above 3 lines,
// expect error message depicted in **** Note1 ****
// purpose of this next block is to show result sets are cleared
// from prior multi, and we can do another insert
// thus avoiding error 500 out of sync errors
$query = "INSERT INTO `table1`(`thing`) values ('ccc')";
$mysqli->query($query); // a single insert statement
// Finally, this shows that running a multi without a multi_query fcn call will bomb
$query = "INSERT INTO `table1`(`thing`) values ('explosion'); \r\n";
$query .=$query; // double it up with concat
$mysqli->query($query); // make a multi query explode by not calling multi_query (but rather query)
// The above line generated an error, error message below (**** Note2 ****)
$mysqli->close();
} catch (mysqli_sql_exception $e) {
throw $e;
}
?>
数据库结果:
select * from table1;
+----+-------+
| id | thing |
+----+-------+
| 1 | aaa |
| 2 | bbb1 |
| 3 | bbb1 |
| 4 | ccc |
+----+-------+
显示的源代码中提到了以下错误消息.第一个完全避免.第二个不是，它表明需要函数 multi_query()，而不是 query().
The following error messages are mentioned in the source code shown. The first one is completely avoided. The second one is not, and there to show that the function multi_query(), as opposed to query(), is required.
****** 注 1 ******
****** Note1 ******
致命错误:未捕获的异常 'mysqli_sql_exception' 带有消息'命令不同步；你现在不能运行这个命令C:\Apache24\htdocs\mi_insert_multi_query_test.php:36 堆栈跟踪:#0C:\Apache24\htdocs\mi_insert_multi_query_test.php(36):mysqli->query('INSERT INTO `ta...') #1 {main} 被抛出C:\Apache24\htdocs\mi_insert_multi_query_test.php 第 36 行
Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Commands out of sync; you can't run this command now' in C:\Apache24\htdocs\mi_insert_multi_query_test.php:36 Stack trace: #0 C:\Apache24\htdocs\mi_insert_multi_query_test.php(36): mysqli->query('INSERT INTO `ta...') #1 {main} thrown in C:\Apache24\htdocs\mi_insert_multi_query_test.php on line 36
******注2******
****** Note2 ******
致命错误:未捕获的异常 'mysqli_sql_exception' 带有消息'你的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本以使用正确的语法在第 2 行'INSERT INTO table1(thing) values ('explosion')' 附近在 C:\Apache24\htdocs\mi_insert_multi_query_test.php:41 堆栈跟踪中:#0 C:\Apache24\htdocs\mi_insert_multi_query_test.php(41): mysqli->query('INSERT INTO `ta...') #1 {main} 被抛出C:\Apache24\htdocs\mi_insert_multi_query_test.php 第 41 行选择 *来自表1;
Fatal error: Uncaught exception 'mysqli_sql_exception' with message '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 'INSERT INTO
table1(thing) values ('explosion')' at line 2' in C:\Apache24\htdocs\mi_insert_multi_query_test.php:41 Stack trace:#0C:\Apache24\htdocs\mi_insert_multi_query_test.php(41): mysqli->query('INSERT INTO `ta...') #1 {main} thrown in C:\Apache24\htdocs\mi_insert_multi_query_test.php on line 41 select * from table1;
这篇关于SQL 出现语法错误，但我没有看到的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
