8

I'm doing a mysql query like:

Select * from "User";

and it returns:

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 '"User"' at line 1

The error has something to do with the double-quotes ", can I keep the select statement as is, and make mysql cope with the double quotes?

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
Mohamed Wagdy Khorshid
  • 547
  • 4
  • 15
  • 32
  • The code is not mine, the application is already built and this convention may be used all over the application – Mohamed Wagdy Khorshid Dec 14 '12 at 19:15
  • Double-quotes are standard ANSI delimiters for identifiers. What is your sql_mode? Is there a table named User, user, USER or similar? What is the case sensitivity of the server's operating system? – pilcrow Dec 14 '12 at 19:16
  • the sql_mode was : STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION and then i executed "SET GLOBAL sql_mode = 'ANSI';" and it then became: REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI – Mohamed Wagdy Khorshid Dec 14 '12 at 19:20
  • when running "select * form user" it executes correctly – Mohamed Wagdy Khorshid Dec 14 '12 at 19:22

1 Answers1

14

Taken from this post:

SET GLOBAL SQL_MODE=ANSI_QUOTES;

Personally when I tested, I had to do it like this:

SET SQL_MODE=ANSI_QUOTES;

I don't think there's any other way.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_ansi_quotes

ANSI_QUOTES

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

Community
  • 1
  • 1
Wesley Murch
  • 101,186
  • 37
  • 194
  • 228
  • it worked !!! but what is the difference between GLOBAL SQL_MODE and just SQL_MODE ?? – Mohamed Wagdy Khorshid Dec 14 '12 at 19:24
  • I'm not sure, to be honest - SQL is not a strong point for me. Both queries work for me, but the `GLOBAL` one did not seem to do anything. – Wesley Murch Dec 14 '12 at 19:25
  • 2
    `SET SESSION SQL_MODE=ANSI_QUOTES;` is another way to do it that might make more sense. See also: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html As I said, I'm no pro at SQL so you might want to research any side effects, caveats, etc. – Wesley Murch Dec 14 '12 at 19:31