What is problem in this MySQL Query, it is giving error 1064 (42000)?

Question

the query is

CREATE TABLE order(
    order_id int primary key,
    customer_name varchar(30) not null,
    product_name varchar(20) not null,
    date_ordered date,
    quantity int,
    unit_price float,
    phone_no varchar(20)
);

the error is :

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 'order( order_id int primary key, customer_name varchar(30) not null, ' at line 1

i am using MySQL verson 8.0.32

Answer 1

ORDER is part os mysql syntax just use another name for your table like orders:

CREATE TABLE orders (
order_id int primary key,
customer_name varchar(30) not null,
product_name varchar(20) not null,
date_ordered date,
quantity int,
unit_price float,
phone_no varchar(20)
);

Answer 2

order is a keyword you cannot use it.To know the list of other keywords check the below link https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Answer 3

Because order is part of the SQL syntax (to sort queried records using ORDER BY) it wont work.

You have to rename your table to something different like for example orders then it will work:

CREATE TABLE orders (
   order_id int primary key,
   customer_name varchar(30) not null,
   product_name varchar(20) not null,
   date_ordered date,
   quantity int,
   unit_price float,
   phone_no varchar(20)
);