做音乐类项目的人多少都会遇到一个尴尬期下载记录一开始用日志文件凑合着记本地跑没问题哪天要按歌手统计、给用户做“最近下载”列表、或者在两台设备之间同步历史记录就发现文件那套根本扛不住。这个项目编号 008难度 4.8要解决的就是这件事——把音乐下载记录老老实实存进 MySQL让记录可以查、可以统计、可以多端对接而不是停留在“写完就扔”的日志层面。这篇文章我会从零拆解整个存储方案表结构怎么设计、每个字段为什么这么定、常见的重复下载怎么防、时间乱码这种坑怎么避开最后给一套可以直接抄走的建表 SQL 和查询语句。适合正在做音乐下载器、个人播放器、或准备给自己的小工具加“下载历史”功能的开发者参考。1. 这个需求背后的真实场景很多开发者在项目初期并不会意识到“下载记录”是个需要认真设计的模块。往往是功能做到一半或者用户开始提需求了才回头补这块。在我看来这个需求真正被引爆通常有三个节点一是下载量开始变大需要统计热门歌曲二是用户要求跨设备同步下载历史三是运营或者你自己想看每日下载趋势。这三个场景靠普通文本日志都很难撑起来。1.1 什么时候必须要一张下载记录表如果你只是写个脚本偶尔下载几首歌确实没必要引入 MySQLLog 文件足够。但一旦满足以下任意一条就应该认真建表用户能在 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。为什么要拆成两个因为下载是异步操作有可能失败、超时。只记一个时间就无法精确统计“下载成功率”和“平均下载耗时”。字段类型上我做了几个关键选择放给大家参考字段类型建议原因id 主键BIGINT UNSIGNED AUTO_INCREMENT不要用 INT下载记录增长快INT 上限约 42 亿但巨量记录下很容易触顶song_hashCHAR(32)MD5 结果固定 32 位用 CHAR 比 VARCHAR 更省空间、查询更快durationINT UNSIGNED歌曲时长精确到秒几千秒完全够用file_sizeBIGINT UNSIGNED文件大小按字节存。FLAC 高清文件轻松上百 MBINT 不够用source_typeTINYINT UNSIGNED来源用数字字典避免直接存中文描述灵活且省空间download_statusTINYINT UNSIGNED下载状态也是字典值0-下载中 1-成功 2-失败 3-取消download_timeDATETIME和 TIMESTAMP 相比DATETIME 不依赖时区范围更大避免 2038 问题这些看起来都是细节但实际都是在生产环境里踩过坑才定下来的。比如文件大小用 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_ci5.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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT音乐下载记录表;引擎固定用 InnoDB不要用 MyISAM。虽然 CDN 或日志类场景偶尔会有人用 MyISAM 觉得读得快但下载记录有更新和并发写入InnoDB 的行级锁、崩溃恢复、事务支持才是正解。3.3 写入方式单条、批量与幂等插入写入之前先明确一个设计下载接口被调用时先写一条download_status0的记录真正下载成功后再更新成 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.urljdbc:mysql://localhost:3306/music_app?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghaiPython 使用 PyMySQL 时import pymysql conn pymysql.connect( hostlocalhost, usermusic_app, password你的密码, databasemusic_app, charsetutf8mb4, cursorclasspymysql.cursors.DictCursor, )两边的显式指定都不能省因为 MySQL 服务端、连接层、应用层任何一处字符集不一致都会导致中文乱码而报错往往还不直观。5. 常见问题与排查速查这部分都是我在实际项目里见过的坑有些问题隐蔽得让人抓狂这里直接整理成速查表方便照着排查。现象根本原因解决方案插入中文歌名变成“”或乱码数据库/表/连接任一环节字符集不是 utf8mb4建库建表统一 utf8mb4连接串显式指定 characterEncodingutf8记录时间比本地时间慢或快 8 小时MySQL 连接时区与服务端时区不一致连接串加 serverTimezoneAsia/Shanghai或执行SET time_zone 08:00唯一索引没生效同一首歌重复插入成功唯一键字段含 NULLNULL 不参与唯一约束给 song_hash、device_id 加 NOT NULL 约束批量插入报“Duplicate entry”但不希望中断未使用幂等写法改用INSERT ... ON DUPLICATE KEY UPDATE歌曲名带 emoji 写入报错库表仍为 utf8mb3/utf8 字符集统一迁移到 utf8mb4 并重启连接查询 GROUP BY 统计不准数量偏大把失败/取消状态也统计进去加上WHERE download_status 1表数据量几十万后查询明显变慢缺少时间索引或组合索引不匹配检查执行计划确认idx_download_time是否命中5.1 中文乱码中文乱码是最常见的坑。如果建表时用了 utf8mb4连接串也指定了还是乱码检查 MySQL 服务端全局变量SHOW VARIABLES LIKE character_set%;重点关注character_set_server和character_set_database。如果character_set_server还是 latin1旧版本默认值那么即使建表指定 utf8mb4某些备份恢复、导入导出场景下仍可能出问题。稳妥做法是在 MySQL 配置文件[mysqld]段加上character-set-serverutf8mb4 collation-serverutf8mb4_unicode_ci设置完后重启 MySQL一劳永逸。5.2 时间差了 8 小时这是中国开发者最容易遇到的问题。原因是 MySQL 连接默认使用系统时区如果你的数据库主机位于 UTC 时区而应用在 UTC8连过去查NOW()就会差 8 小时。排查方法很简单SELECT NOW(), global.time_zone, session.time_zone;global.time_zone如果显示SYSTEM表示跟随操作系统时区。解决方案有几种一是操作系统的时区设置正确二是在连接串指定serverTimezoneAsia/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、descSQL 怎么写怎么错或者被默认解析成特殊含义。虽然可以用反引号包住解决但最好的方案是设计阶段就避免。如果你接手的老表里已经存在这种字段名记住查询时必须使用反引号SELECT group, song_name FROM download_record;这种事情看起来小但却是实际开发中极其常见的卡壳点值得记进避坑清单。回到开头那个判断下载记录存储看起来是个不起眼的小模块但表结构设计和写入策略一旦定错后面所有查询、统计、扩展都会被拖累。我现在的习惯是写任何持久化模块前先把“谁、什么时候、做了什么、结果如何”四个问题想清楚字段和索引自然就有了。再分享一个经验这类记录表上线后可以顺手把download_time建立每日定时任务把前一天的下载量统计写入一张统计表。之后看趋势数据就是毫秒级不用每次跑到明细表里用GROUP BY现算。这也是 MySQL 场景下的常规优化做法一次投入长期受益。