127 lines
3.7 KiB
Markdown
127 lines
3.7 KiB
Markdown
# JSON 语法错误修复
|
||
|
||
## 问题描述
|
||
|
||
在执行模拟数据插入时遇到 PostgreSQL JSON 语法错误：
|
||
```
|
||
ERROR: 22P02: invalid input syntax for type json
|
||
LINE 252: WHEN 'share' THEN '{"platform": "weibo", "shared_at": "' || NOW() || '"}'::jsonb
|
||
DETAIL: Token ""}" is invalid.
|
||
CONTEXT: JSON data, line 1: "}
|
||
```
|
||
|
||
## 问题根源
|
||
|
||
错误发生在用户交互数据插入的 CASE 语句中，尝试通过字符串拼接来构建包含时间戳的 JSON：
|
||
|
||
### 问题代码
|
||
```sql
|
||
CASE interaction_type
|
||
WHEN 'share' THEN '{"platform": "weibo", "shared_at": "' || NOW() || '"}'::jsonb
|
||
WHEN 'favorite' THEN '{"folder": "我的收藏", "added_at": "' || NOW() || '"}'::jsonb
|
||
ELSE '{}'::jsonb
|
||
END
|
||
```
|
||
|
||
### 问题分析
|
||
1. **时间戳格式问题**: `NOW()` 返回的时间戳格式可能包含特殊字符
|
||
2. **JSON 转义问题**: 直接字符串拼接没有正确处理 JSON 转义
|
||
3. **语法错误**: 生成的字符串不是有效的 JSON 格式
|
||
|
||
## 解决方案
|
||
|
||
使用 PostgreSQL 的 `jsonb_build_object()` 函数来安全地构建 JSONB 对象：
|
||
|
||
### 修复后的代码
|
||
```sql
|
||
CASE interaction_type
|
||
WHEN 'share' THEN jsonb_build_object('platform', 'weibo', 'shared_at', NOW())
|
||
WHEN 'favorite' THEN jsonb_build_object('folder', '我的收藏', 'added_at', NOW())
|
||
ELSE '{}'::jsonb
|
||
END
|
||
```
|
||
|
||
## 技术优势
|
||
|
||
### 使用 `jsonb_build_object()` 的优势：
|
||
1. **自动转义**: 函数会自动处理 JSON 中的特殊字符和转义
|
||
2. **类型安全**: 正确处理不同数据类型（字符串、时间戳等）
|
||
3. **语法保证**: 生成的始终是有效的 JSONB 格式
|
||
4. **性能优化**: 原生函数比字符串拼接更高效
|
||
|
||
### 修复前后对比
|
||
|
||
**修复前（❌ 错误）：**
|
||
```sql
|
||
-- 字符串拼接方式，容易出错
|
||
'{"platform": "weibo", "shared_at": "' || NOW() || '"}'::jsonb
|
||
```
|
||
|
||
**修复后（✅ 正确）：**
|
||
```sql
|
||
-- 使用专用函数，安全可靠
|
||
jsonb_build_object('platform', 'weibo', 'shared_at', NOW())
|
||
```
|
||
|
||
## 生成的数据示例
|
||
|
||
修复后，`interaction_data` 字段将包含正确格式的 JSONB 数据：
|
||
|
||
**分享数据：**
|
||
```json
|
||
{
|
||
"platform": "weibo",
|
||
"shared_at": "2025-07-07T10:30:45.123Z"
|
||
}
|
||
```
|
||
|
||
**收藏数据：**
|
||
```json
|
||
{
|
||
"folder": "我的收藏",
|
||
"added_at": "2025-07-07T10:30:45.123Z"
|
||
}
|
||
```
|
||
|
||
## PostgreSQL JSONB 函数说明
|
||
|
||
### `jsonb_build_object()`
|
||
- **语法**: `jsonb_build_object(key1, value1, key2, value2, ...)`
|
||
- **功能**: 从键值对列表构建 JSONB 对象
|
||
- **优势**: 自动处理类型转换和 JSON 转义
|
||
|
||
### 其他相关函数
|
||
- `jsonb_build_array()`: 构建 JSONB 数组
|
||
- `to_jsonb()`: 将任意值转换为 JSONB
|
||
- `jsonb_set()`: 设置 JSONB 对象中的值
|
||
|
||
## 最佳实践
|
||
|
||
1. **避免字符串拼接**: 不要用 `||` 操作符拼接 JSON 字符串
|
||
2. **使用原生函数**: 优先使用 PostgreSQL 的 JSONB 函数
|
||
3. **处理特殊字符**: 让数据库自动处理转义和特殊字符
|
||
4. **类型安全**: 让函数处理不同数据类型的转换
|
||
|
||
## 验证结果
|
||
|
||
- ✅ **SQL 语法检查**: 通过 PostgreSQL 语法验证
|
||
- ✅ **JSON 格式**: 生成有效的 JSONB 数据
|
||
- ✅ **时间戳处理**: 正确处理 NOW() 函数返回值
|
||
- ✅ **中文支持**: 正确处理中文字符
|
||
|
||
## 相关资源
|
||
|
||
- [PostgreSQL JSONB 函数文档](https://www.postgresql.org/docs/current/functions-json.html)
|
||
- [JSONB vs JSON 性能对比](https://www.postgresql.org/docs/current/datatype-json.html)
|
||
- [JSON 数据类型最佳实践](https://www.postgresql.org/docs/current/json-faq.html)
|
||
|
||
## 文件状态
|
||
|
||
- **文件**: `h:\blews\akmon\video_system_database.sql`
|
||
- **修复行数**: 第 990-995 行
|
||
- **状态**: ✅ 已修复，JSON 语法正确
|
||
- **更新时间**: 2025-07-07
|
||
|
||
---
|
||
*JSON 字符串拼接问题已成功修复，现在使用 PostgreSQL 原生 JSONB 函数来安全构建 JSON 数据。*
|