apple-mail-search

在 macOS 上通过 SQLite 快速搜索 Apple Mail。按主题、发件人、日期、附件搜索电子邮件 - 结果约为 50 毫秒，而使用 AppleScript 则需要 8 分钟以上。当要求查找、搜索或列出电子邮件时使用。

# Apple Mail Search

Search Apple Mail.app emails instantly via SQLite. ~50ms vs 8+ minutes with AppleScript.

## Installation

```bash
# Copy mail-search to your PATH
cp mail-search /usr/local/bin/
chmod +x /usr/local/bin/mail-search
```

## Usage

```bash
mail-search subject "invoice"           # Search subjects
mail-search sender "@amazon.com"        # Search by sender email
mail-search from-name "John"            # Search by sender name
mail-search to "recipient@example.com"  # Search sent mail
mail-search unread                      # List unread emails
mail-search attachments                 # List emails with attachments
mail-search attachment-type pdf         # Find PDFs
mail-search recent 7                    # Last 7 days
mail-search date-range 2025-01-01 2025-01-31
mail-search open 12345                  # Open email by ID
mail-search stats                       # Database statistics
```

## Options

```
-n, --limit N    Max results (default: 20)
-j, --json       Output as JSON
-c, --csv        Output as CSV
-q, --quiet      No headers
--db PATH        Override database path
```

## Examples

```bash
# Find bank statements from last month
mail-search subject "statement" -n 50

# Get unread emails as JSON for processing
mail-search unread --json | jq '.[] | .subject'

# Find all PDFs from a specific sender
mail-search sender "@bankofamerica.com" -n 100 | grep -i statement

# Export recent emails to CSV
mail-search recent 30 --csv > recent_emails.csv
```

## Why This Exists

| Method | Time for 130k emails |
|--------|---------------------|
| AppleScript iteration | 8+ minutes |
| Spotlight/mdfind | **Broken since Big Sur** |
| SQLite (this tool) | ~50ms |

Apple removed the emlx Spotlight importer in macOS Big Sur. This tool queries the `Envelope Index` SQLite database directly.

## Technical Details

**Database:** `~/Library/Mail/V{9,10,11}/MailData/Envelope Index`

**Key tables:**
- `messages` - Email metadata (dates, flags, FKs)
- `subjects` - Subject lines
- `addresses` - Email addresses and display names
- `recipients` - TO/CC mappings
- `attachments` - Attachment filenames

**Limitations:**
- Read-only (cannot compose/send)
- Metadata only (bodies in .emlx files)
- Mail.app only (not Outlook, etc.)

## Advanced: Raw SQL

For custom queries, use sqlite3 directly:

```bash
sqlite3 -header -column ~/Library/Mail/V10/MailData/Envelope\ Index "
SELECT m.ROWID, s.subject, a.address
FROM messages m
JOIN subjects s ON m.subject = s.ROWID
LEFT JOIN addresses a ON m.sender = a.ROWID
WHERE s.subject LIKE '%your query%'
ORDER BY m.date_sent DESC
LIMIT 20;
"
```

## License

MIT

---

## 中文说明

# Apple Mail Search

通过 SQLite 即时搜索 Apple Mail.app 邮件。约 50 毫秒，而使用 AppleScript 需要 8 分钟以上。

## 安装

```bash
# Copy mail-search to your PATH
cp mail-search /usr/local/bin/
chmod +x /usr/local/bin/mail-search
```

## 用法

```bash
mail-search subject "invoice"           # Search subjects
mail-search sender "@amazon.com"        # Search by sender email
mail-search from-name "John"            # Search by sender name
mail-search to "recipient@example.com"  # Search sent mail
mail-search unread                      # List unread emails
mail-search attachments                 # List emails with attachments
mail-search attachment-type pdf         # Find PDFs
mail-search recent 7                    # Last 7 days
mail-search date-range 2025-01-01 2025-01-31
mail-search open 12345                  # Open email by ID
mail-search stats                       # Database statistics
```

## 选项

```
-n, --limit N    Max results (default: 20)
-j, --json       Output as JSON
-c, --csv        Output as CSV
-q, --quiet      No headers
--db PATH        Override database path
```

## 示例

```bash
# Find bank statements from last month
mail-search subject "statement" -n 50

# Get unread emails as JSON for processing
mail-search unread --json | jq '.[] | .subject'

# Find all PDFs from a specific sender
mail-search sender "@bankofamerica.com" -n 100 | grep -i statement

# Export recent emails to CSV
mail-search recent 30 --csv > recent_emails.csv
```

## 为何存在此工具

| 方法 | 处理 13 万封邮件所需时间 |
|--------|---------------------|
| AppleScript 迭代 | 8 分钟以上 |
| Spotlight/mdfind | **自 Big Sur 起已失效** |
| SQLite（本工具） | 约 50 毫秒 |

Apple 在 macOS Big Sur 中移除了 emlx 的 Spotlight 导入器。本工具直接查询 `Envelope Index` SQLite 数据库。

## 技术细节

**数据库：** `~/Library/Mail/V{9,10,11}/MailData/Envelope Index`

**关键表：**
- `messages` - 邮件元数据（日期、标志、外键）
- `subjects` - 主题行
- `addresses` - 邮件地址和显示名称
- `recipients` - TO/CC 映射
- `attachments` - 附件文件名

**局限性：**
- 只读（无法撰写/发送）
- 仅元数据（正文位于 .emlx 文件中）
- 仅支持 Mail.app（不支持 Outlook 等）

## 进阶：原始 SQL

如需自定义查询，可直接使用 sqlite3：

```bash
sqlite3 -header -column ~/Library/Mail/V10/MailData/Envelope\ Index "
SELECT m.ROWID, s.subject, a.address
FROM messages m
JOIN subjects s ON m.subject = s.ROWID
LEFT JOIN addresses a ON m.sender = a.ROWID
WHERE s.subject LIKE '%your query%'
ORDER BY m.date_sent DESC
LIMIT 20;
"
```

## 许可证

MIT