What causes this syntax error when creating a table?

Question

I am trying to create a table in MySQL, but I am getting an error. The code is looking OK to me, but I don't know why I am getting the error.

This is the code :

CREATE TABLE usage (
    user_id    VARCHAR(20) ,
    usage_date  DATE ,
    usage_location     VARCHAR(20) ,
    time_spent      INT
);

The error that I am getting:

Query 1: 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 'usage (
    user_id    VARCHAR(20) ,
    usage_date  DATE ,
    usage_locatio' at line 1

You should avoid using any of the words on the reserved word list, see: [Keywords and Reserved Words](https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-U), and only as an escape use the backticks. — Luuk, Feb 09 '22 at 15:23
Avoid using RESERVED WORDS and KEYWORDS as identifiers in MySQL. That's still best practice. — spencer7593, Feb 09 '22 at 15:29

score 1 · Answer 1 · answered Feb 09 '22 at 15:04

1

try using some other names like usage1 for datausage because that is reserved

CREATE TABLE stuffusage (
    user_id    VARCHAR(20) ,
    usage_date  DATE ,
    usage_location     VARCHAR(20) ,
    time_spent      INT
);

answered Feb 09 '22 at 15:04

Pratik Agrawal

370
1
16

score 0 · Accepted Answer · answered Feb 09 '22 at 15:00

0

Try putting backticks around usage. It seems to be a keyword.

CREATE TABLE `usage` (
    user_id    VARCHAR(20) ,
    usage_date  DATE ,
    usage_location     VARCHAR(20) ,
    time_spent      INT
);

answered Feb 09 '22 at 15:00

md2perpe

3,154
2
16
20

score 0 · Answer 3 · answered Feb 09 '22 at 15:01

0

"usage" cannot be used as a table name. It is a special code defined in MYSQL.

answered Feb 09 '22 at 15:01

MSipahi

9
3

Best practice is to avoid using keywords and reserved words as identifiers in MySQL (table name, column name, etc). `USAGE` is a MySQL Reserved Word (documented in the MySQL Reference Manual) It is possible to use a reserved word as an identifier, but it has to be properly escaped. Typically enclosed in backticks, – spencer7593 Feb 09 '22 at 15:36

What causes this syntax error when creating a table?

3 Answers3