一、项目从Oracle迁移到MySQL时,报错:Error updating database. Cause: java.sql.SQLException: You can’t specify target table ‘irp_workflow_form’ for update in FROM clause
// 问题记录:
### Error updating database. Cause: java.sql.SQLException: You can't specify target table 'irp_workflow_form' for update in FROM clause
### The error may involve com.hundsun.gefrm.irp.server.mapper.IrpCommonMapper.updateLastNodeForm-Inline
### The error occurred while setting parameters
### SQL: update irp_workflow_form set approve_status = ?, flow_id_list = ? where wf_instance_no = ? and wf_node_name = ? and last_update_time = (select max(last_update_time) last_update_time from irp_workflow_form a where a.wf_instance_no = ? and a.wf_node_name = ?)
### Cause: java.sql.SQLException: You can't specify target table 'irp_workflow_form' for update in FROM clause
第一时间检查SQL发现是正常的标准sql ,然后网上搜了一下问题是MySQL不支持查询时进行更新(删、改)数据操作,想想开发环境好像用的是MySQL的数据库是正常的,发布到测试环境时不支持,于是检查了下数据库的版本发现:
查询MySQL版本:
SELECT VERSION();
发现:10.3.10-MariaDB-log(开发环境的数据库不是MySQL二十MariaDB mysql的一个分支完全兼容MySQL 反之不一定)这个可以兼容运行时正常
测试环境(MySQL5.6.16)这个运行报错:
二、解决方式:更改SQL
于是根据网上建议,在外层套一层即可避免:
原SQL(mybatis 粘贴出来的 凑合着看):
update irp_workflow_form
set approve_status = #{approveStatus},
flow_id_list = #{flowIdList}
where wf_instance_no = #{wfInstanceNo,jdbcType=VARCHAR}
and wf_node_name = #{wfNodeName,jdbcType=VARCHAR}
and last_update_time =
(select max(last_update_time) as last_update_time
from irp_workflow_form a
where a.wf_instance_no = #{wfInstanceNo,jdbcType=VARCHAR}
and a.wf_node_name = #{wfNodeName,jdbcType=VARCHAR})
更改后 套一层即可避免:
update irp_workflow_form
set approve_status = #{approveStatus},
flow_id_list = #{flowIdList}
where wf_instance_no = #{wfInstanceNo,jdbcType=VARCHAR}
and wf_node_name = #{wfNodeName,jdbcType=VARCHAR}
and last_update_time =
(select t.last_update_time from (select max(last_update_time) as last_update_time
from irp_workflow_form a
where a.wf_instance_no = #{wfInstanceNo,jdbcType=VARCHAR}
and a.wf_node_name = #{wfNodeName,jdbcType=VARCHAR}) t)
三、原因分析
仔细想想 MySQL不支持查询时进行更新(删、改)数据操作,大概猜测是因为事务这块不支持,于是继续找资料:
为什么MySQL不允许对同一张表同时进行查询和更新?
下面这个看起来没有问题的SQL语句却运行不了
UPDATE `tb1` AS outer_tb1 SET cnt = (
SELECT count(*) FROM `tb1` AS inner_tb1
WHERE inner_tb1.type = outer_tb1.type
);
如果清楚MySQL是如何执行查询的,就知道为什么会这样子了。
因为执行SELECT count(*) FROM tb1 AS inner_tb1 WHERE inner_tb1.type = outer_tb1.type
的时候会获得读锁(共享锁),一旦数据表被加上读锁,其他请求可以对该表再次增加读锁,但是不能增加写锁。(当一个请求在读数据时,其他请求也可以读,但是不能写,因为一旦另外一个线程写了数据,就会导致当前线程读取到的数据不是最新的了。这就是不可重复读现象
解决
可以通过使用生成表的形式来绕过这种限制,因为MySQL只会把这个表当作一个临时表来处理。
UPDATE tb1 INNER JOIN(
SELECT type,count(*) AS cnt FROM tb1
GROUP BY type
) AS outer_tb1 USING(type)
SET tb1.cnt = outer_tb1.cnt;
参考博客:https://blog.csdn.net/weixin_44019182/article/details/107872658