blocks|key|157568|text|问题所在|type|unstyled|depth|inlineStyleRanges|entityRanges|data|157569|在MySQL中，某些单词，如SELECT、INSERT、DELETE等都是保留字。因为它们有特殊的含义，所以每当您将它们用作表名、列名或其他类型的标识符时，MySQL都会将其视为语法错误-除非您用反引号将标识符括起来。|offset|length|style|CODE|157570|如官方文件中所述，在部分(增加了重点)：|157571|157572|+MySQL中的某些对象，包括数据库、表、索引、列、别名、视图、存储过程、分区、表空间和其他对象名称，都称为。|blockquote|157573|157574|..。|157575|如果标识符包含特殊字符或是保留字，则无论何时引用它，都必须将其引起来。|BOLD|157576|157577|标识符引号字符是反引号+("`")：|157578|157579|关键字和保留字的完整列表可以在一节中找到。在该页中，后跟"(R)“的单词是保留字。下面列出了一些保留字，包括许多可能导致此问题的保留字。|157580|157581|ADD|unordered-list-item|157582|AND|157583|BEFORE|157584|BY|157585|CALL|157586|CASE|157587|CONDITION|157588|DELETE|157589|DESC|157590|DESCRIBE|157591|FROM|157592|GROUP|157593|IN|157594|INDEX|157595|INSERT|157596|INTERVAL|157597|IS|157598|KEY|157599|LIKE|157600|LIMIT|157601|LONG|157602|MATCH|157603|NOT|157604|OPTION|157605|OR|157606|ORDER|157607|PARTITION|157608|RANK|157609|REFERENCES|157610|SELECT|157611|TABLE|157612|TO|157613|UPDATE|157614|WHERE|157615|157616|解决方案|157617|您有两个选择。|157618|1.不要使用保留字作为标识符|157619|最简单的解决方案是避免使用保留字作为标识符。您可能会为您的列找到另一个合理的名称，而不是保留字。|157620|这样做有几个优点：|157621|157622|它消除了您或其他使用数据库的开发人员由于忘记或不知道特定标识符是保留字而意外写入语法错误的可能性。MySQL中有许多保留字，大多数开发人员不太可能知道所有这些保留字。通过从一开始就不使用这些词，你就避免了给自己或未来的开发人员留下陷阱。|157623|157624|在不同的方言中，引用标识符的方式是不同的。默认情况下，MySQL使用反引号来引用标识符，而符合ANSI的SQL+(以及ANSI+SQL模式中的MySQL，如here所指出的)使用双引号来引用标识符。因此，用反引号将标识符引起来的查询不太容易移植到其他SQL方言。|157625|纯粹为了减少将来出错的风险，这通常是一种比使用反引号引用标识符更明智的做法。|157626|2.使用反引号|157627|如果无法重命名表或列，请按照前面的引号中的说明，将有问题的标识符括在反引号(`)中。|157628|演示用法的示例(取自10.3+Keywords+and+Reserved+Words)：|157629|SQL+TABLE+interval+(begin+INT，end+INT)；ERROR+1064+(42000)：|157630|语法中存在错误。near+'interval+(begin+INT，end+INT)‘mysql>+CREATE+TABLE+interval+(begin+INT，end+INT)；查询正常，0行受影响(0.01秒)|157631|157632|​|157633|同样，问题中的查询可以通过将关键字key括在反引号中来修复，如下所示：|157634|INSERT+INTO+user_details+(username,+location,+`key`)
VALUES+('Tim',+'Florida',+42)";+++++++++++++++%5E+++%5E|code-block|syntax|javascript|157635|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/2889884/1709587|1|http://dev.mysql.com/doc/en/keywords.html^0|0|E|6|L|6|S|6|0|0|0|0|0|0|D|3|0|0|8|3|E|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|26|4|0|0|0|0|12|1|0|A|W|1|0|0|1R|8|0|0|0|H|3|0|0^^$0|@$1|2|3|4|5|6|7|4A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|4B|8|@$D|4C|E|4D|F|G]|$D|4E|E|4F|F|G]|$D|4G|E|4H|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|4I|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|4J|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|4K|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|4L|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|4M|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|4N|8|@$D|4O|E|4P|F|S]]|9|@]|A|$]]|$1|T|3|P|5|6|7|4Q|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|4R|8|@$D|4S|E|4T|F|S]|$D|4U|E|4V|F|G]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|4W|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|4X|8|@]|9|@]|A|$]]|$1|Z|3|-4|5|6|7|4Y|8|@]|9|@]|A|$]]|$1|10|3|11|5|12|7|4Z|8|@]|9|@]|A|$]]|$1|13|3|14|5|12|7|50|8|@]|9|@]|A|$]]|$1|15|3|16|5|12|7|51|8|@]|9|@]|A|$]]|$1|17|3|18|5|12|7|52|8|@]|9|@]|A|$]]|$1|19|3|1A|5|12|7|53|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|12|7|54|8|@]|9|@]|A|$]]|$1|1D|3|1E|5|12|7|55|8|@]|9|@]|A|$]]|$1|1F|3|1G|5|12|7|56|8|@]|9|@]|A|$]]|$1|1H|3|1I|5|12|7|57|8|@]|9|@]|A|$]]|$1|1J|3|1K|5|12|7|58|8|@]|9|@]|A|$]]|$1|1L|3|1M|5|12|7|59|8|@]|9|@]|A|$]]|$1|1N|3|1O|5|12|7|5A|8|@]|9|@]|A|$]]|$1|1P|3|1Q|5|12|7|5B|8|@]|9|@]|A|$]]|$1|1R|3|1S|5|12|7|5C|8|@]|9|@]|A|$]]|$1|1T|3|1U|5|12|7|5D|8|@]|9|@]|A|$]]|$1|1V|3|1W|5|12|7|5E|8|@]|9|@]|A|$]]|$1|1X|3|1Y|5|12|7|5F|8|@]|9|@]|A|$]]|$1|1Z|3|20|5|12|7|5G|8|@]|9|@]|A|$]]|$1|21|3|22|5|12|7|5H|8|@]|9|@]|A|$]]|$1|23|3|24|5|12|7|5I|8|@]|9|@]|A|$]]|$1|25|3|26|5|12|7|5J|8|@]|9|@]|A|$]]|$1|27|3|28|5|12|7|5K|8|@]|9|@]|A|$]]|$1|29|3|2A|5|12|7|5L|8|@]|9|@]|A|$]]|$1|2B|3|2C|5|12|7|5M|8|@]|9|@]|A|$]]|$1|2D|3|2E|5|12|7|5N|8|@]|9|@]|A|$]]|$1|2F|3|2G|5|12|7|5O|8|@]|9|@]|A|$]]|$1|2H|3|2I|5|12|7|5P|8|@]|9|@]|A|$]]|$1|2J|3|2K|5|12|7|5Q|8|@]|9|@]|A|$]]|$1|2L|3|2M|5|12|7|5R|8|@]|9|@]|A|$]]|$1|2N|3|2O|5|12|7|5S|8|@]|9|@]|A|$]]|$1|2P|3|2Q|5|12|7|5T|8|@]|9|@]|A|$]]|$1|2R|3|2S|5|12|7|5U|8|@]|9|@]|A|$]]|$1|2T|3|2U|5|12|7|5V|8|@]|9|@]|A|$]]|$1|2V|3|2W|5|12|7|5W|8|@]|9|@]|A|$]]|$1|2X|3|-4|5|6|7|5X|8|@]|9|@]|A|$]]|$1|2Y|3|2Z|5|6|7|5Y|8|@]|9|@]|A|$]]|$1|30|3|31|5|6|7|5Z|8|@]|9|@]|A|$]]|$1|32|3|33|5|6|7|60|8|@]|9|@]|A|$]]|$1|34|3|35|5|6|7|61|8|@]|9|@]|A|$]]|$1|36|3|37|5|6|7|62|8|@]|9|@]|A|$]]|$1|38|3|-4|5|6|7|63|8|@]|9|@]|A|$]]|$1|39|3|3A|5|12|7|64|8|@]|9|@]|A|$]]|$1|3B|3|-4|5|6|7|65|8|@]|9|@]|A|$]]|$1|3C|3|3D|5|6|7|66|8|@]|9|@$D|67|E|68|1|69]]|A|$]]|$1|3E|3|3F|5|6|7|6A|8|@]|9|@]|A|$]]|$1|3G|3|3H|5|6|7|6B|8|@]|9|@]|A|$]]|$1|3I|3|3J|5|6|7|6C|8|@$D|6D|E|6E|F|G]]|9|@]|A|$]]|$1|3K|3|3L|5|6|7|6F|8|@]|9|@$D|6G|E|6H|1|6I]]|A|$]]|$1|3M|3|3N|5|6|7|6J|8|@]|9|@]|A|$]]|$1|3O|3|3P|5|M|7|6K|8|@$D|6L|E|6M|F|G]]|9|@]|A|$]]|$1|3Q|3|-4|5|6|7|6N|8|@]|9|@]|A|$]]|$1|3R|3|3S|5|6|7|6O|8|@]|9|@]|A|$]]|$1|3T|3|3U|5|6|7|6P|8|@$D|6Q|E|6R|F|G]]|9|@]|A|$]]|$1|3V|3|3W|5|3X|7|6S|8|@]|9|@]|A|$3Y|3Z]]|$1|40|3|-4|5|6|7|6T|8|@]|9|@]|A|$]]]|41|$42|$5|43|44|45|A|$46|47]]|48|$5|43|44|45|A|$46|49]]]]

