MySQL错误1064：SELECT语句中的SQL语法错误

I am relatively new to somewhat advanced MySQL querying. I had been trying to query the most recent order in an order table of a particular user using MySQL SELECT statement using the following MySQL query.

我对一些先进的MySQL查询相对较新。我一直在尝试使用MySQL SELECT语句使用以下MySQL查询查询特定用户的订单表中的最新订单。

SELECT o1.* FROM order AS o1
WHERE o1.orderDateTime = 
( 
  SELECT MAX(o2.orderDateTime) FROM order AS o2
  WHERE o2.userId = '1'
)

But I had been constantly getting the following MySQL error #1064 related to MySQL syntax.

但我一直在不断收到与MySQL语法相关的以下MySQL错误#1064。

#1064 - 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 'order AS o1 WHERE o1.orderDateTime = (SELECT MAX(o2.orderDateTime)FROM order AS ' at line 1

#1064 - 您的SQL语法有错误;查看与您的MySQL服务器版本对应的手册,以便在'订单AS o1 WHERE o1.orderDateTime =(SELECT MAX(o2.orderDateTime)FROM order AS''附近使用正确的语法)

I got similar errors in relation with INSERT statements but I managed to fix it up using the methods specified in MySQL 1064: You have an error in your SQL syntax I made every effort to fix the query in the current case but I was still unsuccessful.

我得到了与INSERT语句相关的类似错误,但我设法使用MySQL 1064中指定的方法修复它:你的SQL语法出错了我在当前案例中尽力修复查询但我仍然没有成功。

I would be grateful to you if someone can help me out with fixing this MySQL syntax error for SELECT clause specified above. It would be great if someone could specify me the exact reason for the occurrence of this issue, as well.

如果有人可以帮我修复上面指定的SELECT子句的MySQL语法错误,我将不胜感激。如果有人能指出我发生这个问题的确切原因,那将是很好的。

3 个解决方案

#1

order is a reserved word and its a bad choice for table name. You need to escape using backticks in the query

order是一个保留字,对于表名来说是一个糟糕的选择。您需要在查询中使用反引号进行转义

SELECT o1.* FROM `order` AS o1
WHERE o1.orderDateTime = (
    SELECT MAX(o2.orderDateTime) FROM `order` AS o2
    WHERE o2.userId = '1'
)

http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html

#2

As per @Abhik, order is a MySQL keyword.

根据@Abhik,order是一个MySQL关键字。

And you should avoid collapse with two methods:

你应该用两种方法避免崩溃:

Use backticks (`) (@Abhik has already explained this.)

使用反引号(`)(@Abhik已经解释了这一点。)

Prepend Database name before Table Name e.g. DataBase_Name.order.

在表名前面添加数据库名称,例如DataBase_Name.order。

But, still @Abhik's approach is preferable as in case of database name change, you need to change DataBase name in your query.

但是,仍然@ Abhik的方法更好,因为在数据库名称更改的情况下,您需要在查询中更改DataBase名称。

#3

First of all you could follow @Abhik Chakraborty suggestion to include back ticks around order table name. order is a reserved word in mysql. My suggestion was to improve your sql query. YOu could acomplish the same using:

首先,您可以按照@Abhik Chakraborty的建议,在订单表名称后面加上回滴答。 order是mysql中的保留字。我的建议是改进你的SQL查询。你可以用以下方法来完成同样的事情:

SELECT o1.* FROM `order` o1
WHERE  o1.userId = '1' order by orderDateTime desc limit 1

the subquery seems unnecessary.

子查询似乎没必要。

#1