小米miui澎湃 相册数据库db文件 表 gallery_sub.db

sqlite

gallery_sub.db

MediaFeature 43万行

 

 

Card
CustomWidgetDBEntity
DeleteRecord
FaceClusterInfo
FaceInfo
Library
MediaFeature
MediaRemarkInfo
MediaScene
PendingTaskInfo
PeopleCover
PeopleEvent
PersistentResponse
RecommendWidgetDBEntity
Record
SyncTag
TrashBinItem
TrashSyncTag
UnhandledScanTaskRecord
android metadata
sqlite_sequence

 

 

 

SyncTag    1
Card    133
TrashSyncTag    1
Library    69
PendingTaskInfo    6673
MediaFeature    434302
MediaScene    78174
FaceClusterInfo    7189
FaceInfo    41505
PeopleEvent    116
Record    143
DeleteRecord    259568
MediaRemarkInfo    602
TrashBinItem    594
UnhandledScanTaskRecord    614832

 

 

gallery.db

sqlite windows powershell 获取某个数据库文件的所有表各有多少行

$db  = 'G:\mi10ultra\data..data..com.miui.gallery20250627\databases\gallery.db'

$sql = @'
SELECT 'SELECT "' || name || '", COUNT(*) FROM "' || name || '";'
FROM   sqlite_master
WHERE  type='table' AND name NOT LIKE 'sqlite_%';
'@

$sql | & sqlite3.exe $db | & sqlite3.exe $db

解析一下上面这个
SELECT 'SELECT "' || name || '", COUNT(*) FROM "' || name || '";'

SELECT 'a' || name || 'b' || name || 'c'

||是拼接字符串的作用

最后Select输出的是

a name1 b name1 c

a name2 b name2 c

这样的name就是一个个表名

 

 

android_metadata|1
cloud|169977
cloudSetting|1
shareImage|0
shareUser|0
cloudUser|0
cloudCache|0
userInfo|1
ownerSubUbifocus|0
shareSubUbifocus|0
peopleFace|0
faceToImages|0
peopleRecommend|0
discoveryMessage|1
recentDiscoveredMedia|36819
cloudControl|25
favorites|1163
album|36
shareAlbum|0

 

gallery_sub.db

android_metadata|1
Card|152
PendingTaskInfo|4
PersistentResponse|0
MediaRemarkInfo|0
PeopleCover|0
DeleteRecord|4295
Record|162
Library|74
SyncTag|1
TrashBinItem|48
TrashSyncTag|1
FaceInfo|45216
FaceClusterInfo|37
PeopleEvent|117
MediaFeature|531866
MediaScene|87877
CustomWidgetDBEntity|0
RecommendWidgetDBEntity|0
UnhandledScanTaskRecord|0

 

---

 

下面这个不行，大概是转义换行的问题

# 把数据库路径放进变量可读性更好
$db = 'G:\mi10ultra\data..data..com.miui.gallery20250627\databases\gallery.db'

& sqlite3.exe $db '
  SELECT ''SELECT ""'' || name || '"" , COUNT(*) FROM ""'' || name || '"";'' 
  FROM   sqlite_master
  WHERE  type=''table'' AND name NOT LIKE ''sqlite_%'';
' | & sqlite3.exe $db