148 lines
5.0 KiB
Markdown
148 lines
5.0 KiB
Markdown
# 消息系统数据库表已存在问题的解决方案
|
||
|
||
当看到错误 `ERROR: 42P07: relation "ak_message_types" already exists` 时，说明数据库表已经存在。这里提供几种解决方案：
|
||
|
||
## 方案一：检查现有表结构（推荐）
|
||
|
||
首先检查现有表是否完整，运行以下查询：
|
||
|
||
```sql
|
||
-- 检查消息系统相关表
|
||
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;
|
||
```
|
||
|
||
## 方案二：更新现有数据（如果表结构完整）
|
||
|
||
如果表结构完整，只需要更新/插入消息类型数据：
|
||
|
||
```sql
|
||
-- 安全地插入或更新消息类型数据
|
||
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();
|
||
```
|
||
|
||
## 方案三：删除现有表重新创建（危险，会丢失数据）
|
||
|
||
⚠️ **警告：此操作会删除所有现有数据，请谨慎使用！**
|
||
|
||
```sql
|
||
-- 删除相关表（注意外键依赖顺序）
|
||
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 脚本
|
||
```
|
||
|
||
## 方案四：检查缺失的表（推荐）
|
||
|
||
如果只有部分表存在，创建缺失的表：
|
||
|
||
```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;
|
||
```
|
||
|
||
## 推荐的处理步骤：
|
||
|
||
1. **首先运行方案一的检查命令**，了解现有表的情况
|
||
2. **如果表结构完整**，运行方案二更新数据
|
||
3. **如果缺少某些表**，从原 `message_system.sql` 中提取缺失表的创建语句单独执行
|
||
4. **验证系统完整性**，确保所有表和数据都正确
|
||
|
||
## 测试数据完整性：
|
||
|
||
运行以下查询验证系统是否正常：
|
||
|
||
```sql
|
||
-- 测试查询：获取消息类型统计
|
||
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 中：
|
||
1. 先运行检查命令了解现状
|
||
2. 根据检查结果选择相应的处理方案
|
||
3. 确保 RLS (Row Level Security) 策略正确配置
|
||
|
||
## 最终验证：
|
||
|
||
确保消息系统的前端代码能够正常连接和操作数据库：
|
||
|
||
1. 检查 `utils/msgSystemInit.uts` 中的 Supabase 配置
|
||
2. 运行 `utils/msgSystemTest.uts` 中的集成测试
|
||
3. 在消息页面测试基本功能（列表、发送、接收）
|