数据流文档：hk_songs_test → crawler_dev（三平台）

---

一、数据库说明

数据库	连接	用途
new_music_library	TencentDB (port 60209)	包含 hk_songs_test（源数据）
hikoon-data	AliCloud RDS (port 3307)	包含平台关联表 hk_song_platform、hk_song_and_record、hk_music_record 等
hikoon-data-spider	同上 AliCloud RDS (port 3307)	包含各平台爬虫原始表 media_tencent_songs 等
crawler_dev	AliCloud RDS PostgreSQL	目标写入库

---

二、关联链路

new_music_library.hk_songs_test.source_song_id
    = hikoon-data.hk_song_platform.id
    ↓ (hk_song_and_record.song_id = hk_song_platform.id)
hikoon-data.hk_song_and_record
    ↓ (record_id = hk_music_record.id)
hikoon-data.hk_music_record（一首歌对应多条，每个平台/版本各一条）
    ├── platform (1=QQ, 2=Kugou, 4=Netease)
    ├── platform_unique_key
    │       ├─ QQ     → media_tencent_songs.mid（字符串 mid）
    │       ├─ Kugou  → media_ku_gou_songs.id（数字 ID）
    │       └─ Netease → media_netease_songs.id（数字 ID）
    └── platform_mid
            ├─ QQ     → media_tencent_songs.id（数字 ID）
            └─ Kugou  → media_ku_gou_songs.hid（hash 字符串）

重要：hk_songs_test.source_table_name = 'hk_song_platform'，source_song_id 即为 hk_song_platform.id，是从 hk_songs_test 回溯到 hikoon-data 的唯一关联键。

一对多：一条 hk_songs_test 记录可能在 QQ、酷狗、网易都有对应的 hk_music_record，每个平台分别写入各自的 crawler_xx_songs 表。

---

三、QQ音乐（platform = 1）

Spider 核心关联：hk_music_record.platform_unique_key = media_tencent_songs.mid

crawler_dev.crawler_qqmusic_songs

目标字段	来源库	来源表.字段	备注
id	生成	—	UUID
platform_song_id	hikoon-data	hk_music_record.platform_mid	QQ 数字歌曲 ID，等同于 media_tencent_songs.id
mid	hikoon-data	hk_music_record.platform_unique_key	QQ mid 字符串，等同于 media_tencent_songs.mid
title / name	new_music_library	hk_songs_test.name
cover	new_music_library → OSS	hk_songs_test.cover_url	转换至 archive-dev OSS
duration	hikoon-data-spider	media_tencent_songs.duration	hk_songs_test 无此字段
lyric	hikoon-data-spider	media_tencent_songs.lyric	去掉 [mm:ss.xx] 时间戳后存储
composer_name	hikoon-data-spider	media_tencent_songs.composer_name	备选 hk_songs_test.composer
lyricist_name	hikoon-data-spider	media_tencent_songs.lyricist_name	备选 hk_songs_test.lyricist
url	new_music_library → OSS	hk_songs_test.audio_url	统一转换至 archive-dev OSS
lyric_url	new_music_library	hk_songs_test.lyrics_url	已是 archive-dev OSS .txt，直接使用
published_at	hikoon-data-spider	media_tencent_songs.published_at	备选 hk_songs_test.issue_time
platform_index_url	hikoon-data-spider	media_tencent_songs.platform_index_url
album_id	关联后写入	见下方 albums 说明	FK → crawler_qqmusic_albums.id
singers	关联后组装	见下方 singers 说明	JSONB 格式
album	hikoon-data-spider	media_tencent_albums.* 组成 JSON	冗余字段

crawler_dev.crawler_qqmusic_singers

关联路径：media_tencent_songs.id（by mid）→ media_tencent_singer_has_songs.song_id → media_tencent_singers（ON singer_id）

目标字段	来源表.字段
id	media_tencent_singers.id
mid	media_tencent_singers.mid
name	media_tencent_singers.name
avatar	media_tencent_singers.avatar → OSS
sex	media_tencent_singers.sex
area	media_tencent_singers.area
index	media_tencent_singers.index
intro	media_tencent_singers.intro
home_url	media_tencent_singers.home_url

crawler_qqmusic_songs.singers JSONB 格式：

