mysql - SQL 语法错误 ERROR 1064

Question

每次我尝试在 MySQL 命令行中创建下表时:

CREATE TABLE book(
   `book_id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `isbn` char(20),
   `title` char(20),
   `author_f_name` char(20),
   `author_l_name` char(20),
   `condition` ENUM("as new","very good","good","fair","poor"),
   `price` decimal(8,2),
   `genre` char(20)
);

我不断收到此错误消息:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL version for the right syntax to use near 'condition ENUM("as new","very good","good","fair","poor"), price decimal(8,2), g' at line 6

我试过对 ENUM 选项使用单引号和双引号。我哪里出错了？

Answer 1

问题是你的列名是condition，这是一个Reserved Word in MySQL .

您需要更改列名称或正确引用它。

参见 this thread对于这个问题的讨论，它解释了如何引用列名。引用部分帖子:

Yes, quote you identifiers.

The normal quotechar is the backtick (`):

create table rules (condition varchar(255))

this is specific for mysql. ANSI SQL also defines this feature, but with the double quote (") as delimiter:

create table rules ("condition" varchar(255))

because the double quote is normally a string delimiter in mysql, you have to tell mysql specifically to use the ANSI_QUOTES SQL mode, or just the ANSI mode.

set sql_mode := 'ANSI'

you can also specify tha ansi mode at server startup.