mysql syntax error for timestamp

I have this piece of SQL that is being fed to Mysql.

CREATE TABLE pn_history(
 member INT,
 action INT,
 with INT,
 timestamp DATETIME,
 details VARCHAR(256)
)

But is comes back as an error about 开发者_开发知识库the syntax.

#1064 - 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 'with INT,
timestamp DATETIME,
details VARCHAR(256)
)' at line 4 

Why is this failing?

Both 'with' and 'timestamp' are reserved words in MySQL. So to get this to work, you'd need to escape each one:

CREATE TABLE pn_history(
 member INT,
 action INT,
 `with` INT,
 `timestamp` DATETIME,
 details VARCHAR(256)
)

Really though, you need to consider changing the names of your columns identifiers.

Read more about MySQL Reserved Words.

EDIT: Actually, TIMESTAMP is not a reserved word. The documentation says:

MySQL allows some keywords to be used as unquoted identifiers because many people previously used them. Examples are those in the following list:

• ACTION
• BIT
• DATE
• ENUM
• NO
• TEXT
• `TIME
• TIMESTAMP

So I guess that means peer pressure took TIMESTAMP off the reserved word list. Hah!

The problem is the name of the with column. Change the name into something like withValue.

CREATE TABLE pn_history(
 member INT,
 action INT,
 withValue INT,
 timestamp DATETIME,
 details VARCHAR(256)
)

timestamp is a keyword (it is a data type in mysql) which may be causing you problems.

I would suggest using a different name, but if it must be named timestamp, try using backticks to quote it.