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

错误示例

   <delete id="deleteOldRelations">
        DELETE FROM departments_closure_table
        WHERE descendant IN ( 
                SELECT descendant FROM departments_closure_table
                WHERE ancestor = #{departmentId}
            )
    </delete>
1
2
3
4
5
6
7

程序运行之后，会报错：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_descendants
    AS
    SELECT descendant FROM departments_closure_table
    WHERE ancestor = #{departmentId}
</insert>
1
2
3
4
5
6

2. 删除关系：

<delete id="deleteOldRelations">
    DELETE FROM departments_closure_table
    WHERE descendant IN (SELECT descendant FROM temp_descendants)
</delete>
1
2
3
4

3. 删除临时表：

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

完整的 XML 配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
    PUBLIC "-//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_descendants
        AS
        SELECT descendant FROM departments_closure_table
        WHERE ancestor = #{departmentId}
    </insert>

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

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

</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

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 {

    @Autowired
    private DepartmentsClosureDao departmentsClosureDao;

    @Transactional
    public void moveDepartment(Long departmentId, Long newParentId) {
        // 创建临时表
        departmentsClosureDao.createTempTable(departmentId);

        // 删除旧的关系
        departmentsClosureDao.deleteOldRelations(departmentId);

        // 插入新的关系 (这个方法需要你自己实现，假设它存在)
        departmentsClosureDao.insertNewRelations(departmentId, newParentId);

        // 删除临时表
        departmentsClosureDao.dropTempTable();
    }

    // 其他服务方法
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

总结

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