MySQL中，不能在一个DML（数据操纵语言，如INSERT, UPDATE, DELETE）语句中直接引用目标表进行子查询

错误示例

   <delete id="deleteOldRelations">DELETE FROM departments_closure_tableWHERE descendant IN ( SELECT descendant FROM departments_closure_tableWHERE ancestor = #{departmentId})</delete>

程序运行之后，会报错：You can't specify target table 'departments_closure_table' for update in FROM clause; nested exception is java.sql.SQLException: You can't specify target table 'departments_closure_table' for update in FROM clause] with root cause

错误原因

这个语句违反了MySQL的语法规则，因为它试图在一个DELETE语句的FROM子句中引用了正在更新的表departments_closure_table。在MySQL中，你不能在一个DML（数据操纵语言，如INSERT, UPDATE, DELETE）语句中直接引用目标表进行子查询。

如何解决

解决办法由ChatGPT生成。
可以通过以下步骤解决这个问题：

1. 先查询出需要删除的 descendant 列表并存储在临时表中。
2. 再删除临时表中的这些 descendant。

修正后的 SQL

1. 创建临时表并存储需要删除的 descendant 列表：

<insert id="createTempTable" parameterType="long">CREATE TEMPORARY TABLE temp_descendantsASSELECT descendant FROM departments_closure_tableWHERE ancestor = #{departmentId}
</insert>

2. 删除关系：

<delete id="deleteOldRelations">DELETE FROM departments_closure_tableWHERE descendant IN (SELECT descendant FROM temp_descendants)
</delete>

3. 删除临时表：

<delete id="dropTempTable">DROP TEMPORARY TABLE temp_descendants
</delete>

完整的 XML 配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.example.demo.mapper.DepartmentsClosureDao"><insert id="createTempTable" parameterType="long">CREATE TEMPORARY TABLE temp_descendantsASSELECT descendant FROM departments_closure_tableWHERE ancestor = #{departmentId}</insert><delete id="deleteOldRelations">DELETE FROM departments_closure_tableWHERE descendant IN (SELECT descendant FROM temp_descendants)</delete><delete id="dropTempTable">DROP TEMPORARY TABLE temp_descendants</delete></mapper>

Java 调用代码

在 Java 代码中，按顺序调用这些方法：

package com.example.demo.service;import com.example.demo.mapper.DepartmentsClosureDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;@Service
public class DepartmentService {@Autowiredprivate DepartmentsClosureDao departmentsClosureDao;@Transactionalpublic void moveDepartment(Long departmentId, Long newParentId) {// 创建临时表departmentsClosureDao.createTempTable(departmentId);// 删除旧的关系departmentsClosureDao.deleteOldRelations(departmentId);// 插入新的关系 (这个方法需要你自己实现，假设它存在)departmentsClosureDao.insertNewRelations(departmentId, newParentId);// 删除临时表departmentsClosureDao.dropTempTable();}// 其他服务方法
}

总结

通过使用临时表，我们可以避免 MySQL 不允许在同一个查询中更新和查询同一张表的限制。按照上述步骤和示例代码进行实现，可以正确删除部门关系而不引发 You can't specify target table 错误。