package com.rongyichuang.tools;
|
|
import org.junit.jupiter.api.Test;
|
|
import java.sql.Connection;
|
import java.sql.DriverManager;
|
import java.sql.PreparedStatement;
|
|
public class StageIdFixTest {
|
|
// 与 application.yml 保持一致，并加长超时
|
private static final String URL = "jdbc:mysql://139.155.104.10:3306/ryc?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&connectTimeout=60000&socketTimeout=60000";
|
private static final String USER = "ryc";
|
private static final String PASS = "KiYap3E8X8RLcM6T";
|
|
@Test
|
public void fixStageId() throws Exception {
|
try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
|
conn.setAutoCommit(false);
|
|
// 规则：按每个比赛选择其第一个阶段（最小ID）；若无阶段，则回填为比赛自身 activity_id
|
String sql = """
|
UPDATE t_avtivity_player ap
|
LEFT JOIN (
|
SELECT child.pid AS activity_id, MIN(child.id) AS stage_id
|
FROM t_activity child
|
WHERE child.pid IS NOT NULL AND child.pid <> 0
|
GROUP BY child.pid
|
) s ON s.activity_id = ap.activity_id
|
SET ap.stage_id = COALESCE(s.stage_id, ap.activity_id)
|
""";
|
|
try (PreparedStatement ps = conn.prepareStatement(sql)) {
|
int affected = ps.executeUpdate();
|
conn.commit();
|
System.out.println("[StageIdFixTest] 更新完成，受影响行数=" + affected);
|
} catch (Exception ex) {
|
conn.rollback();
|
throw ex;
|
}
|
}
|
}
|
}
|
