package com.rongyichuang.tools;
|
|
import org.junit.jupiter.api.Test;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.boot.test.context.SpringBootTest;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
|
import java.util.List;
|
import java.util.Map;
|
|
@SpringBootTest
|
public class ActivityPlayerRegionUpdater {
|
|
@Autowired
|
private JdbcTemplate jdbcTemplate;
|
|
@Test
|
public void addRegionFieldToActivityPlayer() {
|
System.out.println("=== 为 t_activity_player 表添加 region_id 字段 ===");
|
|
try {
|
// 1. 检查字段是否已存在
|
String checkColumnSql = """
|
SELECT COLUMN_NAME
|
FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 't_activity_player'
|
AND COLUMN_NAME = 'region_id'
|
""";
|
|
List<Map<String, Object>> existingColumns = jdbcTemplate.queryForList(checkColumnSql);
|
|
if (!existingColumns.isEmpty()) {
|
System.out.println("✅ region_id 字段已存在，无需添加");
|
return;
|
}
|
|
// 2. 添加 region_id 字段
|
String addColumnSql = """
|
ALTER TABLE t_activity_player
|
ADD COLUMN region_id bigint DEFAULT NULL COMMENT '区域ID，关联t_region表'
|
AFTER player_id
|
""";
|
|
jdbcTemplate.execute(addColumnSql);
|
System.out.println("✅ 成功添加 region_id 字段");
|
|
// 3. 添加外键约束
|
try {
|
String addForeignKeySql = """
|
ALTER TABLE t_activity_player
|
ADD CONSTRAINT fk_activity_player_region
|
FOREIGN KEY (region_id) REFERENCES t_region(id)
|
ON DELETE SET NULL ON UPDATE CASCADE
|
""";
|
|
jdbcTemplate.execute(addForeignKeySql);
|
System.out.println("✅ 成功添加外键约束");
|
} catch (Exception e) {
|
System.out.println("⚠️ 外键约束添加失败（可能已存在）: " + e.getMessage());
|
}
|
|
// 4. 验证表结构
|
String describeTableSql = """
|
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT
|
FROM INFORMATION_SCHEMA.COLUMNS
|
WHERE TABLE_SCHEMA = DATABASE()
|
AND TABLE_NAME = 't_activity_player'
|
ORDER BY ORDINAL_POSITION
|
""";
|
|
List<Map<String, Object>> columns = jdbcTemplate.queryForList(describeTableSql);
|
System.out.println("\n=== t_activity_player 表结构 ===");
|
for (Map<String, Object> column : columns) {
|
System.out.println(String.format("%-20s %-15s %-10s %-15s %s",
|
column.get("COLUMN_NAME"),
|
column.get("DATA_TYPE"),
|
column.get("IS_NULLABLE"),
|
column.get("COLUMN_DEFAULT"),
|
column.get("COLUMN_COMMENT")
|
));
|
}
|
|
} catch (Exception e) {
|
System.err.println("❌ 操作失败: " + e.getMessage());
|
e.printStackTrace();
|
}
|
|
System.out.println("\n✅ region_id 字段添加完成！");
|
}
|
}
|