[{"name": "歌手名", "singer_id": <media_tencent_singers.id>, "platform_singer_id": "<media_tencent_singers.mid>"}]

crawler_dev.crawler_qqmusic_albums

关联路径：media_tencent_songs.album_id → media_tencent_albums.id

目标字段	来源表.字段
id	media_tencent_albums.id
mid	media_tencent_albums.mid
cover	media_tencent_albums.cover → OSS
title	media_tencent_albums.title
intro	media_tencent_albums.intro
type	media_tencent_albums.type
company_id	media_tencent_albums.company_id
company	media_tencent_albums.company
is_owner	media_tencent_albums.is_owner
published_at	media_tencent_albums.published_at

---

四、酷狗（platform = 2）

Spider 核心关联：hk_music_record.platform_unique_key = media_ku_gou_songs.id

crawler_dev.crawler_kugou_songs

目标字段	来源库	来源表.字段	备注
id	生成	—	UUID
platform_song_id	hikoon-data	hk_music_record.platform_unique_key	酷狗数字歌曲 ID，等同于 media_ku_gou_songs.id
hash	hikoon-data	hk_music_record.platform_mid	酷狗 hash 字符串，等同于 media_ku_gou_songs.hid
album_audio_id	hikoon-data	hk_music_record.album_audio_id
title / name	new_music_library	hk_songs_test.name
cover	new_music_library → OSS	hk_songs_test.cover_url	转换至 archive-dev OSS
duration	hikoon-data-spider	media_ku_gou_songs.duration
lyric	hikoon-data-spider	media_ku_gou_songs.lyric	去时间戳后存储
composer_name	hikoon-data-spider	media_ku_gou_songs.composer_name
lyricist_name	hikoon-data-spider	media_ku_gou_songs.lyricist_name
url	new_music_library → OSS	hk_songs_test.audio_url	统一转换至 archive-dev OSS
lyric_url	new_music_library	hk_songs_test.lyrics_url	已是 archive-dev OSS .txt，直接使用
published_at	hikoon-data-spider	media_ku_gou_songs.published_at
platform_index_url	hikoon-data-spider	media_ku_gou_songs.platform_index_url
album_id	关联后写入	见 albums	FK → crawler_kugou_albums.id
singers	关联后组装	见 singers	JSONB

crawler_dev.crawler_kugou_singers

关联路径：media_ku_gou_songs.id → media_ku_gou_singer_has_songs.song_id → media_ku_gou_singers（ON singer_id）

目标字段	来源表.字段
id	media_ku_gou_singers.id
name	media_ku_gou_singers.name
avatar	media_ku_gou_singers.avatar → OSS
sex	media_ku_gou_singers.sex
area	media_ku_gou_singers.area
index	media_ku_gou_singers.index
intro	media_ku_gou_singers.intro
home_url	media_ku_gou_singers.home_url

crawler_kugou_songs.singers JSONB 格式：

[{"name": "歌手名", "singer_id": <media_ku_gou_singers.id>, "platform_singer_id": "<id字符串>"}]

crawler_dev.crawler_kugou_albums

关联路径：media_ku_gou_songs.album_id → media_ku_gou_albums.id

目标字段	来源表.字段
id	media_ku_gou_albums.id
cover	media_ku_gou_albums.cover → OSS
title	media_ku_gou_albums.title
intro	media_ku_gou_albums.intro
type	media_ku_gou_albums.type
company_id	media_ku_gou_albums.company_id
company	media_ku_gou_albums.company
is_owner	media_ku_gou_albums.is_owner
published_at	media_ku_gou_albums.published_at

---

五、网易云（platform = 4）

Spider 核心关联：hk_music_record.platform_unique_key = media_netease_songs.id

crawler_dev.crawler_netease_songs

目标字段	来源库	来源表.字段	备注
id	生成	—	UUID
platform_song_id	hikoon-data	hk_music_record.platform_unique_key	网易云数字歌曲 ID，等同于 media_netease_songs.id
title / name	new_music_library	hk_songs_test.name
cover	new_music_library → OSS	hk_songs_test.cover_url	转换至 archive-dev OSS
duration	hikoon-data-spider	media_netease_songs.duration
lyric	hikoon-data-spider	media_netease_songs.lyric	去时间戳后存储
composer_name	hikoon-data-spider	media_netease_songs.composer_name
lyricist_name	hikoon-data-spider	media_netease_songs.lyricist_name
url	new_music_library → OSS	hk_songs_test.audio_url	统一转换至 archive-dev OSS
lyric_url	new_music_library	hk_songs_test.lyrics_url	已是 archive-dev OSS .txt，直接使用
published_at	hikoon-data-spider	media_netease_songs.published_at
platform_index_url	hikoon-data-spider	media_netease_songs.platform_index_url
album_id	关联后写入	见 albums	FK → crawler_netease_albums.id
singers	关联后组装	见 singers	JSONB

