音乐下载记录MySQL存储方案：表结构设计与避坑实践

做音乐类项目的人，多少都会遇到一个尴尬期：下载记录一开始用日志文件凑合着记，本地跑没问题，哪天要按歌手统计、给用户做“最近下载”列表、或者在两台设备之间同步历史记录，就发现文件那套根本扛不住。这个项目（编号 008，难度 4.8）要解决的就是这件事——把音乐下载记录老老实实存进 MySQL，让记录可以查、可以统计、可以多端对接，而不是停留在“写完就扔”的日志层面。

这篇文章我会从零拆解整个存储方案：表结构怎么设计、每个字段为什么这么定、常见的重复下载怎么防、时间乱码这种坑怎么避开，最后给一套可以直接抄走的建表 SQL 和查询语句。适合正在做音乐下载器、个人播放器、或准备给自己的小工具加“下载历史”功能的开发者参考。

1. 这个需求背后的真实场景

很多开发者在项目初期并不会意识到“下载记录”是个需要认真设计的模块。往往是功能做到一半，或者用户开始提需求了，才回头补这块。在我看来，这个需求真正被引爆通常有三个节点：一是下载量开始变大，需要统计热门歌曲；二是用户要求跨设备同步下载历史；三是运营或者你自己想看每日下载趋势。这三个场景，靠普通文本日志都很难撑起来。

1.1 什么时候必须要一张下载记录表

如果你只是写个脚本偶尔下载几首歌，确实没必要引入 MySQL，Log 文件足够。但一旦满足以下任意一条，就应该认真建表：

• 用户能在 App 或网页里查看“我的下载记录”，并且要求按时间倒序、按歌手筛选；
• 需要统计歌曲/歌手的下载排行，或者做每日下载量趋势图；
• 同一个用户在多台设备登录，下载历史要合并同步；
• 下载任务失败后要重试，或者需要追踪某个任务为什么失败。

这些需求本质上都是“查询”需求，而且是有条件的查询。文件日志只能顺序读，每次查一次“歌手A下载过哪些歌”就要全量扫描一遍，数据到几千条就开始卡。MySQL 这类关系型数据库配合索引，在百万级以内的单表查询都能做到毫秒级响应，这才是它在这个场景下不可替代的原因。

1.2 为什么是 MySQL 而不是文件/Redis/SQLite

说实话，我第一次做类似功能时也犹豫过：直接用 Redis 存列表不行吗？答案是不行。Redis 做排行榜、计数器这类热数据很强，但它的持久化机制决定了它更适合做缓存而不是最终存储，服务器宕机丢数据这件事在“历史记录”这种场景下是不能接受的。SQLite 更适合端侧本地存储，比如手机 App 离线先记录、联网再同步，但它不支持并发写入，也不方便服务端做聚合统计。

MySQL 在这个场景下的优势很清晰：

• 成熟稳定，文档多，团队协作成本低；
• 支持事务和索引，写坏数据概率低，查询快；
• utf8mb4 字符集对中文歌名、歌手名支持得很好；
• 生态工具丰富，后续做报表、接可视化后台都很方便。

对于绝大多数中小型音乐类项目，“服务端 MySQL + 客户端本地缓存”的组合已经足够稳定。真到了每天写入几十万条的海量场景，再考虑分表或引入其他存储，那是后话，但表结构设计时要提前留好扩展空间。

2. 表结构设计：一次下载如何变成一条记录

表结构是这次项目的灵魂。设计得好，后续查询、统计、扩展都顺；设计不好，后面加字段、改索引都是眼泪。我设计下载记录表时遵循一个原则：每一行记录，应该能完整回答“谁、在什么时候、从哪里、下载了哪首歌、结果如何”。

2.1 字段拆解：哪些必备，哪些可以后加

核心必备字段我归纳为五类：歌曲标识、用户/设备标识、时间、来源、结果状态。

• 歌曲标识：包括song_hash、song_name、artist、album。注意这里一定要设计一个“逻辑上的歌曲唯一标识”，很多人只存歌名，结果同名歌曲一堆，根本无法区分具体是哪一首。我用的是对歌曲详情页 URL 或文件原始地址做 MD5，得到固定 32 位哈希，这样同一个文件无论在哪里被下载，都能识别为同一首歌。

• 用户/设备标识：字段叫device_id或user_id都行。如果项目没有用户体系，先存设备号；接入账号体系后，再加字段或者用user_id替代。这个字段决定了后续“我的下载记录”怎么查询。

• 记录时间：一个“发起下载时间”download_time，一个“完成时间”finish_time。为什么要拆成两个？因为下载是异步操作，有可能失败、超时。只记一个时间，就无法精确统计“下载成功率”和“平均下载耗时”。

字段类型上我做了几个关键选择，放给大家参考：

