Immich 数据库查询实战基于 Postgres 的常用 SQL 操作与底层表结构解析【免费下载链接】immichHigh performance self-hosted photo and video management solution.项目地址: https://gitcode.com/GitHub_Trending/im/immich本文围绕 Immich 官方文档中的数据库查询指南展开介绍如何直接进入 Immich 的 Postgres 容器执行 SQL覆盖资产Assets按文件名/路径/ID/校验值/元数据/类型查找、标签与用户统计、人物删除、系统配置与文件状态检查、Postgres 用户管理等全部核心查询场景。结合服务端 schema 源码进一步解释每张表、每个关键字段的真实定义与约束帮助你在排查数据、做重复清理或深度自定义统计时既能“查得到”也“查得明白”。连接数据库入口命令与风险提示Immich 使用 Postgres 作为唯一的数据存储核心。文档给出的标准连接方式是通过 Docker 直接进入数据库容器docker exec -it immich_postgres psql --dbnameDB_DATABASE_NAME --usernameDB_USERNAME其中DB_USERNAME与DB_DATABASE_NAME取自你的.env文件对应环境变量说明见 environment-variables.md 的 Database 一节默认值分别为postgres与immich。容器名immich_postgres并非约定俗成的猜测在 docker-compose.yml 中database服务明确声明container_name: immich_postgres并且通过POSTGRES_USER、POSTGRES_PASSWORD、POSTGRES_DB三个环境变量注入你在.env中配置的DB_USERNAME、DB_PASSWORD、DB_DATABASE_NAME。如果你自定义了.env容器名通常不变但后两个参数必须替换。风险警示原文档 danger 提示的忠实继承直接修改数据库可能带来不可预料的后果——“把数据库搅动一番可能会让月球起火”。请尽可能避免直接修改数据库并且永远先做好最新备份。本文后续所有查询示例中SELECT类语句只读、安全涉及DELETE/ALTER的语句请务必先备份。资产Assets查询资产是 Immich 数据库的核心。其主表定义在 asset.table.tsid是主键UUIDownerId外键指向用户checksum是一个bytea类型的 SHA-1 校验值列originalFileName、originalPath分别记录上传时的原始文件名与路径deletedAt用于回收站软删除visibility控制可见性。以下查询均建立在这些字段之上。按文件名与路径查找originalFileName列保存的是上传时刻的文件名包含扩展名SELECT * FROM asset WHERE originalFileName PXL_20230903_232542848.jpg; SELECT * FROM asset WHERE originalFileName LIKE PXL_%; -- 所有以 PXL_ 开头的文件 SELECT * FROM asset WHERE originalFileName LIKE %_2023_%; -- 文件名中间包含 _2023_ 的文件按路径查找originalPath是资产在存储目录中的相对路径SELECT * FROM asset WHERE originalPath upload/library/admin/2023/2023-09-03/PXL_2023.jpg; SELECT * FROM asset WHERE originalPath LIKE upload/library/admin/2023/%;从源码可以补充两点背景帮助理解为什么这些列值得建立查询习惯originalFileName上建了普通 B-tree 索引asset.table.ts 的Column({ index: true })等值查询效率很好LIKE PXL_%这类前缀匹配也能走索引但%_2023_%这种中缀模糊匹配是全表扫描数据量大时要留意耗时。表上还定义了(originalPath, libraryId)复合索引和一个基于f_unaccent的 trigram GIN 索引asset.table.ts说明官方在服务端检索路径与文件名时也是这两个字段最常用。按 ID 查找资产 ID 在 URL、API 和数据库之间通用是最可靠的定位键SELECT * FROM asset WHERE id 9f94e60f-65b6-47b7-ae44-a4df7b57f0e9;如果只记得 ID 的一部分可以先转成文本再做模糊匹配SELECT * FROM asset WHERE id::text LIKE %ab431d3a%;id::text是 Postgres 的显式类型转换写法将 UUID 转成字符串后再用LIKE。id是主键且带部分索引visibility timeline AND deletedAt IS NULL见 asset.table.ts精确按 ID 查询几乎零成本LIKE变体则用于排障时“只看到片段”的场景。按校验值SHA-1 Checksum查找checksum列在源码中声明为bytea并附注释// sha1 checksumasset.table.ts。二进制列在 SQL 中需要用decode/encode在十六进制文本与字节之间转换。文档给出的用法SELECT encode(checksum, hex) FROM asset; SELECT * FROM asset WHERE checksum decode(69de19c87658c4c15d9cacb9967b8e033bf74dd1, hex); SELECT * FROM asset WHERE checksum \x69de19c87658c4c15d9cacb9967b8e033bf74dd1; -- 等价写法在文件系统侧可以用sha1sum filename计算任意文件的校验值再回数据库比对这是排查“文件已丢失但记录还在”或“磁盘上有文件但未被索引”这类问题的标准手段。校验值还被用于重复检测。服务端在 schema 层就承认了这一点asset表上建有(ownerId, checksum)的部分唯一索引libraryId IS NULL时以及(ownerId, libraryId, checksum)索引asset.table.ts即“校验值在用户或用户共享库范围内必须唯一”。文档提供的重复资产查询排除回收站SELECT T1.checksum, array_agg(T2.id) ids FROM asset T1 INNER JOIN asset T2 ON T1.checksum T2.checksum AND T1.id ! T2.id AND T2.deletedAt IS NULL WHERE T1.deletedAt IS NULL GROUP BY T1.checksum;该查询通过自连接 GROUP BY checksum聚合出所有同校验值的 ID 列表。T1.deletedAt IS NULL与T2.deletedAt IS NULL双重过滤确保不统计回收站资产array_agg把同组 ID 聚合成数组方便后续定位。按元数据查询EXIF 元数据独立存放在asset_exif表中其主键assetId直接外键指向asset.id且级联删除asset-exif.table.ts因此两表以assetId关联是文档所有元数据查询的连接键。实况照片Live Photos实况照片由一张静态图 一段关联视频组成livePhotoVideoId是asset表的自引用外键asset.table.tsSELECT * FROM asset WHERE livePhotoVideoId IS NOT NULL;按描述查询description字段在 asset-exif.table.ts 中定义为text、默认空串注释即 “or caption”SELECT asset.*, asset_exif.description FROM asset_exif JOIN asset ON asset.id asset_exif.assetId WHERE TRIM(asset_exif.description) ; -- 所有有描述的文件 SELECT asset.*, asset_exif.description FROM asset_exif JOIN asset ON asset.id asset_exif.assetId WHERE asset_exif.description ILIKE %string to match%; -- 按字符串搜索描述ILIKE是大小写不敏感的模糊匹配TRIM(...) 用来排除默认空描述。查找无元数据的资产EXIF 解析失败的常见信号SELECT asset.* FROM asset_exif LEFT JOIN asset ON asset.id asset_exif.assetId WHERE asset_exif.assetId IS NULL;注意这条查询的原样写法存在语义可疑之处——以asset_exif为主表LEFT JOIN到asset再用asset_exif.assetId IS NULL过滤条件方向与常规“找缺失 EXIF 记录”的写法相反。如果实际目的是找出没有对应asset_exif行的资产更直观的写法应是SELECT asset.* FROM asset LEFT JOIN asset_exif ON asset.id asset_exif.assetId WHERE asset_exif.assetId IS NULL;本文如实保留原文档 SQL 供对照实际使用时建议以上述语义为准。按文件大小查询fileSizeInByte是bigint可空列见 asset-exif.table.tsSELECT * FROM asset JOIN asset_exif ON asset.id asset_exif.assetId WHERE asset_exif.fileSizeInByte 100000 ORDER BY asset_exif.fileSizeInByte ASC;按类型Type查询与统计资产类型枚举为IMAGE与VIDEO对应 asset.table.ts 中的type!: AssetType列SELECT * FROM asset WHERE asset.type VIDEO; SELECT * FROM asset WHERE asset.type IMAGE;SELECT asset.type, COUNT(*) FROM asset GROUP BY asset.type;按用户维度的类型统计资产通过ownerId关联user表SELECT user.email, asset.type, COUNT(*) FROM asset JOIN user ON asset.ownerId user.id GROUP BY asset.type, user.email ORDER BY user.email;这类聚合查询适合评估存储构成例如视频占比过高时可考虑启用转码或清理策略。标签Tags查询标签体系由tag、tag_asset多对多中间表构成。tag表以(userId, value)唯一tag.table.ts并且支持parentId自引用外键形成层级标签树tag.table.ts。SELECT t.value AS tag_name, COUNT(*) AS number_assets FROM tag t JOIN tag_asset ta ON t.id ta.tagId JOIN asset a ON ta.assetId a.id WHERE a.visibility ! hidden GROUP BY t.value ORDER BY number_assets DESC;SELECT t.value AS tag_name, u.email as user_email, COUNT(*) AS number_assets FROM tag t JOIN tag_asset ta ON t.id ta.tagId JOIN asset a ON ta.assetId a.id JOIN user u ON a.ownerId u.id WHERE a.visibility ! hidden GROUP BY t.value, u.email ORDER BY number_assets DESC;两条查询的共同设计是WHERE a.visibility ! hidden排除被标记为隐藏的资产使统计结果与界面可见数据一致。visibility列默认值为timelineasset.table.ts。用户Users查询SELECT * FROM user;从资产 ID 反查其属主信息SELECT user.* FROM user JOIN asset ON user.id asset.ownerId WHERE asset.id fa310b01-2f26-4b7a-9042-d578226e021f;这条查询的价值在于权限审计当你想确认某个资产归属哪个账号比如共享链接、API 上传产生的资产直接由ownerId外键一步定位。人物Persons查询文档给出的示例是删除某个已命名的人物并解除其关联脸部的归属DELETE FROM person WHERE name PersonNameHere;这条DELETE之所以“解绑”而非“连级删除脸部”背后有 schema 层的机制支撑person表定义了AfterDeleteTrigger删除后会调用person_delete_audit触发器函数person.table.ts同时asset_face表中脸部到人物的外键是ON DELETE SET NULL语义因此人物删除后原属于该人物的脸部记录保留但失去人物归属等待重新聚类。再次强调DELETE前请务必备份数据库。系统System查询系统配置ConfigSELECT key, value FROM system_metadata WHERE key system-config;system_metadata是一张极简的键值表key为主键value为jsonbsystem-metadata.table.ts。它保存的是通过 Web 管理界面修改的“系统设置”仅在未使用配置文件时生效——如果你通过 config-file 方式配置了IMMICH_CONFIG_FILE则以配置文件为准这张表中的设置只是数据库里的历史/界面态副本。文件属性与状态检查缺少缩略图的资产缩略图生成失败、需要手动重跑的典型信号。缩略文件记录在asset_file表中其(assetId, type, isEdited)三元组唯一asset-file.table.tstype取值包含thumbnail与previewSELECT * FROM asset WHERE (NOT EXISTS (SELECT 1 FROM asset_file WHERE asset.id asset_file.assetId AND asset_file.type thumbnail) OR NOT EXISTS (SELECT 1 FROM asset_file WHERE asset.id asset_file.assetId AND asset_file.type preview)) AND asset.visibility timeline;逻辑是对时间轴可见visibility timeline的资产检查其在asset_file中是否同时具备thumbnail和preview两种文件记录任一缺失即命中。找到后通常可结合任务系统admin 界面重启 thumbnail 生成或重新上传来修复。失败的文件移动Immich 在重命名/移动资产时会向move_history表写入计划记录entityId、pathType、oldPath、newPath并对(entityId, pathType)与newPath分别建立唯一约束见 move.table.ts用于在事务失败时回滚。正常情况下该表应保持为空SELECT * FROM move_history;如果该表存在记录说明此前有移动操作半途失败需要人工比对oldPath/newPath与磁盘实际状态后谨慎处理。Postgres 内部操作修改数据库密码例如将默认的postgres弱口令换成强口令。注意这改变的是 Postgres 角色本身改完后必须同步更新.env中的DB_PASSWORD并重建recreate相关容器否则服务端将因连接失败而不可用ALTER USER DB_USERNAME WITH ENCRYPTED PASSWORD newpasswordhere;小结查询时的三张底牌只读先行资产、标签、用户、系统配置类查询都是SELECT可以放心用于日常巡检move_history是否为空是判断文件操作健康度的快速信号。字段语义有源码背书checksum是bytea型 SHA-1、originalFileName含扩展名、asset_exif.assetId为主键级联删除、person删除走触发器 SET NULL解绑——这些定义都能在 server/src/schema/tables/ 目录下逐一核对理解约束后再写查询可以避免误判数据。写操作永远备份优先涉及DELETE如人物删除或ALTER USER如改密码时先用 backup-and-restore 所述流程做一份当前备份再执行操作。按此路径你可以把官方文档中的每一条查询从“能跑”提升到“知其所以然”并安全地用于生产环境的数据排查与治理。【免费下载链接】immichHigh performance self-hosted photo and video management solution.项目地址: https://gitcode.com/GitHub_Trending/im/immich创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考