Syntax error even after replacement of keyword in field name in mysql table schema?

Asked Oct 10 '19 at 02:31

Active Oct 10 '19 at 02:39

Viewed 15 times

Here is my table schema:

CREATE TABLE invoice_item
(
    _id                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    invoice_id             BIGINT,
    item                   CHAR(11) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    # One of:
    #   - 'D': Default
    #   - 'M': Meta
    type                   CHAR(1) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    order                  INT NOT NULL,
    status                 CHAR(1) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    INDEX (invoice_id, item, status, order)
);

And I got this error message

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 INT NOT NULL,

I am using mysql 8 on AWS RDS.

I am not sure if it is because 'order' is a reserved keyword.

I have replaced 'order' with 'rank' (to avoid using a keyword) but mysql still throws a syntax error at the same spot.

What is exactly the syntax error there?

edited Oct 10 '19 at 02:39

asked Oct 10 '19 at 02:31

Anthony Kong

37,791
46
172
304

Have you tried `order` instead? Using these backward apostrophies? – Grzegorz Oct 10 '19 at 02:34
It's a keyword, but you can by using backticks: `\`order\`` – Paul T. Oct 10 '19 at 02:34
Question updated. Even if I replaced order with rank there is still a syntax error. But I am not to tell what it is. – Anthony Kong Oct 10 '19 at 02:35
Wrong quotes, those are NOT the same as backticks. The character usually located below the ESC key, on the same key with `~` – Paul T. Oct 10 '19 at 02:36
I have replaced the keyword with another word rank, without quote at all, but still get the same error message. – Anthony Kong Oct 10 '19 at 02:37
@Barmar Not a duplicate. See edited version of the question. – Anthony Kong Oct 10 '19 at 02:39
1

Know that `type` and `status` are keywords too. You can easily google [the reserved keyword list](https://dev.mysql.com/doc/refman/5.5/en/keywords.html#keywords-5-5-detailed-T) – Paul T. Oct 10 '19 at 02:39
@PaulT. You're right. Type is also a keyword. – Anthony Kong Oct 10 '19 at 02:41
1

And `RANK` became a reserved word in 8.0.2. – Barmar Oct 10 '19 at 02:51
@Bamar Thanks for the heads up! Appreciate it. – Anthony Kong Oct 10 '19 at 05:17

Syntax error even after replacement of keyword in field name in mysql table schema?

0 Answers0