mysql - Union 2 tables inner join on the third with 2 legacy applications

Question

我有一个连接到 mySQL 数据库的遗留 Access 前端。遗留应用程序有很多分配给 onclose 触发器的危险宏。我还有一个在同一个数据库上运行的开发中的 Web 应用程序。 Web 应用程序中有几个模块正在生产中使用。我的测试是在一台单独的开发机器上完成的，它有一个单独的专用开发版本的数据库。

我正在安装到我的网络应用程序中的一个新模块带有它自己的一组表。它会愉快地存在于同一个数据库中，但希望它在自己的表中拥有自己的数据副本。我犹豫要不要大量修改该模块的新表或代码库。

遗留数据库中共有 6 个表保存不同对象的相似数据。我现在只处理这些表中最重要的两个。以下仅代表这 2 个表中的一小部分列。

CREATE TABLE IF NOT EXISTS `agent` (
`age_id` int(11) NOT NULL AUTO_INCREMENT,
`age_agent_email_address` varchar(255) DEFAULT NULL,
`age_welcome_email_sent_y_or_n` varchar(255) DEFAULT 'No',
`age_status` varchar(255) DEFAULT 'Active',
PRIMARY KEY (`age_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=1854 ;

和

CREATE TABLE IF NOT EXISTS `prospecting_contacts` (
`psp_prospect_id` varchar(255) NOT NULL DEFAULT '',
`psp_prospecting_status` varchar(255) DEFAULT 'Active',
`psp_prospect_email_address` varchar(255) DEFAULT NULL,
`psp_remove_from_email_marketing` varchar(255) DEFAULT 'No',
`psp_id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`psp_id`) USING BTREE,
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=2050793 ;

新模块附带了几个相关的表格。我相信只有其中一个需要更新。

CREATE TABLE IF NOT EXISTS `phplist_user_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) CHARACTER SET latin1 NOT NULL,
`confirmed` tinyint(4) DEFAULT '0',
`blacklisted` tinyint(4) DEFAULT '0',
`bouncecount` int(11) DEFAULT '0',
`entered` datetime DEFAULT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`uniqid` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`htmlemail` tinyint(4) DEFAULT '0',
`subscribepage` int(11) DEFAULT NULL,
`rssfrequency` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`password` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`passwordchanged` date DEFAULT NULL,
`disabled` tinyint(4) DEFAULT '0',
`extradata` text CHARACTER SET latin1,
`foreignkey` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
`optedin` tinyint(4) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
KEY `foreignkey` (`foreignkey`),
KEY `idx_phplist_user_user_uniqid` (`uniqid`),
KEY `emailidx` (`email`),
KEY `enteredindex` (`entered`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

php_list_user_user 表将包含此查询结果的数据:

SELECT `age_agent_email_address` AS `email` FROM `agent` 
    WHERE `age_status` = 'Active'
UNION DISTINCT
SELECT `psp_prospect_email_address` FROM `prospecting_contacts`
    WHERE `psp_prospecting_status` = 'Active'

遗留访问应用程序更新了 agent 和 prospecting_contacts 表。新模块更新了 php_list_user_user 表。我相信我可以使用 TRIGGER 来回复制信息。但是，我正在寻找一种不会重复数据的方法。

我想到了 CREATE VIEW，但是 mysql 手册说 union 和 join 破坏了它的更新能力。 http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html

那么，有没有办法在不重复数据的情况下更新这3张表呢？或者我应该只复制电子邮件地址并在 INSERT 和 UPDATE 上使用 TRIGGER？

Answer 1

虽然外键更适合于保持表的一致性而不是防止重复，但您也许可以对外键做一些巧妙的事情。 http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

这似乎违反直觉，但另一种解决方案是维护一个查找表，该表指示可以在何处找到特定值。您可以加入所有三个(子)表以防止重复。

触发器也可以。