mysql 8.0 版本: delete from table1 s where project_id = ? and not exists (select project_id from table2 u where u.project_id = s.project_id and s.delivery_date = u.use_date) 这个sql 运行一点问题都没有. 这个sql 在 5.6 或者5.7 上跑是有问题的. 报错: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [delete from table1 s where project_id = ? and not exists (select project_id from table2 u where u.project_id = s.project_id and s.delivery_date = u.use_date)]; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's where project_id = 330 and not exists (select project_id from ijk_project_uplo' at line 1
报错了. 找不到 s 表.
这个是解析器的一个曲缺陷. 丢掉了 meta信息 ,不知道 s 是table1 的别名.
修改方法: 1)
delete s from table1 s
where project_id = ?
and not exists (select project_id from table2 u
where u.project_id = s.project_id
and s.delivery_date = u.use_date) 2) 直接去掉 s 子查询 用table1
delete from table1
where project_id = ?
and not exists (select project_id from table2 u
where u.project_id =table1.project_id
and table1.delivery_date = u.use_date)
