我应该如何存储外发电子邮件，以便收件人也可以通过链接阅读电子邮件？
简介
好的，我有一个 Web 应用程序每天发送大量不同的电子邮件。我想在电子邮件中添加“如果您无法阅读此电子邮件，请单击此处”链接。
我想知道如何最好地存储它，以及其他人是如何做到的。
问题
今天我有一个像这样的数据库表：
CREATE TABLE IF NOT EXISTS `mydb`.`httpsemail` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`from` VARCHAR(255) NOT NULL ,
`to` VARCHAR(255) NOT NULL ,
`subject` VARCHAR(255) NOT NULL ,
`body` MEDIUMTEXT NULL ,
`hash` CHAR(60) NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB
电子邮件内容是从许多不同的模板生成的，并根据收件人插入不同的客户相关数据。
该表很快增长到数万行（每发送一封电子邮件一行），大小为几 GB。
我什至不确定这是一个问题。我总是通过主键检索电子邮件，因此速度很快。硬盘很大。但是，备份速度很慢。
可能的解决方案/改进
可能的改进是当用户单击链接时重新生成电子邮件的内容，而不是将生成的结果存储在数据库中。但是，输入可能会同时发生变化，我希望电子邮件被“修复” - 这意味着用户应该看到电子邮件在发送时的样子。
另一个改进可能是自动删除 X 天之前存储的电子邮件。然而，这意味着链接有一天会停止工作，具体取决于 X。较低的 X 有利于可用空间，但对用户不利。如何决定X？
想法？
Introduction
Ok, so I have a web application that sends a lot of different emails every day. I want to include a "Click here if you can't read this email" link in the emails.
I'm wondering how to best store this, and how other people do it.
Problem
Today I have a database table like this:
CREATE TABLE IF NOT EXISTS `mydb`.`httpsemail` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`from` VARCHAR(255) NOT NULL ,
`to` VARCHAR(255) NOT NULL ,
`subject` VARCHAR(255) NOT NULL ,
`body` MEDIUMTEXT NULL ,
`hash` CHAR(60) NOT NULL ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB
The email contents are generated from many different templates with different customer-related data inserted depending on the recipient.
The table quickly grows to tens of thousands of rows (one row per sent email), with a size of several gigabytes.
I'm not even sure this is a problem. I always retrieve emails by primary key so it's fast. The hard drive is big. However, backups are slow.
Possible solutions/improvements
A possible improvement is to regenerate the contents of the email when the user clicks the link instead of storing the generated result in the database. However, the inputs may change in the meantime, and I want the email to be "fixed" - meaning that the user should see the email as it was at the time of sending.
Another improvement might be to automatically delete stored emails older than X days. However, this means the link will stop working some day, depending on the X. A lower X is good for free space, but bad for the user. How to decide X?
Thoughts?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您仍然可以生成每个视图的副本，但您必须在生成电子邮件时将所有变量数据的副本复制到单独的表中。这将节省您必须保留每个用户的副本，但仍然允许重新生成相同的电子邮件，即使原始父表中的某些数据同时已更改。
至于有效期，就看你自己了。几个月是合理的，但这也取决于电子邮件中的数据 - 将销售传单存储的时间超过销售期限就没有多大意义。
You can still generate a per-view copy, but you'll have to make a copy of all the variable data into a seperate table at the time the email is generated. That would save you have to keep a per-user copy around, but still allow regenerating the same email, even though some of the data has changed in the original parent table in the meantime.
As for the expiry period, that's up to you. a couple months would be reasonable, but it would also depend on the data in the email as well - storing a sale flyer for longer than the sale period wouldn't make much sense.