MySQL ERROR 1064 in line, but don't know what is wrong?

Question

I've got the following line I want to execute in MySQL:

CREATE TABLE 'virtual_domains' (
    'id' int(11) NOT NULL auto_increment,
    'name' varchar(50) NOT NULL,
     PRIMARY KEY ('id'))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

However, it gave me this error:

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 ''virtual_domains' ('id' int(11) NOT NULL auto_increment, 'name' varchar(50) NOT ' at line 1

What am I missing here??

Thanks for the help!

Rob

Get rid off single quotes for table and column names, its invalid in mysql. — Abhik Chakraborty, Jun 12 '15 at 09:40

score 2 · Accepted Answer · answered Jun 12 '15 at 09:41

remove the single quotes around the table and column names. use backticks instead.

CREATE TABLE `virtual_domains` (
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(50) NOT NULL,
     PRIMARY KEY (`id`))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

score 0 · Answer 2 · edited May 23 '17 at 11:58

In addition to use of backticks (`symbol`), since none of the identifiers you have used require escaping, you can simply remove the escaping altogether:

CREATE TABLE virtual_domains (
    id int(11) NOT NULL auto_increment,
    name varchar(50) NOT NULL,
     PRIMARY KEY (id))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

Alternatively when you do need to escape symbols, instead of using backticks, consider using ANSI compliant quotes ("symbol"). You will need to set SQL_MODE=ANSI_QUOTES:

SET SQL_MODE=ANSI_QUOTES;

CREATE TABLE "virtual_domains" (
    "id" int(11) NOT NULL auto_increment,
    "name" varchar(50) NOT NULL,
     PRIMARY KEY ("id"))
ENGINE=InnoDB DEFAULT CHARSET=utf8;

The benefit of this is improved portability between the various RDBMS.

SqlFiddle here

MySQL ERROR 1064 in line, but don't know what is wrong?

2 Answers2