124 lines
3.9 KiB
SQL
124 lines
3.9 KiB
SQL
-- 修复邀请码功能 - 检查并添加缺失的字段和索引
|
||
-- 执行时间：2026-02-03
|
||
|
||
USE fastadmin;
|
||
|
||
-- 检查并添加 invite_code 字段（如果不存在）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'fastadmin'
|
||
AND TABLE_NAME = 'nf_user'
|
||
AND COLUMN_NAME = 'invite_code';
|
||
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `nf_user` ADD COLUMN `invite_code` VARCHAR(10) DEFAULT NULL COMMENT ''邀请码'' AFTER `vip_endtime`',
|
||
'SELECT ''invite_code 字段已存在'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 invited_by 字段（如果不存在）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'fastadmin'
|
||
AND TABLE_NAME = 'nf_user'
|
||
AND COLUMN_NAME = 'invited_by';
|
||
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `nf_user` ADD COLUMN `invited_by` VARCHAR(10) DEFAULT NULL COMMENT ''被谁邀请（邀请码）'' AFTER `invite_code`',
|
||
'SELECT ''invited_by 字段已存在'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 invite_count 字段（如果不存在）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'fastadmin'
|
||
AND TABLE_NAME = 'nf_user'
|
||
AND COLUMN_NAME = 'invite_count';
|
||
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `nf_user` ADD COLUMN `invite_count` INT(11) DEFAULT 0 COMMENT ''邀请人数'' AFTER `invited_by`',
|
||
'SELECT ''invite_count 字段已存在'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 invite_reward_total 字段（如果不存在）
|
||
SET @col_exists = 0;
|
||
SELECT COUNT(*) INTO @col_exists
|
||
FROM INFORMATION_SCHEMA.COLUMNS
|
||
WHERE TABLE_SCHEMA = 'fastadmin'
|
||
AND TABLE_NAME = 'nf_user'
|
||
AND COLUMN_NAME = 'invite_reward_total';
|
||
|
||
SET @sql = IF(@col_exists = 0,
|
||
'ALTER TABLE `nf_user` ADD COLUMN `invite_reward_total` DECIMAL(10,2) DEFAULT 0.00 COMMENT ''邀请奖励总额'' AFTER `invite_count`',
|
||
'SELECT ''invite_reward_total 字段已存在'' AS message');
|
||
PREPARE stmt FROM @sql;
|
||
EXECUTE stmt;
|
||
DEALLOCATE PREPARE stmt;
|
||
|
||
-- 检查并添加 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
|
||
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
|
||
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;
|