错误信息如下

Resolved [org.springframework.jdbc.BadSqlGrammarException: <EOL><EOL>### Error updating database.  Cause: 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 'rank <EOL> <EOL> WHERE (song_list_id IN (3,4,5,6))' at line 1<EOL><EOL>### The error may exist in com/xy/music/mapper/RankMapper.java (best guess)<EOL><EOL>### The error may involve defaultParameterMap<EOL><EOL>### The error occurred while setting parameters<EOL><EOL>### SQL: DELETE FROM rank     WHERE (song_list_id IN (?,?,?,?))<EOL><EOL>### Cause: 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 'rank <EOL> <EOL> WHERE (song_list_id IN (3,4,5,6))' at line 1<EOL>; bad SQL grammar []; 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 'rank <EOL> <EOL> WHERE (song_list_id IN (3,4,5,6))' at line 1]

已解决 [org.springframework.jdbc.BadSqlGrammarException: <EOL><EOL>### 更新数据库时出错。原因：java.sql.SQLSyntaxErrorException：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 1 行的 'rank <EOL> <EOL> WHERE (song_list_id IN (3,4,5,6))' 附近使用正确的语法<EOL><EOL># ##错误可能存在于com/xy/music/mapper/RankMapper.java（最佳猜测）<EOL><EOL>###错误可能涉及defaultParameterMap<EOL><EOL>###设置时出错parameters<EOL><EOL>### SQL: DELETE FROM rank WHERE (song_list_id IN (?,?,?,?))<EOL><EOL>### Cause: java.sql.SQLSyntaxErrorException: You have an error在您的 SQL 语法中；检查与您的 MySQL 服务器版本相对应的手册，以在第 1 行的“rank <EOL> <EOL> WHERE (song_list_id IN (3,4,5,6))' 附近使用正确的语法<EOL>；糟糕的 SQL 语法 [];嵌套异常是 java.sql.SQLSyntaxErrorException：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 1 行的 'rank <EOL> <EOL> WHERE (song_list_id IN (3,4,5,6))' 附近使用正确的语法]

数据库表结构如下

image.png

用MybatisPlus生成的Pojo

image.png 查询语句如下:

@Transactional(rollbackFor = Exception.class)
    @Override
    public boolean removeBatchByIdsAndOtherInfo(List<Integer> ids) {
        // 获取所有歌单
        LambdaQueryWrapper<SongList> songListLambdaQueryWrapper = new LambdaQueryWrapper<>();
        (SongList::getId,ids);
        List<SongList> songLists = songListMapper.selectList(songListLambdaQueryWrapper);

        if (songLists.size() > 0) {

            // 删除歌单照片
            List<String> songListPics = songLists.stream()
                    .map(songList -> songList.getPic().substring(songList.getPic().lastIndexOf("/") + 1))
                    .collect(Collectors.toList());
            FileUtils.delete(PathUtils.SONG_LIST_PIC_PATH,songListPics);

            // 删除包含歌单的其他信息
            LambdaQueryWrapper<Rank> rankLambdaQueryWrapper = new LambdaQueryWrapper<>();
            (Rank::getSongListId,ids);
            rankMapper.delete(rankLambdaQueryWrapper);

            LambdaQueryWrapper<ListSong> listSongLambdaQueryWrapper = new LambdaQueryWrapper<>();
            (ListSong::getSongListId,ids);
            listSongMapper.delete(listSongLambdaQueryWrapper);

            LambdaQueryWrapper<Collect> collectLambdaQueryWrapper = new LambdaQueryWrapper<>();
            (Collect::getSongListId,ids);
            collectMapper.delete(collectLambdaQueryWrapper);

            LambdaQueryWrapper<Comment> commentLambdaQueryWrapper = new LambdaQueryWrapper<>();
            (Comment::getSongListId,ids);
            commentMapper.delete(commentLambdaQueryWrapper);
        }
        // 删除歌单
        int i = songListMapper.deleteBatchIds(ids);
        return i > 0;
    }

错误原因:

数据库表名使用了rank于数据库关键词rank冲突了

解决方案：

修改数据库表名即可 image.png image.png