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:

• one to store text
• one to store long integer to index the text field,

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)

Answer 1

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;