I obtain a error syntax when I exceute one query

Question

I need to create table and I do this:

CREATE TABLE "home" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "address" varchar(200) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY ("id")
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

when I execute this query I obtain this error:

Errore SQL [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 '"causali" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "address" va' at line 1
  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 '"home" (
  "id" int(11) NOT NULL AUTO_INCREMENT,
  "adddress" va' at line 1

Anyone can help me?

Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — Nick, Nov 11 '18 at 11:20
@Nick: the linked answer talks a lot about *backticks*, etc., but not at all about *double quoted* names (only jarlh's comment on the question mentions it) — dnoeth, Nov 11 '18 at 11:41
@dnoeth the second answer on the linked question talks about double quotes. — Nick, Nov 11 '18 at 11:44
@Nick: Yes, but who is going to read the 2nd answer when there's an accepted answer with such a high score :-) — dnoeth, Nov 11 '18 at 12:17

Madhur Bhaiya · Accepted Answer · 2018-11-11T11:21:05.470

2

MySQL does not like double quotes on the table / column / alias names, until we enable ANSI_QUOTES mode.

Treat " as an identifier quote character (like the ` quote character) and not as a string quote character. You can still use backticks to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotation marks to quote literal strings because they are interpreted as identifiers.

This mode is by default Disabled. Now, you can either use backticks around them, or use without double quotes (except when it is a Reserved keyword, then you will have to use backticks around it).

CREATE TABLE home (
  id int(11) NOT NULL AUTO_INCREMENT,
  address varchar(200) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

edited Nov 11 '18 at 11:21

answered Nov 11 '18 at 11:02

Madhur Bhaiya

28,155
10
49
57

Of course double quoted names are allowed, when you switch on ANSI_QUOTES (also part of ANSI sql mode) – dnoeth Nov 11 '18 at 11:16
These questions should be voted to close as duplicates, not answered. – Nick Nov 11 '18 at 11:20
@Nick I did not know if a dupe existed. If I know, I generally close vote specifying the dupe. Moreover, the accepted answer does not specify about ANSI_QUOTES mode. – Madhur Bhaiya Nov 11 '18 at 11:38
1

@MadhurBhaiya the second answer on the linked question talks about double quotes. – Nick Nov 11 '18 at 11:46
@Nick ok thanks. I did not go through all the answers. Will take note of this in particular question in future. – Madhur Bhaiya Nov 11 '18 at 11:49

I obtain a error syntax when I exceute one query

1 Answers1