Spring JPA不支持SET @变量 如何实现id重排及重置自增主键操作

解决方案 #

Spring JPA默认不支持在单条@Query注解中直接使用会话变量+多语句执行，你可以选择以下任意一种方案实现需求： #

方案1：使用JdbcTemplate分步骤执行（最稳妥，无需修改连接配置） #

直接注入Spring自带的JdbcTemplate，分三次执行单条SQL，包裹在同一事务中即可生效：

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

// 类中注入JdbcTemplate
private final JdbcTemplate jdbcTemplate;

// 构造方法注入
public XxxService(JdbcTemplate jdbcTemplate) {
    this.jdbcTemplate = jdbcTemplate;
}

@Transactional
public void resetNewsModId() {
    // 执行变量初始化
    jdbcTemplate.execute("SET @a:=0");
    // 执行id重赋值
    jdbcTemplate.update("UPDATE news_mod SET id=@a:=@a+1 WHERE canonical>0");
    // 重置自增起始值
    jdbcTemplate.execute("ALTER TABLE news_mod AUTO_INCREMENT = 1");
}

方案2：开启JDBC多语句支持，单@Query直接执行 #

先修改数据库连接配置，在JDBC URL末尾添加参数allowMultiQueries=true，示例：
jdbc:mysql://localhost:3306/your_db?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
然后在Repository层定义原生更新方法：

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.transaction.annotation.Transactional;

public interface NewsModRepository extends CrudRepository<NewsMod, Long> {
    @Modifying
    @Transactional
    @Query(value = "SET @a:=0; UPDATE news_mod SET id=@a:=@a+1 WHERE canonical>0; ALTER TABLE news_mod AUTO_INCREMENT = 1;", nativeQuery = true)
    void resetIdSerial();
}

方案3：纯Java实现（无数据库语法依赖，跨数据库兼容） #

如果需要兼容不同数据库、不想依赖MySQL专属的变量语法，可以用Java代码实现赋值逻辑，适合数据量不大的场景：

@Transactional
public void resetNewsModId(NewsModRepository repository) {
    // 查询所有符合条件的记录，可自定义排序规则保证id顺序符合预期
    List<NewsMod> list = repository.findByCanonicalGreaterThan(0, Sort.by(Sort.Direction.ASC, "createTime"));
    // 遍历赋值id
    for (int i = 0; i < list.size(); i++) {
        list.get(i).setId(i + 1L);
    }
    // 批量保存
    repository.saveAll(list);
    // 重置自增主键
    jdbcTemplate.execute("ALTER TABLE news_mod AUTO_INCREMENT = 1");
}

注意事项 #

• 操作前务必备份全表数据，主键重赋值属于高危操作
• UPDATE语句建议补充ORDER BY子句指定排序规则，避免id赋值顺序不符合预期
• 若存在其他表关联news_mod.id的外键约束，需要先临时关闭外键约束或更新关联数据，避免更新失败

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