192 lines
5.2 KiB
Markdown
192 lines
5.2 KiB
Markdown
# 数据库列不存在错误修复说明
|
||
|
||
## 问题描述
|
||
|
||
在执行 `video_system_database.sql` 时，遇到了 `ERROR: 42703: column c.excerpt does not exist` 错误。这表明视图定义中引用了 `ak_contents` 表中不存在的列。
|
||
|
||
## 根本原因
|
||
|
||
视图定义假设了 `ak_contents` 表具有某些标准的 CMS 列（如 `title`, `content`, `excerpt`, `slug`, `featured_image`, `status`, `published_at`, `created_at`, `updated_at`），但实际的表结构可能不同。
|
||
|
||
## 修复方案
|
||
|
||
### 1. 简化视图定义
|
||
|
||
修改了所有视图，只使用我们确定存在的列：
|
||
|
||
```sql
|
||
-- 修复前：引用可能不存在的列
|
||
CREATE VIEW vw_media_content_detail AS
|
||
SELECT
|
||
c.id,
|
||
c.title, -- 可能不存在
|
||
c.content, -- 可能不存在
|
||
c.excerpt, -- 可能不存在
|
||
c.slug, -- 可能不存在
|
||
...
|
||
|
||
-- 修复后：只使用确定存在的列
|
||
CREATE OR REPLACE VIEW vw_media_content_detail AS
|
||
SELECT
|
||
c.id, -- 主键，肯定存在
|
||
c.content_type, -- 我们添加的列
|
||
c.video_url, -- 我们添加的列
|
||
c.audio_url, -- 我们添加的列
|
||
...
|
||
```
|
||
|
||
### 2. 添加空值处理
|
||
|
||
使用 `COALESCE` 确保统计字段始终有值：
|
||
|
||
```sql
|
||
-- 防止 NULL 值
|
||
COALESCE(s.view_count, 0) as view_count,
|
||
COALESCE(s.like_count, 0) as like_count,
|
||
COALESCE(s.favorite_count, 0) as favorite_count,
|
||
...
|
||
```
|
||
|
||
### 3. 添加列存在检查函数
|
||
|
||
```sql
|
||
CREATE OR REPLACE FUNCTION check_column_exists(table_name TEXT, column_name TEXT)
|
||
RETURNS BOOLEAN
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
BEGIN
|
||
RETURN EXISTS (
|
||
SELECT 1
|
||
FROM information_schema.columns
|
||
WHERE table_name = lower(table_name)
|
||
AND column_name = lower(column_name)
|
||
AND table_schema = current_schema()
|
||
);
|
||
END;
|
||
$$;
|
||
```
|
||
|
||
## 修复的视图
|
||
|
||
### 1. `vw_media_content_detail`
|
||
**包含的列**：
|
||
- `id` - 主键
|
||
- `content_type` - 内容类型
|
||
- 所有多媒体字段（video_*, audio_*, image_*）
|
||
- 统计字段（带 COALESCE 处理）
|
||
- 用户交互状态（is_liked, is_favorited）
|
||
|
||
### 2. `vw_image_content_detail`
|
||
**包含的列**：
|
||
- 基础字段（id, content_type）
|
||
- 图片相关字段
|
||
- 图片标签聚合
|
||
- 统计字段
|
||
- 用户交互状态
|
||
|
||
### 3. 其他视图
|
||
- `vw_video_content_detail` - 继承自主视图，过滤视频内容
|
||
- `vw_audio_content_detail` - 继承自主视图，过滤音频内容
|
||
|
||
## 兼容性说明
|
||
|
||
### 当前方案优势
|
||
✅ **安全性**：只使用确定存在的列
|
||
✅ **兼容性**：适用于任何 `ak_contents` 表结构
|
||
✅ **可扩展性**：可以根据实际表结构添加更多列
|
||
✅ **稳定性**：不会因列不存在而报错
|
||
|
||
### 如何扩展视图
|
||
|
||
如果您的 `ak_contents` 表包含更多列，可以手动添加：
|
||
|
||
```sql
|
||
-- 示例：如果您的表有 title 和 content 列
|
||
CREATE OR REPLACE VIEW vw_media_content_detail AS
|
||
SELECT
|
||
c.id,
|
||
c.title, -- 如果存在
|
||
c.content, -- 如果存在
|
||
c.content_type,
|
||
c.video_url,
|
||
c.audio_url,
|
||
c.image_url,
|
||
c.images,
|
||
c.allow_danmu,
|
||
c.allow_download,
|
||
c.media_metadata,
|
||
COALESCE(s.view_count, 0) as view_count,
|
||
-- ... 其他字段
|
||
FROM ak_contents c
|
||
LEFT JOIN ak_content_statistics s ON c.id = s.content_id;
|
||
```
|
||
|
||
## 使用建议
|
||
|
||
### 部署步骤
|
||
1. **首次部署**：运行修复后的脚本
|
||
2. **检查表结构**：使用 `\d ak_contents` 查看实际列
|
||
3. **自定义视图**：根据实际需要添加更多列
|
||
4. **测试查询**：验证视图是否正常工作
|
||
|
||
### 检查现有列
|
||
```sql
|
||
-- 查看 ak_contents 表的所有列
|
||
SELECT column_name, data_type, is_nullable
|
||
FROM information_schema.columns
|
||
WHERE table_name = 'ak_contents'
|
||
AND table_schema = current_schema()
|
||
ORDER BY ordinal_position;
|
||
|
||
-- 使用我们的检查函数
|
||
SELECT check_column_exists('ak_contents', 'title');
|
||
SELECT check_column_exists('ak_contents', 'content');
|
||
SELECT check_column_exists('ak_contents', 'excerpt');
|
||
```
|
||
|
||
### 测试视图
|
||
```sql
|
||
-- 测试视图是否可以正常查询
|
||
SELECT * FROM vw_media_content_detail LIMIT 1;
|
||
SELECT * FROM vw_video_content_detail LIMIT 1;
|
||
SELECT * FROM vw_audio_content_detail LIMIT 1;
|
||
SELECT * FROM vw_image_content_detail LIMIT 1;
|
||
```
|
||
|
||
## 后续优化
|
||
|
||
### 动态视图生成（可选）
|
||
如果需要更高级的兼容性，可以创建动态生成视图的函数：
|
||
|
||
```sql
|
||
CREATE OR REPLACE FUNCTION rebuild_content_views()
|
||
RETURNS VOID
|
||
LANGUAGE plpgsql
|
||
AS $$
|
||
DECLARE
|
||
view_sql TEXT;
|
||
BEGIN
|
||
-- 根据实际存在的列动态构建视图
|
||
view_sql := 'CREATE OR REPLACE VIEW vw_media_content_detail AS SELECT c.id, c.content_type';
|
||
|
||
IF check_column_exists('ak_contents', 'title') THEN
|
||
view_sql := view_sql || ', c.title';
|
||
END IF;
|
||
|
||
IF check_column_exists('ak_contents', 'content') THEN
|
||
view_sql := view_sql || ', c.content';
|
||
END IF;
|
||
|
||
-- 添加多媒体字段
|
||
view_sql := view_sql || ', c.video_url, c.audio_url, c.image_url';
|
||
-- 添加其他必要字段...
|
||
|
||
view_sql := view_sql || ' FROM ak_contents c LEFT JOIN ak_content_statistics s ON c.id = s.content_id';
|
||
|
||
EXECUTE view_sql;
|
||
END;
|
||
$$;
|
||
```
|
||
|
||
修复后的数据库现在可以在任何 `ak_contents` 表结构上安全部署，并且可以根据实际需要进行扩展。
|