crawler_dev.crawler_netease_singers

关联路径：media_netease_songs.id → media_netease_singer_has_songs.song_id → media_netease_singers（ON singer_id）

目标字段	来源表.字段
id	media_netease_singers.id
name	media_netease_singers.name
avatar	media_netease_singers.avatar → OSS
sex	media_netease_singers.sex
area	media_netease_singers.area
index	media_netease_singers.index
intro	media_netease_singers.intro
home_url	media_netease_singers.home_url

crawler_netease_songs.singers JSONB 格式：

[{"name": "歌手名", "singer_id": <media_netease_singers.id>, "platform_singer_id": "<id字符串>"}]

crawler_dev.crawler_netease_albums

关联路径：media_netease_songs.album_id → media_netease_albums.id

目标字段	来源表.字段
id	media_netease_albums.id
cover	media_netease_albums.cover → OSS
title	media_netease_albums.title
intro	media_netease_albums.intro
type	media_netease_albums.type
company_id	media_netease_albums.company_id
company	media_netease_albums.company
is_owner	media_netease_albums.is_owner
published_at	media_netease_albums.published_at

---

六、入库顺序与过滤条件

过滤条件

条件	实际字段	处理
平台记录存在	hk_music_record.platform_unique_key 不为空	关联后若无对应平台记录则跳过
标题不为空	hk_songs_test.name	空则跳过
音频URL不为空	hk_songs_test.audio_url	空则跳过；有则统一转换至 archive-dev OSS
歌手不为空	hk_songs_test.singer	文本字段，空则跳过；实际 singers 数据从 spider 关联表获取
歌词	hk_songs_test.lyrics_url	已是 archive-dev OSS .txt，直接写入 lyric_url；spider 的 lyric 字段去时间戳后写入 lyric 列

写入顺序（每首歌，每个平台）

1. singers — 按 id 做 ON CONFLICT DO NOTHING
2. albums — 按 id 做 ON CONFLICT DO NOTHING
3. songs — 填入 album_id（FK）和 singers（JSONB），按 platform_song_id 做唯一约束
4. singer_songs — songs 写入后填充，按 (singer_id, song_id) 做 ON CONFLICT DO NOTHING
5. singer_albums — albums 写入后填充，按 (singer_id, album_id) 做 ON CONFLICT DO NOTHING

---

七、歌手关联表

crawler_xx_singer_songs

目标字段	类型	来源
id	uuid	生成
singer_id	int/bigint	crawler_xx_singers.id（已写入的歌手主键）
song_id	uuid	crawler_xx_songs.id（已写入的歌曲主键）

数据来源：media_xx_singer_has_songs（singer_id, song_id），spider 表的 singer_id / song_id 与 PG 各表主键直接对应。

PG 目标表	Spider 来源表	唯一约束
crawler_qqmusic_singer_songs	media_tencent_singer_has_songs	(singer_id, song_id)
crawler_kugou_singer_songs	media_ku_gou_singer_has_songs	(singer_id, song_id)
crawler_netease_singer_songs	media_netease_singer_has_songs	(singer_id, song_id)

crawler_xx_singer_albums

目标字段	类型	来源
id	uuid	生成
singer_id	int/bigint	crawler_xx_singers.id（已写入的歌手主键）
album_id	bigint	crawler_xx_albums.id（已写入的专辑主键）

数据来源：media_xx_singer_has_albums（singer_id, album_id）。

PG 目标表	Spider 来源表	唯一约束
crawler_qqmusic_singer_albums	media_tencent_singer_has_albums	(singer_id, album_id)
crawler_kugou_singer_albums	media_ku_gou_singer_has_albums	(singer_id, album_id)
crawler_netease_singer_albums	media_netease_singer_has_albums	(singer_id, album_id)