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?

解决方案

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.

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.

您需要对表名和数据库名使用反引号。语法如下：UPDATE `yourDatabaseName`.`yourTableName`

SET yourColumnName1=yourColumnName1+1

WHERE yourColumnName2=’yourValue’;

为了理解上述语法，让我们创建一个表。创建表的查询如下：mysql> use test;

Database changed

mysql> create table Add1Demo

-> (

-> Id varchar(10),

-> Value int

-> );

使用insert命令在表中插入一些记录。查询如下：mysql> insert into Add1Demo values('1',780);

mysql> insert into Add1Demo values('2',1001);

mysql> insert into Add1Demo values('3',1654);

mysql> insert into Add1Demo values('4',3999);

mysql> insert into Add1Demo values('5',5999);

mysql> insert into Add1Demo values('6',799);

mysql> insert into Add1Demo values('8',899);

使用select语句显示表中的所有记录。查询如下：mysql> select *from Add1Demo;

以下是输出：+------+-------+

| Id   | Value |

+------+-------+

| 1    |   780 |

| 2    |  1001 |

| 3    |  1654 |

| 4    |  3999 |

| 5    |  5999 |

| 6    |   799 |

| 8    |   899 |

+------+-------+

7 rows in set (0.00 sec)

这是在数据库名称和表名称周围使用反引号的查询。不要对数据库和表名使用单引号。查询如下，假设将1添加到当前字段。mysql> update `test`.`Add1Demo`

-> set Value=Value+1

-> where Id='6';

Rows matched: 1 Changed: 1 Warnings: 0

现在，您可以再次检查具有特定ID 6的表记录。查询如下：mysql> select *from Add1Demo where Id='6';

以下是输出：+------+-------+

| Id   | Value |

+------+-------+

| 6    |   800 |

+------+-------+

1 row in set (0.00 sec)