error in SQL syntax creating a database table

Question

I'm trying to create a table in my database in mysql 14.14 Distrib 5.6.24,

I want a table with two columns:

SQL:

CREATE TABLE mytable(
    tag MEDIUMTEXT,
    index BIGINT(20)
) 
ENGINE MyISAM;

But I get the 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 'BIGINT(20)

possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — AdamMc331, Jul 02 '15 at 18:47
`index` is a reserved word in MySQL - https://dev.mysql.com/doc/refman/5.0/en/keywords.html — AdamMc331, Jul 02 '15 at 18:47
I can understand voting to close a question like this (either duplicate or typographical error). But down voting? Especially for a new person on the site? That doesn't seem friendly. — Gordon Linoff, Jul 02 '15 at 19:04

score 3 · Accepted Answer · answered Jul 02 '15 at 18:48

index is a reserved word in MySQL (and any other relational database I can think of). It's used to create, well, indexes.

In MySQL you can escape object names by enclosing them with ` characters:

CREATE TABLE mytable (
    tag MEDIUMTEXT, 
    `index` BIGINT(20) 
) ENGINE MyISAM;

But it's generally considered a better practice to avoid such cases completely. For example, you could call the numeric column tag_index:

CREATE TABLE mytable (
    tag MEDIUMTEXT, 
    tag_index BIGINT(20) 
) ENGINE MyISAM;

1 Answers1