这些看起来都是细节，但实际都是在生产环境里踩过坑才定下来的。比如文件大小用 INT 存，早期没事，等用户下载了几个 FLAC 大文件，一条记录存不下直接报错，这种问题排查起来非常痛苦。

2.2 唯一键设计：怎么避免重复下载记录

多数人建表只记得主键，却忽略唯一键。结果就是用户手滑点了两次下载，记录表里出现两行几乎一样的数据，统计下载量时直接翻倍，非常恶心。

我采用的是UNIQUE KEY uk_song_device (song_hash, device_id)，用“歌曲哈希 + 设备号”作为唯一维度。同一首歌在同一台设备上的下载记录只允许存在一条。这样一下子解决了两个问题：

• 应用层不需要先SELECT再决定INSERT，直接写即可，性能更好；
• 就算接口被并发调用，数据库层面也会拦截重复记录，最后一道屏障。

这里有个新手容易踩的坑：唯一索引的字段如果允许 NULL，那么 NULL 值之间是可以重复的，唯一约束形同虚设。所以song_hash和device_id都应该加上NOT NULL，保证唯一约束真正生效。

2.3 索引策略：查询习惯决定索引怎么建

索引不是越多越好，每多一个索引，写入时就要多维护一次 B+ 树，写入性能会有损耗。建索引前先想想业务到底会怎么查。

我的使用场景比较集中，主要三种查询：查某台设备的最近下载记录、查某首歌的下载量、按时间和状态做统计。对应索引如下：

• KEY idx_download_time (download_time)：所有“最近下载”类的列表，基本都用时间排序；
• KEY idx_artist_song (artist, song_name)：按歌手和歌名检索，组合索引覆盖大多数搜索场景；
• KEY idx_status (download_status)：统计成功或失败记录时，能快速过滤。

不建议给file_url建普通索引，URL 又长区分度又低，索引空间浪费严重。也没必要给source_type单独建索引，字段取值就 0/1/2/3 几种，区分度太差，查询时过滤大部分数据反而慢。

3. 实操：建库建表与写入第一条记录

理论讲完，下面上一套可以直接落地的完整操作。我默认你本机已经装好了 MySQL 8.0 及以上版本，装的是 5.7 也兼容，字符集部分稍有差异，下文会特别标注。

3.1 创建数据库、账号与字符集设置

第一步先建库，字符集统一使用 utf8mb4。老项目里常见的坑是用了 utf8mb3（旧版 utf8），这个字符集存不了 emoji，某些生僻字也会报错。音乐歌名里偶尔冒出特殊符号，统一用 utf8mb4 最保险。

CREATE DATABASE IF NOT EXISTS music_app DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

关于排序规则多说一句：MySQL 8.0 默认是utf8mb4_0900_ai_ci，5.7 一般用utf8mb4_general_ci或utf8mb4_unicode_ci。如果你选的排序规则对中文比较有要求，优先utf8mb4_unicode_ci，它在多语言场景下表现更稳定。开发环境 8.0 直接用默认也行，但建议统一指定，避免以后迁移数据库时行为不一致。

然后是账号。不要拿 root 到处用，给业务单独开一个最小权限账号：

CREATE USER 'music_app'@'%' IDENTIFIED BY '你的密码'; GRANT SELECT, INSERT, UPDATE, DELETE ON music_app.download_record TO 'music_app'@'%'; FLUSH PRIVILEGES;

生产环境不建议'%'，应把 Host 限制为应用服务器 IP。开发环境图省事可以用'%'，但心里要清楚线上必须收紧。

3.2 建表 SQL 完整版与字段说明

这是这个项目的核心产出，建表语句我做了详细注释，直接抄作业即可。

CREATE TABLE IF NOT EXISTS download_record ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID', song_hash CHAR(32) NOT NULL COMMENT '歌曲唯一标识，MD5(文件地址)', song_name VARCHAR(255) NOT NULL COMMENT '歌名', artist VARCHAR(255) DEFAULT NULL COMMENT '歌手', album VARCHAR(255) DEFAULT NULL COMMENT '专辑', file_url VARCHAR(512) DEFAULT NULL COMMENT '下载源地址', file_size BIGINT UNSIGNED DEFAULT NULL COMMENT '文件大小（字节）', duration INT UNSIGNED DEFAULT NULL COMMENT '歌曲时长（秒）', file_format VARCHAR(16) DEFAULT NULL COMMENT '文件格式：MP3/FLAC/WAV', source_type TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '来源：0-未知 1-榜单 2-搜索 3-歌单', device_id VARCHAR(128) NOT NULL COMMENT '设备标识/用户ID', download_status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态：0-下载中 1-成功 2-失败 3-取消', error_code VARCHAR(64) DEFAULT NULL COMMENT '失败时的错误码或错误信息摘要', download_time DATETIME NOT NULL COMMENT '发起下载时间', finish_time DATETIME DEFAULT NULL COMMENT '完成时间', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间', PRIMARY KEY (id), UNIQUE KEY uk_song_device (song_hash, device_id), KEY idx_download_time (download_time), KEY idx_artist_song (artist, song_name), KEY idx_status (download_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='音乐下载记录表';

