使用mysql merge into 时遇到的mysql #1093错误的解决

INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5),5,1,1,1,now()) ON duplicate key UPDATE readcount=readcount + 1,readtime=now()

结果提示错误，如下：

静态分析：

分析时发现11个错误。

Unrecognized keyword. (near "key" at position 185)
Unexpected token. (near "=" at position 209)
Unexpected token. (near "readcount" at position 210)
Unexpected token. (near "+" at position 220)
Unexpected token. (near "1" at position 222)
Unexpected token. (near "," at position 223)
Unexpected token. (near "readtime" at position 224)
Unexpected token. (near "=" at position 232)
Unrecognized keyword. (near "now" at position 233)
Unexpected token. (near "(" at position 236)
Unexpected token. (near ")" at position 237)
SQL 查询： 文档

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) ,5,1,1,1,now()) ON duplicate key UPDATE readcount=readcount + 1,readtime=now()

MySQL 返回： 文档

#1093 - You can‘t specify target table ‘readbook‘ for update in FROM clause

error code

于是更改语句：

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT * from (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) as a ),5,1,1,1,now()) ON duplicate key UPDATE readcount=readcount + 1,readtime=now()

提示插入成功。

之所以能成功是因为，1093错误为：但修改一个表的时候子查询不能是同一个表，解决办法：把子查询再套一层，变成原来表的孙子查询就可以了

其实，这个过程中，由于对Mysql不熟悉，我还遇到了其它很多问题。

比如 

(SELECT * from (SELECT readid From readbook where userid=1 and bookid=1 and readpage=5) as a

必须有 as a ，因为必须给表起个别名，但我以前使用SQL SERVER时，没这个要求。

还有就是，原来我还这么写过

INSERT INTO readbook (readid,readpage,readcount,bookid,userid,readtime) VALUES ( (SELECT 

CASE WHEN a.readid>0 THEN a.readid ELSE NULL END from(SELECT readid From readbook where 

userid=1 and bookid=1 and readpage=3) as a) ,3,1,1,1,now()) ON duplicate key UPDATE 

readcount=readcount + 1,readtime=now()

后来逐渐尝试，发现这个CASE WEHN是没有必要的，貌似MYSQL能自动把查不到结果的值，以默认值代替。

使用mysql merge into 时遇到的mysql #1093错误的解决

标签：class   技术分享   eve   分享图片   cli   存在   closed   position   serve