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
– Abhik Chakraborty, Commented Jun 12, 2015 at 9:40

Jens · Accepted Answer · 2015-06-12 09:41:31Z

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;

Community · Accepted Answer · 2017-05-23 11:58:35Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related