引擎固定用 InnoDB，不要用 MyISAM。虽然 CDN 或日志类场景偶尔会有人用 MyISAM 觉得读得快，但下载记录有更新和并发写入，InnoDB 的行级锁、崩溃恢复、事务支持才是正解。

3.3 写入方式：单条、批量与幂等插入

写入之前先明确一个设计：下载接口被调用时先写一条download_status=0的记录，真正下载成功后再更新成 1，失败则更新成 2 并填入error_code。这样任何时候去查表，都能看到下载中/成功/失败三种状态，不会出现“记录不存在但文件下载好了”的尴尬。

单条插入很简单，注意时间字段由服务端生成，不要轻信客户端传上来的时间：

INSERT INTO download_record (song_hash, song_name, artist, album, file_url, file_size, duration, file_format, source_type, device_id, download_status, download_time) VALUES (MD5('https://music.example.com/song/101'), '晴天', '周杰伦', '叶惠美', 'https://music.example.com/song/101', 8388608, 269, 'MP3', 1, 'device_abc_001', 0, NOW());

注意这里song_hash直接用MD5(URL)生成，逻辑和字段定义保持一致，避免同一首歌因为传入值不同而产生两条哈希。

批量插入适合初始化数据或批量同步历史下载记录，一次插入几百条都很快。配合唯一索引，用INSERT ... ON DUPLICATE KEY UPDATE可以做到“存在就更新、不存在就插入”，天然幂等：

INSERT INTO download_record (song_hash, song_name, artist, album, file_url, file_size, duration, file_format, source_type, device_id, download_status, download_time) VALUES (...多条...) ON DUPLICATE KEY UPDATE song_name = VALUES(song_name), download_status = VALUES(download_status), finish_time = VALUES(finish_time);

需要特别提醒：MySQL 8.0.20 开始，VALUES()这种写法在ON DUPLICATE KEY UPDATE里已经标记为废弃，推荐使用行别名语法。如果数据库是 8.0.19 及以上，可以这样写：

INSERT INTO download_record (...) VALUES (...) AS new ON DUPLICATE KEY UPDATE song_name = new.song_name, download_status = new.download_status, finish_time = new.finish_time;

这个细节容易被网上旧教程带偏，版本一旦升级就可能出现 SQL 能跑但日志刷警告的情况。

4. 查询统计与业务对接

数据存进去只是第一步，能优雅地查出来才算真正交付。这里展示几个我在项目里高频使用的查询，并说明它们的业务含义。

4.1 真实项目里最常用的几种查询

“我的下载记录”列表页，按下载时间倒序，分页取前 20 条：

SELECT song_name, artist, album, file_format, file_size, download_status, download_time, finish_time FROM download_record WHERE device_id = 'device_abc_001' ORDER BY download_time DESC LIMIT 20;

热门歌手下载榜，只统计成功记录：

SELECT artist, COUNT(*) AS download_count FROM download_record WHERE download_status = 1 GROUP BY artist ORDER BY download_count DESC LIMIT 10;

最近 7 天每日下载量趋势：

SELECT DATE(download_time) AS day, COUNT(*) AS total FROM download_record WHERE download_time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) GROUP BY DATE(download_time) ORDER BY day;

每首歌的累计下载量，用来做热门歌曲排序：

SELECT song_name, artist, COUNT(*) AS download_count FROM download_record WHERE download_status = 1 GROUP BY song_hash, song_name, artist ORDER BY download_count DESC LIMIT 50;

这些都是标准查询，不用背，关键在于理解：分组统计尽量用在download_status = 1的成功记录上，避免把失败重试算进去导致数据失真。

4.2 客户端与后端衔接的几个细节

查询部分讲完，说一下业务对接上容易被忽视的几件事。

第一，时间统一由服务端生成。客户端的时间经常不准确，用户改了时区、手机时间不对，都会导致记录出现“未来时间”。数据库里所有时间字段都以服务端为准，客户端展示时再转本地时区。

第二，下载状态要设计成状态机。后端接收下载请求后立刻插入状态 0，回调通知成功时更新为 1，失败时更新为 2 并记录错误码。不要在异步流程里先插入一条成功记录，这样出了问题根本没有回旋余地。

