数据库查询

危险

请注意，直接操作数据库可能会造成严重问题，尽量避免直接修改数据库，并始终备有最新的备份。

提示

运行 docker exec -it immich_postgres psql --dbname=<DB_DATABASE_NAME> --username=<DB_USERNAME> 可通过容器直接连接数据库。

（将 <DB_DATABASE_NAME> 和 <DB_USERNAME> 替换为您的 .env 文件 中的值）。

资源

文件名

备注

"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_ 的所有文件

按路径查找

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/%';

ID

按 ID 查找

SELECT * FROM "asset" WHERE "id" = '9f94e60f-65b6-47b7-ae44-a4df7b57f0e9';

按部分 ID 查找

SELECT * FROM "asset" WHERE "id"::text LIKE '%ab431d3a%';

校验和

备注

您可以使用命令 sha1sum <filename> 计算特定文件的校验和。

按校验和（SHA-1）查找

SELECT encode("checksum", 'hex') FROM "asset";
SELECT * FROM "asset" WHERE "checksum" = decode('69de19c87658c4c15d9cacb9967b8e033bf74dd1', 'hex');
SELECT * FROM "asset" WHERE "checksum" = '\x69de19c87658c4c15d9cacb9967b8e033bf74dd1'; -- 替代表示法

查找具有相同校验和（SHA-1）的重复资源（排除已经删除的文件）

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";

元数据

实时照片

SELECT * FROM "asset" WHERE "livePhotoVideoId" IS NOT NULL;

按描述查找

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 '%要匹配的字符串%'; -- 按字符串搜索

没有元数据的资源

SELECT "asset".* FROM "asset_exif"
  LEFT JOIN "asset" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."assetId" IS NULL;

文件大小小于 100,000 字节，从小到大排序

SELECT * FROM "asset"
  JOIN "asset_exif" ON "asset"."id" = "asset_exif"."assetId"
  WHERE "asset_exif"."fileSizeInByte" < 100000
  ORDER BY "asset_exif"."fileSizeInByte" ASC;

类型

按类型查找

SELECT * FROM "asset" WHERE "asset"."type" = 'VIDEO';
SELECT * FROM "asset" WHERE "asset"."type" = 'IMAGE';

按类型统计

SELECT "asset"."type", COUNT(*) FROM "asset" GROUP BY "asset"."type";

按类型统计（按用户）

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";

标签

按标签统计

SELECT "t"."value" AS "tag_name", COUNT(*) AS "number_assets" FROM "tag" "t"
  JOIN "tag_asset" "ta" ON "t"."id" = "ta"."tagsId" JOIN "asset" "a" ON "ta"."assetsId" = "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"."tagsId" JOIN "asset" "a" ON "ta"."assetsId" = "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;

用户

列出所有用户

SELECT * FROM "user";

通过资源 ID 获取拥有者信息

SELECT "user".* FROM "user" JOIN "asset" ON "user"."id" = "asset"."ownerId" WHERE "asset"."id" = 'fa310b01-2f26-4b7a-9042-d578226e021f';

人物

删除人物并解除与其关联的面部

DELETE FROM "person" WHERE "name" = 'PersonNameHere';

系统

配置

自定义设置

SELECT "key", "value" FROM "system_metadata" WHERE "key" = 'system-config';

（仅在未使用 配置文件 时使用）

文件属性

没有缩略图的资源

SELECT * FROM "asset" WHERE "asset"."previewPath" IS NULL OR "asset"."thumbnailPath" IS NULL;

文件移动失败记录

SELECT * FROM "move_history";

Postgres 内部

更改数据库密码

ALTER USER <DB_USERNAME> WITH ENCRYPTED PASSWORD 'newpasswordhere';