php MySQL SQL 语法错误；检查与您的 MySQL 服务器版本相对应的手册以了解要使用的正确语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25234777/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Error in SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
提问by 0000101010
I am trying to insert a sample blog post into my 'posts' table in MySQL (using PHP) however I receive a syntax error whenever a large character post is submitted. If I submit content of say 20 characters it works but something like 500 characters will throw the following error:
我正在尝试将示例博客文章插入 MySQL 中的“文章”表（使用 PHP），但是每当提交大字符文章时，我都会收到语法错误。如果我提交 20 个字符的内容，它可以工作，但 500 个字符之类的内容会引发以下错误：
Error: 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 ''uid', 'username', 'p_date', 'title', 'content') VALUES('1','Mark Twain', '2014-' at line 1
错误：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 ''uid', 'username', 'p_date', 'title', 'content') VALUES('1','Mark Twain', ' 2014-' 第 1 行
The 'content' is to be inserted into the database via a varchar(1000) variable. The table is defined in mysql as:
“内容”将通过 varchar(1000) 变量插入到数据库中。该表在mysql中定义为：
CREATE TABLE posts
(
pid int NOT NULL AUTO_INCREMENT,
uid int NOT NULL,
username varchar(100) NOT NULL,
p_date date NOT NULL,
title varchar(225) NOT NULL,
content varchar(10000) NOT NULL,
PRIMARY KEY(pid),
FOREIGN KEY(uid) REFERENCES users(uid)
);
The actual content I am trying to submit is this:
我试图提交的实际内容是这样的：
Secondly, these missionaries would gradually, and without creating suspicion or exciting alarm, introduce a rudimentary cleanliness among the nobility, and from them it would work down to the people, if the priests could be kept quiet. This would undermine the Church. I mean would be a step toward that. Next, education -- next, freedom -- and then she would begin to crumble. It being my conviction that any Established Church is an established crime, an established slave-pen, I had no scruples, but was willing to assail it in any way or with any weapon that promised to hurt it. Why, in my own former day -- in remote centuries not yet stirring in the womb of time -- there were old Englishmen who imagined that they had been born in a free country: a "free" country with the Corporation Act and the Test still in force in it -- timbers propped against men's liberties and dishonored consciences to shore up an Established Anachronism with.
其次，这些传教士会逐渐地，在不引起怀疑或令人兴奋的警报的情况下，在贵族中引入一种基本的清洁，如果神父可以保持安静，它就会从他们那里影响到人民。这会破坏教会。我的意思是朝着那个方向迈出了一步。接下来是教育——接下来是自由——然后她就会开始崩溃。我坚信任何既定的教会都是既定的犯罪，一个既定的奴隶圈，我没有任何顾忌，但愿意以任何方式或任何承诺伤害它的武器攻击它。为什么，在我以前的日子——在遥远的几个世纪里，还没有在时间的子宫里生机勃勃——有英国老人们想象他们出生在一个自由的国家：一个“自由的”
The insert statement for this is the following:
插入语句如下：
$sql = "INSERT INTO posts ('uid', 'username', 'p_date', 'title', 'content') VALUES('$uid','$uname', '$date', '$title', '$content')";
if(!mysql_query($sql,$con)){
echo "Oops! Something went wrong during the posting process. Please try again. ";
die('Error: ' . mysql_error($con));
header('Refresh: 1; URL=postingform.php');
}else{
// Now return the user to their post page
header('Refresh: 0; URL=postlist.php?uid='.$uid.'');
}
For some reason it is error-ing out during the INSERT process. The one thing strange I notice is that the date is cut off in the error. To call the date I am using. $date = date("Y-m-d");
由于某种原因，它在 INSERT 过程中出错。我注意到的一件奇怪的事情是日期在错误中被截断了。调用我正在使用的日期。$date = date("Ymd");
I have used this same syntax before without issues.
我以前使用过相同的语法，没有问题。
****Edit
****编辑
A few posters have pointed out that there are single quotations in my INSERT column statements. I have changed these to back tics and completely removed them but the error still results.
一些发帖者指出我的 INSERT 列语句中有单引号。我已将这些更改为支持抽动并完全删除它们，但仍然会导致错误。
New Error:
新错误：
Error: 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 's Court', 'Secondly, these missionaries would gradually, and without creating su' at line 1
错误：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在“法院”附近使用的正确语法，“其次，这些传教士会逐渐地，并且不会在第 1 行创建 su”
There is something still wrong with my insert syntax but everything I am reading says it should be correct.
我的插入语法仍然有问题，但我正在阅读的所有内容都表明它应该是正确的。
$sql = "INSERT INTO posts (`uid`, `username`, `p_date`, `title`, `content`) VALUES('$uid','$uname', '$p_date', '$title', '$content')";
回答by Funk Forty Niner
Remove all the quotes in (for your columns)
删除（对于您的列）中的所有引号
('uid', 'username', 'p_date', 'title', 'content')
Those aren't the correct column identifiers
这些不是正确的列标识符
use
用
(uid, username, p_date, title, content)
or use backticks.
或使用反引号。
(`uid`, `username`, `p_date`, `title`, `content`)
Howeverand as a quick FYI, backticks are mostly used for reserved keywords, or if a table/column contains spaces, hyphens.
但是，作为快速参考，反引号主要用于保留关键字，或者如果表/列包含空格、连字符。
The error message was letting you know here
错误消息让您知道这里
check the manual that corresponds to your MySQL server version for the right syntax to use near ''uid',
^--? right there
检查与您的 MySQL 服务器版本相对应的手册，了解在 ''uid' 附近使用的正确语法，
^--? 在那里
Notice the quote just before 'uid'? That's where the problem starts.
注意到前面的引用了'uid'吗？这就是问题开始的地方。
Edit:
编辑：
Try the following using prepared statements and replace xxxwith your own credentials.
使用准备好的语句尝试以下操作并替换xxx为您自己的凭据。
This should take care of the quotes issue from your input values.
这应该处理输入值中的引号问题。
You will need to add the variables according to your inputs.
您将需要根据您的输入添加变量。
<?php
$DB_HOST = "xxx";
$DB_NAME = "xxx";
$DB_USER = "xxx";
$DB_PASS = "xxx";
$conn = new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME);
if($conn->connect_errno > 0) {
die('Connection failed [' . $conn->connect_error . ']');
}
$uid = ""; // replace with proper value
$uname = ""; // replace with proper value
$date = ""; // replace with proper value
$title = ""; // replace with proper value
$content = ""; // replace with proper value
$stmt = $conn->prepare("INSERT INTO posts (`uid`, `username`, `p_date`, `title`, `content`) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param('sssss', $uid, $uname, $date, $title, $content);
if (!$stmt->execute()) {
echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}
else{
echo "Success";
}
$stmt->close(); // Statement
$conn->close(); // MySQLi
Footnotes:
脚注：
In order to allow single and/or double quotes, based yourself on the following, while using the stripslashes()function.
为了允许单引号和/或双引号，请在使用该stripslashes()函数时基于以下内容。
$content = stripslashes($_POST['content']);
This will enter in DB properly:Bob's sister was here today and said: "Bob, what lovely hair you have!".
这将正确输入数据库：Bob's sister was here today and said: "Bob, what lovely hair you have!".
