本文目录导读：
我将为您设计一个完整的PHP邮件营销系统架构,这个系统将包含订阅管理、模板制作、发送队列、统计分析等核心功能。
系统架构设计
数据库设计
-- 订阅者表
CREATE TABLE subscribers (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100),
status ENUM('active', 'unsubscribed', 'bounced', 'spam') DEFAULT 'active',
source VARCHAR(50),
ip_address VARCHAR(45),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
unsubscribed_at TIMESTAMP NULL,
INDEX idx_email (email),
INDEX idx_status (status),
INDEX idx_created (created_at)
);
-- 订阅分组表
CREATE TABLE subscriber_groups (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 订阅者-分组关联表
CREATE TABLE subscriber_group_members (
subscriber_id INT,
group_id INT,
subscribed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (subscriber_id, group_id),
FOREIGN KEY (subscriber_id) REFERENCES subscribers(id) ON DELETE CASCADE,
FOREIGN KEY (group_id) REFERENCES subscriber_groups(id) ON DELETE CASCADE
);
-- 邮件模板表
CREATE TABLE email_templates (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
subject VARCHAR(500) NOT NULL,
content TEXT NOT NULL,
content_type ENUM('text', 'html') DEFAULT 'html',
variables JSON,
created_by INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 发送任务表
CREATE TABLE campaigns (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(200) NOT NULL,
template_id INT,
group_ids JSON,
subject VARCHAR(500),
sender_name VARCHAR(100),
sender_email VARCHAR(255),
reply_to VARCHAR(255),
status ENUM('draft', 'scheduled', 'sending', 'completed', 'paused', 'cancelled') DEFAULT 'draft',
scheduled_at TIMESTAMP NULL,
started_at TIMESTAMP NULL,
completed_at TIMESTAMP NULL,
total_recipients INT DEFAULT 0,
sent_count INT DEFAULT 0,
opened_count INT DEFAULT 0,
clicked_count INT DEFAULT 0,
bounced_count INT DEFAULT 0,
unsubscribed_count INT DEFAULT 0,
created_by INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (template_id) REFERENCES email_templates(id)
);
-- 邮件发送队列表
CREATE TABLE email_queue (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
campaign_id INT,
subscriber_id INT,
email VARCHAR(255) NOT NULL,
subject VARCHAR(500),
content TEXT,
status ENUM('pending', 'sending', 'sent', 'failed', 'opened', 'clicked', 'bounced', 'unsubscribed') DEFAULT 'pending',
attempts INT DEFAULT 0,
max_attempts INT DEFAULT 3,
error_message TEXT,
sent_at TIMESTAMP NULL,
opened_at TIMESTAMP NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_campaign_status (campaign_id, status),
INDEX idx_subscriber (subscriber_id),
INDEX idx_created (created_at),
FOREIGN KEY (campaign_id) REFERENCES campaigns(id),
FOREIGN KEY (subscriber_id) REFERENCES subscribers(id)
);
-- 邮件点击追踪表
CREATE TABLE email_tracking (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
queue_id BIGINT,
subscriber_id INT,
click_url VARCHAR(2000),
ip_address VARCHAR(45),
user_agent TEXT,
clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (queue_id) REFERENCES email_queue(id),
FOREIGN KEY (subscriber_id) REFERENCES subscribers(id)
);
核心类设计
<?php
// MailMarketingSystem.php
class MailMarketingSystem {
private $db;
private $config;
private $mailer;
public function __construct(PDO $db, array $config) {
$this->db = $db;
$this->config = $config;
$this->mailer = $this->initMailer();
}
// 订阅管理
public function subscribe(string $email, string $name = '', array $groups = [], string $source = 'webform'): bool {
try {
// 验证邮箱
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
throw new Exception('Invalid email address');
}
// 检查是否已存在
$existing = $this->getSubscriberByEmail($email);
if ($existing) {
// 更新订阅状态（如果之前退订了）
if ($existing['status'] === 'unsubscribed') {
$this->reactivateSubscriber($existing['id']);
}
return true;
}
// 开启事务
$this->db->beginTransaction();
// 插入订阅者
$stmt = $this->db->prepare("
INSERT INTO subscribers (email, name, status, source, ip_address)
VALUES (:email, :name, 'active', :source, :ip)
");
$stmt->execute([
'email' => $email,
'name' => $name,
'source' => $source,
'ip' => $_SERVER['REMOTE_ADDR'] ?? ''
]);
$subscriberId = $this->db->lastInsertId();
// 添加到分组
if (!empty($groups)) {
$this->addSubscriberToGroups($subscriberId, $groups);
}
$this->db->commit();
// 发送确认邮件（可选）
$this->sendConfirmationEmail($email, $name);
return true;
} catch (Exception $e) {
$this->db->rollBack();
$this->logError('Subscribe failed: ' . $e->getMessage());
return false;
}
}
// 取消订阅
public function unsubscribe(string $email): bool {
try {
$stmt = $this->db->prepare("
UPDATE subscribers
SET status = 'unsubscribed', unsubscribed_at = NOW()
WHERE email = :email
");
return $stmt->execute(['email' => $email]);
} catch (Exception $e) {
$this->logError('Unsubscribe failed: ' . $e->getMessage());
return false;
}
}
// 创建邮件模板
public function createTemplate(string $name, string $subject, string $content, array $variables = []): int {
// 预处理模板变量
$processedContent = $this->processTemplateVariables($content, $variables);
$stmt = $this->db->prepare("
INSERT INTO email_templates (name, subject, content, variables)
VALUES (:name, :subject, :content, :variables)
");
$stmt->execute([
'name' => $name,
'subject' => $subject,
'content' => $processedContent,
'variables' => json_encode($variables)
]);
return $this->db->lastInsertId();
}
// 创建发送任务
public function createCampaign(array $data): int {
$stmt = $this->db->prepare("
INSERT INTO campaigns (
name, template_id, group_ids, subject,
sender_name, sender_email, reply_to,
scheduled_at, status, created_by
) VALUES (
:name, :template_id, :group_ids, :subject,
:sender_name, :sender_email, :reply_to,
:scheduled_at, 'draft', :created_by
)
");
$stmt->execute([
'name' => $data['name'],
'template_id' => $data['template_id'],
'group_ids' => json_encode($data['group_ids']),
'subject' => $data['subject'] ?? '',
'sender_name' => $data['sender_name'] ?? $this->config['default_sender_name'],
'sender_email' => $data['sender_email'] ?? $this->config['default_sender_email'],
'reply_to' => $data['reply_to'] ?? '',
'scheduled_at' => $data['scheduled_at'] ?? null,
'created_by' => $data['created_by']
]);
return $this->db->lastInsertId();
}
// 准备邮件队列
public function prepareCampaignQueue(int $campaignId): int {
$campaign = $this->getCampaign($campaignId);
if (!$campaign || $campaign['status'] !== 'draft') {
return 0;
}
// 获取目标订阅者
$subscribers = $this->getCampaignSubscribers($campaign);
// 获取模板内容
$template = $this->getTemplate($campaign['template_id']);
$count = 0;
$this->db->beginTransaction();
try {
$stmt = $this->db->prepare("
INSERT INTO email_queue (campaign_id, subscriber_id, email, subject, content, status)
VALUES (:campaign_id, :subscriber_id, :email, :subject, :content, 'pending')
");
foreach ($subscribers as $subscriber) {
// 个性化处理
$personalized = $this->personalizeEmail($template, $subscriber);
$stmt->execute([
'campaign_id' => $campaignId,
'subscriber_id' => $subscriber['id'],
'email' => $subscriber['email'],
'subject' => $personalized['subject'],
'content' => $personalized['content']
]);
$count++;
}
// 更新任务统计
$this->db->prepare("
UPDATE campaigns
SET total_recipients = :count, status = 'scheduled'
WHERE id = :id
")->execute(['count' => $count, 'id' => $campaignId]);
$this->db->commit();
} catch (Exception $e) {
$this->db->rollBack();
$this->logError('Prepare queue failed: ' . $e->getMessage());
return 0;
}
return $count;
}
// 发送邮件（批量处理）
public function sendCampaignEmails(int $campaignId, int $limit = 50): array {
$result = ['sent' => 0, 'failed' => 0];
// 获取待发送的邮件
$stmt = $this->db->prepare("
SELECT * FROM email_queue
WHERE campaign_id = :campaign_id AND status = 'pending'
LIMIT :limit
");
$stmt->execute(['campaign_id' => $campaignId, 'limit' => $limit]);
$emails = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($emails as $email) {
try {
// 更新状态为发送中
$this->updateQueueStatus($email['id'], 'sending');
// 添加追踪像素
$trackingPixel = $this->generateTrackingPixel($email['id']);
$email['content'] = str_replace('</body>', $trackingPixel . '</body>', $email['content']);
// 替换链接为追踪链接
$email['content'] = $this->replaceLinksWithTracking($email['content'], $email['id']);
// 发送邮件
$sent = $this->mailer->send([
'to' => $email['email'],
'subject' => $email['subject'],
'body' => $email['content'],
'tracking_id' => $email['id']
]);
if ($sent) {
$this->updateQueueStatus($email['id'], 'sent', ['sent_at' => date('Y-m-d H:i:s')]);
$this->incrementCampaignCounter($campaignId, 'sent_count');
$result['sent']++;
} else {
throw new Exception('Failed to send email');
}
} catch (Exception $e) {
$this->handleSendFailure($email, $e->getMessage());
$result['failed']++;
}
// 速率限制
usleep(200000); // 200ms 延迟
}
// 检查是否完成
$this->checkCampaignCompletion($campaignId);
return $result;
}
// 处理退信
public function handleBounce(string $email, string $bounceType = 'hard'): void {
$status = $bounceType === 'hard' ? 'bounced' : 'soft_bounce';
$stmt = $this->db->prepare("
UPDATE subscribers SET status = :status WHERE email = :email
");
$stmt->execute(['status' => $status, 'email' => $email]);
// 更新发送队列
$this->db->prepare("
UPDATE email_queue
SET status = 'bounced', error_message = :type
WHERE email = :email AND status IN ('sent', 'pending')
")->execute(['type' => $bounceType, 'email' => $email]);
}
// 处理打开追踪
public function trackOpen(string $trackingId): void {
$stmt = $this->db->prepare("
UPDATE email_queue
SET status = 'opened', opened_at = NOW()
WHERE id = :id AND status = 'sent'
");
$stmt->execute(['id' => base64_decode($trackingId)]);
}
// 处理点击追踪
public function trackClick(string $trackingId, string $url): void {
$queueId = base64_decode($trackingId);
$stmt = $this->db->prepare("
INSERT INTO email_tracking (queue_id, subscriber_id, click_url, ip_address, user_agent)
SELECT :queue_id, subscriber_id, :url, :ip, :ua
FROM email_queue WHERE id = :queue_id2
");
$stmt->execute([
'queue_id' => $queueId,
'url' => $url,
'ip' => $_SERVER['REMOTE_ADDR'],
'ua' => $_SERVER['HTTP_USER_AGENT'],
'queue_id2' => $queueId
]);
// 更新状态
$this->db->prepare("
UPDATE email_queue SET status = 'clicked' WHERE id = :id AND status = 'opened'
")->execute(['id' => $queueId]);
}
// 生成统计报告
public function getCampaignStats(int $campaignId): array {
$campaign = $this->getCampaign($campaignId);
$stats = [
'total' => $campaign['total_recipients'],
'sent' => $campaign['sent_count'],
'opened' => $campaign['opened_count'],
'clicked' => $campaign['clicked_count'],
'bounced' => $campaign['bounced_count'],
'unsubscribed' => $campaign['unsubscribed_count'],
];
// 计算比率
$stats['open_rate'] = $stats['sent'] > 0 ? round($stats['opened'] / $stats['sent'] * 100, 2) : 0;
$stats['click_rate'] = $stats['opened'] > 0 ? round($stats['clicked'] / $stats['opened'] * 100, 2) : 0;
$stats['bounce_rate'] = $stats['total'] > 0 ? round($stats['bounced'] / $stats['total'] * 100, 2) : 0;
return $stats;
}
// 私有辅助方法
private function initMailer() {
// 初始化邮件发送器（支持SMTP、Sendmail等）
return new Mailer($this->config['mail']);
}
private function personalizeEmail(array $template, array $subscriber): array {
$variables = json_decode($template['variables'], true) ?? [];
$replacements = [
'{{name}}' => $subscriber['name'],
'{{email}}' => $subscriber['email'],
'{{unsubscribe_url}}' => $this->generateUnsubscribeUrl($subscriber['email']),
];
// 自定义变量替换
foreach ($variables as $var => $field) {
$replacements["{{{$var}}}"] = $subscriber[$field] ?? '';
}
$subject = strtr($template['subject'], $replacements);
$content = strtr($template['content'], $replacements);
return ['subject' => $subject, 'content' => $content];
}
private function generateTrackingPixel(int $queueId): string {
$encoded = base64_encode($queueId);
$url = $this->config['tracking_url'] . "/open/{$encoded}.gif";
return "<img src=\"{$url}\" width=\"1\" height=\"1\" alt=\"\" />";
}
private function replaceLinksWithTracking(string $content, int $queueId): string {
$encoded = base64_encode($queueId);
$trackingUrl = $this->config['tracking_url'] . "/click/{$encoded}?url=";
return preg_replace_callback(
'/href=["\'](https?:\/\/[^"\']+)["\']/i',
function($matches) use ($trackingUrl) {
return 'href="' . $trackingUrl . urlencode($matches[1]) . '"';
},
$content
);
}
private function handleSendFailure(array $email, string $error): void {
$attempts = $email['attempts'] + 1;
if ($attempts >= $email['max_attempts']) {
$status = 'failed';
} else {
$status = 'pending';
}
$this->db->prepare("
UPDATE email_queue
SET status = :status, attempts = :attempts, error_message = :error
WHERE id = :id
")->execute([
'status' => $status,
'attempts' => $attempts,
'error' => $error,
'id' => $email['id']
]);
}
private function checkCampaignCompletion(int $campaignId): void {
$stmt = $this->db->prepare("
SELECT COUNT(*) as pending
FROM email_queue
WHERE campaign_id = :id AND status IN ('pending', 'sending')
");
$stmt->execute(['id' => $campaignId]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
if ($result['pending'] == 0) {
$this->db->prepare("
UPDATE campaigns
SET status = 'completed', completed_at = NOW()
WHERE id = :id
")->execute(['id' => $campaignId]);
}
}
// ... 其他辅助方法
}
命令行发送处理器
<?php
// cron/send_emails.php
require_once __DIR__ . '/../bootstrap.php';
$system = new MailMarketingSystem($db, $config);
// 获取所有待发送的任务
$stmt = $db->prepare("
SELECT id FROM campaigns
WHERE status IN ('scheduled', 'sending')
AND (scheduled_at IS NULL OR scheduled_at <= NOW())
ORDER BY created_at ASC
");
$stmt->execute();
$campaigns = $stmt->fetchAll(PDO::FETCH_COLUMN);
foreach ($campaigns as $campaignId) {
// 更新状态为发送中
$db->prepare("UPDATE campaigns SET status = 'sending', started_at = NOW() WHERE id = :id")
->execute(['id' => $campaignId]);
// 分批发送
$batchSize = 50;
$totalSent = 0;
while (true) {
$result = $system->sendCampaignEmails($campaignId, $batchSize);
$totalSent += $result['sent'];
if ($result['sent'] == 0) {
break;
}
// 检查是否应该暂停（根据内存使用或时间限制）
if (memory_get_usage(true) > 100 * 1024 * 1024) { // 100MB
break;
}
}
echo "Campaign #{$campaignId}: Sent {$totalSent} emails\n";
}
订阅管理表单示例
<!-- subscribe_form.php -->
<!DOCTYPE html>
<html>
<head>订阅我们的邮件</title>
<style>
.subscribe-form { max-width: 400px; margin: 50px auto; padding: 20px; }
.form-group { margin-bottom: 15px; }
.form-group label { display: block; margin-bottom: 5px; }
.form-group input[type="email"],
.form-group input[type="text"] { width: 100%; padding: 8px; box-sizing: border-box; }
.submit-btn { background: #007bff; color: white; padding: 10px 20px; border: none; cursor: pointer; }
.message { padding: 10px; margin-bottom: 15px; border-radius: 4px; }
.success { background: #d4edda; color: #155724; }
.error { background: #f8d7da; color: #721c24; }
</style>
</head>
<body>
<div class="subscribe-form">
<?php if (isset($_GET['status'])): ?>
<?php if ($_GET['status'] === 'success'): ?>
<div class="message success">订阅成功！请查收确认邮件。</div>
<?php elseif ($_GET['status'] === 'exists'): ?>
<div class="message success">您已经订阅过了。</div>
<?php else: ?>
<div class="message error">订阅失败，请稍后重试。</div>
<?php endif; ?>
<?php endif; ?>
<h2>订阅我们的邮件通讯</h2>
<form method="POST" action="process_subscribe.php">
<div class="form-group">
<label for="name">姓名</label>
<input type="text" id="name" name="name" required>
</div>
<div class="form-group">
<label for="email">邮箱地址</label>
<input type="email" id="email" name="email" required>
</div>
<div class="form-group">
<label>感兴趣的主题</label><br>
<label><input type="checkbox" name="interests[]" value="news"> 新闻资讯</label><br>
<label><input type="checkbox" name="interests[]" value="promotions"> 优惠活动</label><br>
<label><input type="checkbox" name="interests[]" value="updates"> 产品更新</label>
</div>
<button type="submit" class="submit-btn">立即订阅</button>
</form>
<p style="margin-top: 20px; font-size: 12px; color: #666;">
我们尊重您的隐私，绝不会分享您的信息，您可以随时取消订阅。
</p>
</div>
</body>
</html>
退订处理页面
<?php
// unsubscribe.php
require_once 'config/database.php';
require_once 'classes/MailMarketingSystem.php';
$system = new MailMarketingSystem($db, $config);
$email = $_GET['email'] ?? '';
$token = $_GET['token'] ?? '';
// 验证退订令牌
if (empty($email) || empty($token) || !$system->verifyUnsubscribeToken($email, $token)) {
die('Invalid unsubscribe link');
}
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
if ($_POST['confirm'] === 'yes') {
$system->unsubscribe($email);
$message = '您已成功取消订阅。';
$success = true;
} else {
header('Location: /');
exit;
}
}
?>
<!DOCTYPE html>
<html>
<head>取消订阅</title>
</head>
<body>
<div style="max-width: 500px; margin: 50px auto; text-align: center; padding: 20px;">
<?php if (isset($message)): ?>
<h2><?= htmlspecialchars($message) ?></h2>
<p>如果您改变主意，随时可以重新订阅。</p>
<?php else: ?>
<h2>确定要取消订阅吗？</h2>
<p>您将停止接收来自我们所有的邮件通讯。</p>
<form method="POST">
<button type="submit" name="confirm" value="yes" style="background: #dc3545; color: white; padding: 10px 20px; border: none; cursor: pointer;">
确认取消订阅
</button>
<button type="submit" name="confirm" value="no" style="background: #6c757d; color: white; padding: 10px 20px; border: none; cursor: pointer; margin-left: 10px;">
保持订阅
</button>
</form>
<?php endif; ?>
</div>
</body>
</html>
API端点示例
<?php
// api/email_marketing.php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE');
header('Access-Control-Allow-Headers: Content-Type, Authorization');
require_once '../config/database.php';
require_once '../classes/MailMarketingSystem.php';
$system = new MailMarketingSystem($db, $config);
$method = $_SERVER['REQUEST_METHOD'];
$endpoint = $_GET['endpoint'] ?? '';
// API认证
function authenticate() {
$headers = getallheaders();
$token = $headers['Authorization'] ?? '';
// 验证API token
return $token === 'your-api-key';
}
if (!authenticate()) {
http_response_code(401);
echo json_encode(['error' => 'Unauthorized']);
exit;
}
switch ($endpoint) {
case 'subscribe':
if ($method === 'POST') {
$data = json_decode(file_get_contents('php://input'), true);
$result = $system->subscribe(
$data['email'],
$data['name'] ?? '',
$data['groups'] ?? [],
'api'
);
echo json_encode(['success' => $result]);
}
break;
case 'unsubscribe':
if ($method === 'POST') {
$data = json_decode(file_get_contents('php://input'), true);
$result = $system->unsubscribe($data['email']);
echo json_encode(['success' => $result]);
}
break;
case 'subscribers':
if ($method === 'GET') {
// 获取订阅者列表
$page = $_GET['page'] ?? 1;
$limit = $_GET['limit'] ?? 20;
$subscribers = $system->getSubscribers($page, $limit);
echo json_encode($subscribers);
}
break;
case 'campaigns':
if ($method === 'GET') {
$campaigns = $system->getCampaigns();
echo json_encode($campaigns);
} elseif ($method === 'POST') {
$data = json_decode(file_get_contents('php://input'), true);
$id = $system->createCampaign($data);
echo json_encode(['id' => $id]);
}
break;
case 'stats':
if ($method === 'GET') {
$campaignId = $_GET['campaign_id'];
$stats = $system->getCampaignStats($campaignId);
echo json_encode($stats);
}
break;
default:
http_response_code(404);
echo json_encode(['error' => 'Endpoint not found']);
}
部署建议
系统配置示例
<?php
// config/email_marketing.php
return [
'db' => [
'host' => 'localhost',
'dbname' => 'email_marketing',
'username' => 'root',
'password' => '',
],
'mail' => [
'driver' => 'smtp',
'host' => 'smtp.gmail.com',
'port' => 587,
'username' => 'your-email@gmail.com',
'password' => 'your-password',
'encryption' => 'tls',
],
'default_sender_name' => 'Your Company',
'default_sender_email' => 'noreply@yourcompany.com',
'tracking_url' => 'https://yourdomain.com/track',
'unsubscribe_url' => 'https://yourdomain.com/unsubscribe',
'sending' => [
'batch_size' => 50,
'delay_between_emails' => 200000, // 微秒
'max_attempts' => 3,
],
'limits' => [
'daily_send_limit' => 10000,
'max_recipients_per_campaign' => 5000,
'rate_limit_per_hour' => 1000,
],
];
维护计划
每日任务
- 清理失败的邮件队列
- 更新退信列表
- 生成每日报告
每周任务
- 清理无效/重复订阅者
- 优化数据库索引
- 检查发送限制
每月任务
- 归档旧数据
- 更新邮件模板
- 审查发送策略
安全注意事项
- 邮件验证：使用双重确认（Double Opt-in）
- 速率限制：防止滥用和服务器过载
- 数据加密：敏感信息加密存储
- 退订机制：必须包含一键退订
- 认证授权：API使用token认证
- 日志记录：记录所有操作以便审计
- 防注入：所有用户输入需要验证和清理
这个系统设计提供了完整的邮件营销功能,可以根据实际需求进行调整和扩展，建议在生产环境前进行充分的测试，特别是大规模发送时的性能和稳定性测试。
