108 lines
3.4 KiB
SQL
108 lines
3.4 KiB
SQL
-- 邀请码功能完整修复脚本
|
||
-- 执行时间：2026-02-03
|
||
-- 说明：这个脚本会检查并修复所有邀请码相关的问题
|
||
|
||
USE fastadmin;
|
||
|
||
-- ========================================
|
||
-- 第 1 步：修复字段类型
|
||
-- ========================================
|
||
|
||
-- 修复 invited_by 字段类型（从 int 改为 varchar(10)）
|
||
ALTER TABLE `nf_user`
|
||
MODIFY COLUMN `invited_by` VARCHAR(10) DEFAULT NULL COMMENT '被谁邀请（邀请码）';
|
||
|
||
-- 修复 invite_reward_total 字段类型（从 int 改为 decimal(10,2)）
|
||
ALTER TABLE `nf_user`
|
||
MODIFY COLUMN `invite_reward_total` DECIMAL(10,2) DEFAULT 0.00 COMMENT '邀请奖励总额';
|
||
|
||
-- 确保 invite_code 字段正确
|
||
ALTER TABLE `nf_user`
|
||
MODIFY COLUMN `invite_code` VARCHAR(10) DEFAULT NULL COMMENT '邀请码';
|
||
|
||
-- 确保 invite_count 字段正确
|
||
ALTER TABLE `nf_user`
|
||
MODIFY COLUMN `invite_count` INT(11) DEFAULT 0 COMMENT '邀请人数';
|
||
|
||
SELECT '✅ 步骤 1：字段类型修复完成' AS '进度';
|
||
|
||
-- ========================================
|
||
-- 第 2 步：检查并添加索引
|
||
-- ========================================
|
||
|
||
-- 检查 invite_code 唯一索引
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists
|
||
FROM INFORMATION_SCHEMA.STATISTICS
|
||
WHERE TABLE_SCHEMA = 'fastadmin'
|
||
AND TABLE_NAME = 'nf_user'
|
||
AND INDEX_NAME = 'idx_invite_code';
|
||
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `nf_user` ADD UNIQUE INDEX `idx_invite_code` (`invite_code`)',
|
||
'SELECT ''idx_invite_code 索引已存在'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查 invited_by 索引
|
||
SET @index_exists = 0;
|
||
SELECT COUNT(*) INTO @index_exists
|
||
FROM INFORMATION_SCHEMA.STATISTICS
|
||
WHERE TABLE_SCHEMA = 'fastadmin'
|
||
AND TABLE_NAME = 'nf_user'
|
||
AND INDEX_NAME = 'idx_invited_by';
|
||
|
||
SET @sql = IF(@index_exists = 0,
|
||
'ALTER TABLE `nf_user` ADD INDEX `idx_invited_by` (`invited_by`)',
|
||
'SELECT ''idx_invited_by 索引已存在'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
SELECT '✅ 步骤 2：索引检查完成' AS '进度';
|
||
|
||
-- ========================================
|
||
-- 第 3 步：验证修复结果
|
||
-- ========================================
|
||
|
||
SELECT '========================================' AS '';
|
||
SELECT '邀请码字段信息' AS '';
|
||
SELECT '========================================' AS '';
|
||
|
||
SELECT
|
||
COLUMN_NAME AS '字段名',
|
||
COLUMN_TYPE AS '类型',
|
||
IS_NULLABLE AS '可空',
|
||
COLUMN_DEFAULT AS '默认值',
|
||
COLUMN_COMMENT AS '注释'
|
||
FROM
|
||
INFORMATION_SCHEMA.COLUMNS
|
||
WHERE
|
||
TABLE_SCHEMA = 'fastadmin'
|
||
AND TABLE_NAME = 'nf_user'
|
||
AND COLUMN_NAME IN ('invite_code', 'invited_by', 'invite_count', 'invite_reward_total')
|
||
ORDER BY ORDINAL_POSITION;
|
||
|
||
SELECT '========================================' AS '';
|
||
SELECT '索引信息' AS '';
|
||
SELECT '========================================' AS '';
|
||
|
||
SELECT
|
||
INDEX_NAME AS '索引名',
|
||
COLUMN_NAME AS '列名',
|
||
NON_UNIQUE AS '非唯一',
|
||
INDEX_TYPE AS '索引类型'
|
||
FROM
|
||
INFORMATION_SCHEMA.STATISTICS
|
||
WHERE
|
||
TABLE_SCHEMA = 'fastadmin'
|
||
AND TABLE_NAME = 'nf_user'
|
||
AND INDEX_NAME IN ('idx_invite_code', 'idx_invited_by')
|
||
ORDER BY INDEX_NAME, SEQ_IN_INDEX;
|
||
|
||
SELECT '========================================' AS '';
|
||
SELECT '✅ 邀请码功能修复完成！' AS '状态';
|
||
SELECT '请重启 Python 服务以使更改生效' AS '提示';
|
||
SELECT '========================================' AS '';
|