176 lines
4.3 KiB
Markdown
176 lines
4.3 KiB
Markdown
# 数据迁移执行指南
|
||
|
||
## ⚠️ 重要提示
|
||
|
||
日志中显示格式转换警告，说明数据库中还有旧数据（百分比形式，如30表示30%）。需要执行数据迁移脚本，将数据统一转换为比例形式（0.30表示30%）。
|
||
|
||
---
|
||
|
||
## 🔧 执行步骤
|
||
|
||
### 步骤1：备份数据库（强烈推荐）
|
||
|
||
```bash
|
||
# 备份数据库
|
||
mysqldump -u username -p database_name > backup_$(date +%Y%m%d_%H%M%S).sql
|
||
```
|
||
|
||
---
|
||
|
||
### 步骤2：执行数据迁移脚本
|
||
|
||
```bash
|
||
# 执行SQL迁移脚本
|
||
mysql -u username -p database_name < backend/database/migrate_percent_configs_to_ratio.sql
|
||
```
|
||
|
||
**或者直接在MySQL客户端执行**：
|
||
```sql
|
||
-- 1. 备份表
|
||
CREATE TABLE IF NOT EXISTS trading_config_backup_20260126 AS
|
||
SELECT * FROM trading_config;
|
||
|
||
CREATE TABLE IF NOT EXISTS global_strategy_config_backup_20260126 AS
|
||
SELECT * FROM global_strategy_config;
|
||
|
||
-- 2. 迁移 trading_config 表
|
||
UPDATE trading_config
|
||
SET config_value = CAST(config_value AS DECIMAL(10, 4)) / 100.0
|
||
WHERE config_key IN (
|
||
'TRAILING_STOP_ACTIVATION',
|
||
'TRAILING_STOP_PROTECT',
|
||
'MIN_VOLATILITY',
|
||
'TAKE_PROFIT_PERCENT',
|
||
'STOP_LOSS_PERCENT',
|
||
'MIN_STOP_LOSS_PRICE_PCT',
|
||
'MIN_TAKE_PROFIT_PRICE_PCT',
|
||
'FIXED_RISK_PERCENT',
|
||
'MAX_POSITION_PERCENT',
|
||
'MAX_TOTAL_POSITION_PERCENT',
|
||
'MIN_POSITION_PERCENT'
|
||
)
|
||
AND config_type = 'number'
|
||
AND CAST(config_value AS DECIMAL(10, 4)) > 1;
|
||
|
||
-- 3. 迁移 global_strategy_config 表
|
||
UPDATE global_strategy_config
|
||
SET config_value = CAST(config_value AS DECIMAL(10, 4)) / 100.0
|
||
WHERE config_key IN (
|
||
'TRAILING_STOP_ACTIVATION',
|
||
'TRAILING_STOP_PROTECT',
|
||
'MIN_VOLATILITY',
|
||
'TAKE_PROFIT_PERCENT',
|
||
'STOP_LOSS_PERCENT',
|
||
'MIN_STOP_LOSS_PRICE_PCT',
|
||
'MIN_TAKE_PROFIT_PRICE_PCT',
|
||
'FIXED_RISK_PERCENT',
|
||
'MAX_POSITION_PERCENT',
|
||
'MAX_TOTAL_POSITION_PERCENT',
|
||
'MIN_POSITION_PERCENT'
|
||
)
|
||
AND config_type = 'number'
|
||
AND CAST(config_value AS DECIMAL(10, 4)) > 1;
|
||
```
|
||
|
||
---
|
||
|
||
### 步骤3：验证迁移结果
|
||
|
||
```sql
|
||
-- 检查是否还有>1的百分比配置项（应该返回0行）
|
||
SELECT 'trading_config' as table_name, config_key, config_value, account_id
|
||
FROM trading_config
|
||
WHERE config_key IN (
|
||
'TRAILING_STOP_ACTIVATION',
|
||
'TRAILING_STOP_PROTECT',
|
||
'MIN_VOLATILITY',
|
||
'TAKE_PROFIT_PERCENT',
|
||
'STOP_LOSS_PERCENT'
|
||
)
|
||
AND config_type = 'number'
|
||
AND CAST(config_value AS DECIMAL(10, 4)) > 1
|
||
UNION ALL
|
||
SELECT 'global_strategy_config' as table_name, config_key, config_value, NULL as account_id
|
||
FROM global_strategy_config
|
||
WHERE config_key IN (
|
||
'TRAILING_STOP_ACTIVATION',
|
||
'TRAILING_STOP_PROTECT',
|
||
'MIN_VOLATILITY',
|
||
'TAKE_PROFIT_PERCENT',
|
||
'STOP_LOSS_PERCENT'
|
||
)
|
||
AND config_type = 'number'
|
||
AND CAST(config_value AS DECIMAL(10, 4)) > 1;
|
||
```
|
||
|
||
**预期结果**：应该返回0行（没有>1的值）
|
||
|
||
---
|
||
|
||
### 步骤4：清除Redis缓存
|
||
|
||
```bash
|
||
# 清除Redis缓存，让系统重新加载配置
|
||
redis-cli DEL "config:trading_config:*"
|
||
redis-cli DEL "config:global_strategy_config:*"
|
||
```
|
||
|
||
---
|
||
|
||
### 步骤5：重启服务
|
||
|
||
```bash
|
||
# 重启后端服务
|
||
supervisorctl restart backend
|
||
|
||
# 重启交易进程
|
||
supervisorctl restart auto_sys_acc1 auto_sys_acc2 auto_sys_acc3 auto_sys_acc4
|
||
```
|
||
|
||
---
|
||
|
||
## ✅ 验证
|
||
|
||
### 检查日志
|
||
|
||
迁移完成后，日志中不应该再出现格式转换警告：
|
||
|
||
```bash
|
||
# 查看日志，确认没有格式转换警告
|
||
tail -f /www/wwwroot/autosys_new/logs/trading_*.log | grep "配置值格式转换"
|
||
```
|
||
|
||
**预期结果**：不应该再看到格式转换警告
|
||
|
||
---
|
||
|
||
### 检查配置值
|
||
|
||
**迁移前**：
|
||
- `TRAILING_STOP_ACTIVATION`: 30（百分比形式）
|
||
- `TRAILING_STOP_PROTECT`: 15（百分比形式）
|
||
- `MIN_VOLATILITY`: 3（百分比形式）
|
||
|
||
**迁移后**：
|
||
- `TRAILING_STOP_ACTIVATION`: 0.30（比例形式）
|
||
- `TRAILING_STOP_PROTECT`: 0.15（比例形式）
|
||
- `MIN_VOLATILITY`: 0.03（比例形式）
|
||
|
||
---
|
||
|
||
## 📝 注意事项
|
||
|
||
1. **备份数据库**：执行迁移前一定要备份数据库
|
||
2. **验证结果**：迁移后验证是否还有>1的值
|
||
3. **清除缓存**：迁移后清除Redis缓存
|
||
4. **重启服务**：迁移后重启服务，让新配置生效
|
||
|
||
---
|
||
|
||
## 🎯 迁移后的效果
|
||
|
||
- ✅ 数据库中统一存储比例形式（0.30）
|
||
- ✅ 前端直接显示小数（0.30），不带%符号
|
||
- ✅ 后端直接使用（0.30），不需要转换
|
||
- ✅ 日志中不再出现格式转换警告
|