通过电子邮件发送的 MySql 查询

Question

我有一个简单的查询，它从几个不同的表中选择一些字段，我需要它每月运行一次。我知道我可以使用 CREATE EVENT 安排每月的“工作”，但是，是否可以在查询运行后将该信息通过电子邮件发送到某些地址？这样我就不需要登录服务器查看新文件了？

Answer 1

我认为Mysql不支持发送Email。

在这种情况下，您可以开发一个辅助程序来发送创建的文件，并使用 - 计划任务、Cron ...(这取决于您使用的服务器的操作系统)执行它。

辅助程序可以像this code添加要附加的文件(attachFiles 变量)。

public class EmailAttachmentSender {

public static void sendEmailWithAttachments(String host, String port,
        final String userName, final String password, String toAddress,
        String subject, String message, String[] attachFiles)
        throws AddressException, MessagingException {
    // sets SMTP server properties
    Properties properties = new Properties();
    properties.put("mail.smtp.host", host);
    properties.put("mail.smtp.port", port);
    properties.put("mail.smtp.auth", "true");
    properties.put("mail.smtp.starttls.enable", "true");
    properties.put("mail.user", userName);
    properties.put("mail.password", password);

    // creates a new session with an authenticator
    Authenticator auth = new Authenticator() {
        public PasswordAuthentication getPasswordAuthentication() {
            return new PasswordAuthentication(userName, password);
        }
    };
    Session session = Session.getInstance(properties, auth);

    // creates a new e-mail message
    Message msg = new MimeMessage(session);

    msg.setFrom(new InternetAddress(userName));
    InternetAddress[] toAddresses = { new InternetAddress(toAddress) };
    msg.setRecipients(Message.RecipientType.TO, toAddresses);
    msg.setSubject(subject);
    msg.setSentDate(new Date());

    // creates message part
    MimeBodyPart messageBodyPart = new MimeBodyPart();
    messageBodyPart.setContent(message, "text/html");

    // creates multi-part
    Multipart multipart = new MimeMultipart();
    multipart.addBodyPart(messageBodyPart);

    // adds attachments
    if (attachFiles != null && attachFiles.length > 0) {
        for (String filePath : attachFiles) {
            MimeBodyPart attachPart = new MimeBodyPart();

            try {
                attachPart.attachFile(filePath);
            } catch (IOException ex) {
                ex.printStackTrace();
            }

            multipart.addBodyPart(attachPart);
        }
    }

    // sets the multi-part as e-mail's content
    msg.setContent(multipart);

    // sends the e-mail
    Transport.send(msg);

}

/**
 * Test sending e-mail with attachments
 */
public static void main(String[] args) {
    // SMTP info
    String host = "smtp.gmail.com";
    String port = "587";
    String mailFrom = "your-email-address";
    String password = "your-email-password";

    // message info
    String mailTo = "your-friend-email";
    String subject = "New email with attachments";
    String message = "I have some attachments for you.";

    // attachments
    String[] attachFiles = new String[3];
    attachFiles[0] = "e:/Test/Picture.png";
    attachFiles[1] = "e:/Test/Music.mp3";
    attachFiles[2] = "e:/Test/Video.mp4";

    try {
        sendEmailWithAttachments(host, port, mailFrom, password, mailTo,
            subject, message, attachFiles);
        System.out.println("Email sent.");
    } catch (Exception ex) {
        System.out.println("Could not send email.");
        ex.printStackTrace();
    }
}