MySQL CREATE TABLE 'You have an error in your SQL syntax' error

Question

I am pulling my hair out over this. I have connected to a MySQL server and am trying to create a table on it:

CREATE TABLE order (
    order_id INT,
    order_is_overnight BIT,
    order_quantity INT,
    order_amount DOUBLE,
    order_timestamp DATETIME
);

When I run this I get:

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 'order (
        order_id INT,
        order_is_overnight BIT,
        order_quantity INT,
        order_amou' at line 1
SQLState:  42000
ErrorCode: 1064

This is the world's vaguest error message, akin to a Java exception that says "Whoops, something went wrong somewhere in your code!"

Any ideas where I'm going awry?! I have checked, and rechecked, and rechecked and this seems to be a perfectly valid/legal CREATE TABLE statement (don't worry about performance, indexes, keys, etc.; this is just a dummy/test table).

don't forget that ORDER is a reserved word – e4c5 Jul 28 '16 at 11:59 — e4c5, Jul 28 '16 at 11:59
AHHHHHHHHHHHHH..... thanks @e4c5 (+1) – smeeb Jul 28 '16 at 12:04 — smeeb, Jul 28 '16 at 12:04

score 2 · Accepted Answer · answered Jul 28 '16 at 11:59

2

order is an reserved word, you must enclose it name in back quotes

CREATE TABLE `order` (
    order_id INT,
    order_is_overnight BIT,
    order_quantity INT,
    order_amount DOUBLE,
    order_timestamp DATETIME
);

answered Jul 28 '16 at 11:59

Ivan Cachicatari

4,212
2
21
41

Thanks @Ivan C. (+1). FWIW 'order' (in single quotes) did not work, but changing it to **orders** (plural) did. Weird. – smeeb Jul 28 '16 at 12:05
`order` is a reserved word used as this: `SELECT * FROM table ORDER BY id`... BTW fee free to mark my answer as accepted :) – Ivan Cachicatari Jul 28 '16 at 12:08
I will feel free! When SO let's me. You can't accept answers within a certain timeframe of posting a question ;-) – smeeb Jul 28 '16 at 12:11

MySQL CREATE TABLE 'You have an error in your SQL syntax' error

1 Answers1