JPA多级关联级联删除异常：MySQL生成q2_0错误SQL问题排查

1. 为何生成含q2_0的SQL？ #

这是因为你在自定义NativeQuery时混淆了JPQL语法与原生SQL的写法：

• 若你在@Query注解（即使指定了nativeQuery=true）中使用了实体类的关联属性（比如q.quiz.id）而非数据库实际列名（比如quiz_id），Hibernate会错误地尝试解析这些JPQL风格的关联引用，自动生成诸如q2_0的关联表别名，但原生SQL并不识别这种ORM框架生成的别名。MySQL对SQL语法的校验比H2更严格，因此触发SQLSyntaxException；而H2对非标准SQL兼容性更强，会忽略这类无效别名问题，导致测试环境正常但生产环境报错。

2. 解决方法 #

方案一：使用纯原生NativeQuery #

确保SQL语句完全基于数据库表名和列名编写，避免依赖实体类属性或JPQL语法。按关联层级从底层到顶层依次删除（需遵循外键约束，先删子表数据）：

@Repository
public interface QuizRepository extends JpaRepository<Quiz, Long> {
    @Modifying
    @Query(value = "DELETE FROM quiz_question_item_answer WHERE question_item_id IN (SELECT id FROM quiz_question_item WHERE question_id IN (SELECT id FROM quiz_question WHERE quiz_id = ?1))", nativeQuery = true)
    void deleteAnswersByQuizId(Long quizId);

    @Modifying
    @Query(value = "DELETE FROM quiz_question_item WHERE question_id IN (SELECT id FROM quiz_question WHERE quiz_id = ?1)", nativeQuery = true)
    void deleteItemsByQuizId(Long quizId);

    @Modifying
    @Query(value = "DELETE FROM quiz_question WHERE quiz_id = ?1", nativeQuery = true)
    void deleteQuestionsByQuizId(Long quizId);
}

调用时按顺序执行：

quizRepository.deleteAnswersByQuizId(quizId);
quizRepository.deleteItemsByQuizId(quizId);
quizRepository.deleteQuestionsByQuizId(quizId);
quizRepository.deleteById(quizId);

方案二：配置JPA级联删除（推荐） #

直接通过实体类的关联注解配置级联规则，无需自定义SQL，更符合JPA规范且避免数据库兼容性问题：

1. 在Quiz实体的questions字段添加级联规则：

@OneToMany(mappedBy = "quiz", cascade = CascadeType.REMOVE, orphanRemoval = true)
private List<QuizQuestion> questions;

2. 在QuizQuestion实体的items字段添加级联规则：

@OneToMany(mappedBy = "question", cascade = CascadeType.REMOVE, orphanRemoval = true)
private List<QuizQuestionItem> items;

3. 在QuizQuestionItem实体的answers字段添加级联规则：

@OneToMany(mappedBy = "item", cascade = CascadeType.REMOVE, orphanRemoval = true)
private List<QuizQuestionItemAnswer> answers;

之后直接调用quizRepository.deleteById(quizId)或quizRepository.delete(quiz)，JPA会自动按层级删除所有关联的子实体。

方案三：使用JPQL级联删除 #

若不想依赖实体级联配置，可使用JPQL编写删除语句（ORM框架会自动适配不同数据库的SQL语法）：

@Modifying
@Query("DELETE FROM QuizQuestionAnswer a WHERE a.item.question.quiz.id = :quizId")
void deleteAnswersByQuizId(@Param("quizId") Long quizId);

@Modifying
@Query("DELETE FROM QuizQuestionItem i WHERE i.question.quiz.id = :quizId")
void deleteItemsByQuizId(@Param("quizId") Long quizId);

@Modifying
@Query("DELETE FROM QuizQuestion q WHERE q.quiz.id = :quizId")
void deleteQuestionsByQuizId(@Param("quizId") Long quizId);

调用顺序同方案一，最后删除Quiz本身。

内容的提问来源于stack exchange，提问作者Abned