<h1>The Problem</h1>
In MySQL, certain words like <code>SELECT</code>, <code>INSERT</code>, <code>DELETE</code> etc. are reserved words. Since they have a special meaning, MySQL treats it as a syntax error whenever you use them as a table name, column name, or other kind of identifier - unless you surround the identifier with backticks.
As noted in the official docs, in section <a href="http://dev.mysql.com/doc/en/identifiers.html" rel="noreferrer">10.2 Schema Object Names</a> (emphasis added):
<blockquote>
Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers.
...
If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.
...
The identifier quote character is the backtick (&quot;<code>`</code>&quot;):
</blockquote>
A complete list of keywords and reserved words can be found in section <a href="http://dev.mysql.com/doc/en/keywords.html" rel="noreferrer">10.3 Keywords and Reserved Words</a>. In that page, words followed by &quot;(R)&quot; are reserved words. Some reserved words are listed below, including many that tend to cause this issue.
<ul>
<li>ADD</li>
<li>AND</li>
<li>BEFORE</li>
<li>BY</li>
<li>CALL</li>
<li>CASE</li>
<li>CONDITION</li>
<li>DELETE</li>
<li>DESC</li>
<li>DESCRIBE</li>
<li>FROM</li>
<li>GROUP</li>
<li>IN</li>
<li>INDEX</li>
<li>INSERT</li>
<li>INTERVAL</li>
<li>IS</li>
<li>KEY</li>
<li>LIKE</li>
<li>LIMIT</li>
<li>LONG</li>
<li>MATCH</li>
<li>NOT</li>
<li>OPTION</li>
<li>OR</li>
<li>ORDER</li>
<li>PARTITION</li>
<li>RANK</li>
<li>REFERENCES</li>
<li>SELECT</li>
<li>TABLE</li>
<li>TO</li>
<li>UPDATE</li>
<li>WHERE</li>
</ul>
<h1>The Solution</h1>
You have two options.
<h2>1. Don't use reserved words as identifiers</h2>
The simplest solution is simply to avoid using reserved words as identifiers. You can probably find another reasonable name for your column that is not a reserved word.
Doing this has a couple of advantages:
<ul>
<li>It eliminates the possibility that you or another developer using your database will accidentally write a syntax error due to forgetting - or not knowing - that a particular identifier is a reserved word. There are many reserved words in MySQL and most developers are unlikely to know all of them. By not using these words in the first place, you avoid leaving traps for yourself or future developers.
</li>
<li>The means of quoting identifiers differs between SQL dialects. While MySQL uses backticks for quoting identifiers by default, ANSI-compliant SQL (and indeed MySQL in ANSI SQL mode, as noted <a href="https://stackoverflow.com/a/2889884/1709587">here</a>) uses double quotes for quoting identifiers. As such, queries that quote identifiers with backticks are less easily portable to other SQL dialects.
</li>
</ul>
Purely for the sake of reducing the risk of future mistakes, this is usually a wiser course of action than backtick-quoting the identifier.
<h2>2. Use backticks</h2>
If renaming the table or column isn't possible, wrap the offending identifier in backticks (<code>`</code>) as described in the earlier quote from <a href="http://dev.mysql.com/doc/refman/5.6/en/identifiers.html" rel="noreferrer">10.2 Schema Object Names</a>.
An example to demonstrate the usage (taken from <a href="http://dev.mysql.com/doc/en/keywords.html" rel="noreferrer">10.3 Keywords and Reserved Words</a>):
<blockquote><pre><code>mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax.
near 'interval (begin INT, end INT)'
mysql&gt; CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)</code></pre></blockquote>
Similarly, the query from the question can be fixed by wrapping the keyword <code>key</code> in backticks, as shown below:
<pre><code>INSERT INTO user_details (username, location, `key`)
VALUES ('Tim', 'Florida', 42)&quot;; ^ ^
</code></pre>

I'm trying to execute a simple MySQL query as below:

<pre class="lang-sql prettyprint-override"><code>INSERT INTO user_details (username, location, key)
VALUES ('Tim', 'Florida', 42)
</code></pre>

But I'm getting the following error:

<blockquote>
 ERROR 1064 (42000): 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 <code>'key) VALUES ('Tim', 'Florida', 42)'</code> at line 1
</blockquote>

How can I fix the issue?

Syntax error due to using a reserved word as a table or column name in MySQL

数据库

服务器

MySQL

我正在尝试执行一个简单的MySQL查询，如下所示：INSERT INTO user_details (username, location, key)VALUES ('Tim', 'Florida', 42)但是我得到了以下错误：错误1064 (42000)：您的SQL语法中有一个错误；请查看与您的MySQL服务器版本...

问由于在MySQL中使用保留字作为表名或列名而导致的语法错误
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问由于在MySQL中使用保留字作为表名或列名而导致的语法错误EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问由于在MySQL中使用保留字作为表名或列名而导致的语法错误
EN