消息系统数据库表已存在问题的解决方案

当看到错误 ERROR: 42P07: relation "ak_message_types" already exists 时，说明数据库表已经存在。这里提供几种解决方案：

方案一：检查现有表结构（推荐）

首先检查现有表是否完整，运行以下查询：

-- 检查消息系统相关表
SELECT table_name, table_type 
FROM information_schema.tables 
WHERE table_schema = 'public' 
AND table_name LIKE 'ak_message%'
ORDER BY table_name;

-- 检查 ak_message_types 表的列
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns 
WHERE table_schema = 'public' 
AND table_name = 'ak_message_types'
ORDER BY ordinal_position;

-- 查看现有消息类型数据
SELECT code, name, priority, is_system, is_active 
FROM ak_message_types 
ORDER BY priority DESC;

方案二：更新现有数据（如果表结构完整）

如果表结构完整，只需要更新/插入消息类型数据：

-- 安全地插入或更新消息类型数据
INSERT INTO ak_message_types (code, name, description, priority, is_system, is_active, retention_days) 
VALUES
    ('system', '系统消息', '系统自动发送的重要通知', 100, true, true, 90),
    ('training', '训练提醒', '训练计划和完成情况提醒', 70, true, true, 30),
    ('chat', '即时消息', '用户间的实时聊天消息', 90, false, true, 7)
ON CONFLICT (code) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    priority = EXCLUDED.priority,
    is_system = EXCLUDED.is_system,
    is_active = EXCLUDED.is_active,
    retention_days = EXCLUDED.retention_days,
    updated_at = now();

方案三：删除现有表重新创建（危险，会丢失数据）

⚠️ 警告：此操作会删除所有现有数据，请谨慎使用！

-- 删除相关表（注意外键依赖顺序）
DROP TABLE IF EXISTS ak_message_recipients CASCADE;
DROP TABLE IF EXISTS ak_message_group_members CASCADE;
DROP TABLE IF EXISTS ak_message_groups CASCADE;
DROP TABLE IF EXISTS ak_messages CASCADE;
DROP TABLE IF EXISTS ak_message_types CASCADE;

-- 然后重新运行完整的 message_system.sql 脚本

方案四：检查缺失的表（推荐）

如果只有部分表存在，创建缺失的表：

-- 检查所有需要的表是否存在
SELECT 
    'ak_message_types' as table_name,
    CASE WHEN EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ak_message_types') 
         THEN '✓ 存在' ELSE '✗ 不存在' END as status
UNION ALL
SELECT 
    'ak_messages' as table_name,
    CASE WHEN EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ak_messages') 
         THEN '✓ 存在' ELSE '✗ 不存在' END as status
UNION ALL
SELECT 
    'ak_message_recipients' as table_name,
    CASE WHEN EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ak_message_recipients') 
         THEN '✓ 存在' ELSE '✗ 不存在' END as status
UNION ALL
SELECT 
    'ak_message_groups' as table_name,
    CASE WHEN EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ak_message_groups') 
         THEN '✓ 存在' ELSE '✗ 不存在' END as status
UNION ALL
SELECT 
    'ak_message_group_members' as table_name,
    CASE WHEN EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'ak_message_group_members') 
         THEN '✓ 存在' ELSE '✗ 不存在' END as status;

测试数据完整性：

运行以下查询验证系统是否正常：

-- 测试查询：获取消息类型统计
SELECT 
    mt.code,
    mt.name,
    mt.priority,
    COUNT(m.id) as message_count
FROM ak_message_types mt
LEFT JOIN ak_messages m ON mt.id = m.message_type_id
GROUP BY mt.id, mt.code, mt.name, mt.priority
ORDER BY mt.priority DESC;

-- 测试查询：检查表之间的关联
SELECT 
    m.title,
    mt.name as message_type,
    mr.status,
    m.created_at
FROM ak_messages m
JOIN ak_message_types mt ON m.message_type_id = mt.id
LEFT JOIN ak_message_recipients mr ON m.id = mr.message_id
ORDER BY m.created_at DESC
LIMIT 5;

如果使用 Supabase：

在 Supabase 的 SQL Editor 中：

先运行检查命令了解现状
根据检查结果选择相应的处理方案
确保 RLS (Row Level Security) 策略正确配置

最终验证：

确保消息系统的前端代码能够正常连接和操作数据库：

检查 utils/msgSystemInit.uts 中的 Supabase 配置
运行 utils/msgSystemTest.uts 中的集成测试
在消息页面测试基本功能（列表、发送、接收）

5.0 KiB Raw Permalink Blame History Unescape Escape