第三，事务边界要控制好。插入状态 0 和更新状态 1 通常是两个独立事务，中间隔着漫长的下载过程。如果放到一个事务里，等于把数据库连接占用在长时间下载上，系统并发一大就满连接。建议：短事务各管各的，更新状态时带上WHERE download_status = 0条件，防止重复回调导致状态被覆盖回写。

第四，连接串和客户端库的字符集要显式指定，否则再遇到中文乱码别怪数据库。Java 连接串示例：

spring.datasource.url=jdbc:mysql://localhost:3306/music_app?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai

Python 使用 PyMySQL 时：

import pymysql conn = pymysql.connect( host="localhost", user="music_app", password="你的密码", database="music_app", charset="utf8mb4", cursorclass=pymysql.cursors.DictCursor, )

两边的显式指定都不能省，因为 MySQL 服务端、连接层、应用层任何一处字符集不一致，都会导致中文乱码，而报错往往还不直观。

5. 常见问题与排查速查

这部分都是我在实际项目里见过的坑，有些问题隐蔽得让人抓狂，这里直接整理成速查表，方便照着排查。

5.1 中文乱码

中文乱码是最常见的坑。如果建表时用了 utf8mb4，连接串也指定了，还是乱码，检查 MySQL 服务端全局变量：

SHOW VARIABLES LIKE 'character_set%';

重点关注character_set_server和character_set_database。如果character_set_server还是 latin1（旧版本默认值），那么即使建表指定 utf8mb4，某些备份恢复、导入导出场景下仍可能出问题。稳妥做法是在 MySQL 配置文件[mysqld]段加上：

character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci

设置完后重启 MySQL，一劳永逸。

5.2 时间差了 8 小时

这是中国开发者最容易遇到的问题。原因是 MySQL 连接默认使用系统时区，如果你的数据库主机位于 UTC 时区，而应用在 UTC+8，连过去查NOW()就会差 8 小时。排查方法很简单：

SELECT NOW(), @@global.time_zone, @@session.time_zone;

@@global.time_zone如果显示SYSTEM，表示跟随操作系统时区。解决方案有几种：一是操作系统的时区设置正确；二是在连接串指定serverTimezone=Asia/Shanghai；三是直接改 MySQL 全局时区。三选一即可，但如果应用服务器分散在不同地域，建议优先通讯和存储都统一用 UTC，展示层再转本地时区，这个属于架构规范问题，小项目可以不做这么严格。

5.3 唯一索引被 NULL “坑”掉

很多人建唯一索引后测试重复插入，发现居然不报错。原因在于 MySQL 唯一索引对 NULL 的处理方式：多个 NULL 值不视为重复。比如字段device_id允许 NULL，那么 100 条device_id为 NULL 的记录都能插入成功。

解决办法就是设计表结构时加NOT NULL。如果业务上确实存在“未登录用户”的情况，不要用 NULL，用空字符串或默认值'anonymous'代替。

5.4 表数据量膨胀怎么办

下载记录是典型的“持续写入、低频更新、查询集中在近期”的数据，长期运行一定会膨胀。当单表数据量到了千万级别，即使有索引，查询速度也会明显下降。应对方案按我推荐的顺序：

• 定期归档：把 90 天以前、状态为成功的历史记录转移到归档表，业务表只保留热数据；
• 按月分表：设计时就按月份建download_record_202501、download_record_202502，应用层写一个路由函数按月份拼接表名；
• 引入分区表：MySQL 8.0 对分区表支持更成熟，但分区键要选查询条件，否则查起来反而更慢。

我自己的做法是业务表保留 6 个月，超过的进归档表，归档数据临时要查再单独去归档库。这样主表查询始终快，归档数据也不丢。

5.5 一个容易忽略的保留字问题

最后提醒一个隐蔽问题：字段或表名不要撞上 MySQL 保留字。比如字段叫order、group、desc，SQL 怎么写怎么错，或者被默认解析成特殊含义。虽然可以用反引号包住解决，但最好的方案是设计阶段就避免。

如果你接手的老表里已经存在这种字段名，记住查询时必须使用反引号：

SELECT `group`, song_name FROM download_record;

这种事情看起来小，但却是实际开发中极其常见的卡壳点，值得记进避坑清单。

回到开头那个判断：下载记录存储看起来是个不起眼的小模块，但表结构设计和写入策略一旦定错，后面所有查询、统计、扩展都会被拖累。我现在的习惯是写任何持久化模块前，先把“谁、什么时候、做了什么、结果如何”四个问题想清楚，字段和索引自然就有了。

再分享一个经验：这类记录表上线后，可以顺手把download_time建立每日定时任务，把前一天的下载量统计写入一张统计表。之后看趋势数据就是毫秒级，不用每次跑到明细表里用GROUP BY现算。这也是 MySQL 场景下的常规优化做法，一次投入，长期受益。