Error in SQL Syntax ERROR1064
Everytime I try to create the following table in MySQL command line:
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)
);
I keep getting this error message:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresp开发者_Go百科onds 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
I've tried using single quotes and double quotes for the ENUM options. Where did I go wrong?
The problem is that your column name is condition
, which is a Reserved Word in MySQL.
You need to either change the name of your column or properly quote it.
See this thread for a discussion of this very issue, which explains how to quote the column name. To quote part of the post:
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.
condition is a reserved word in MySQL.
精彩评论