如何使用mybatis创建alter table语句？mysqlsyntaxerrorexception：您的SQL语法中有错误

Question

@Mapper
public interface StatMapper {
    @Update("alter table stats reorganize partition #{pFirst},#{pSecond} into ( "
            + "partition #{pSecond} values less than (#{dSecond}) "
            + ");")
    public void updateFirstPartition(@Param("pFirst")String pFirst, @Param("pSecond")String pSecond, @Param("dSecond")LocalDate dSecond);

如何使用MyBatis发出此ALTER TABLE语句？

语句应如下所示(P0和P1被P20180901和P20181001替换）：

alter table stats reorganize partition p0,p1 into (
    partition p1 values less than ('2018-10-01')
);

Answer 1

${}是文本替换，#{}是java.sql.PreparedStatement中的占位符（有关详细信息，请参阅常见问题解答）。因此，使用您的代码，MyBatis生成一个准备好的语句，如下所示...

PreparedStatement ps = connection.prepareStatement(
  "alter table stats reorganize partition ?,? into (partition ? values less than (?))");

...它失败，因为您不能为分区名使用占位符。

以下方法应该起作用。

java prettyprint-override">@Update("alter table stats reorganize partition ${pFirst},${pSecond} into ( "
  + "partition ${pSecond} values less than (#{dSecond}) "
  + ")")