我有两个表存储在两个不同的数据库中,我需要根据lastmodifieddate
和在两个表之间执行一些同步lastSyncAt
。我们称它们为db1
and db2
。这是表的 DDL 模式db1.reps
——需要通过同步更新的表:
CREATE TABLE `db1.reps` (
`veeva_rep_id` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`territories_id` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`display_name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`avatar_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'default_avatar.png',
`rep_type` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'VEEVA',
`username` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`first` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`last` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`title` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`bio` longtext COLLATE utf8_unicode_ci,
`phone` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
`inactive` tinyint(1) NOT NULL DEFAULT '0',
`lastLoginAt` datetime DEFAULT NULL,
`lastSyncAt` datetime NOT NULL,
`repTokenId` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`createdAt` datetime NOT NULL,
`updatedAt` datetime NOT NULL,
PRIMARY KEY (`veeva_rep_id`),
KEY `IDX_485DE7B033B9A304` (`territories_id`),
CONSTRAINT `FK_485DE7B033B9A304` FOREIGN KEY (`territories_id`) REFERENCES `territories` (`veeva_territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
这是 DDL db2.user
- 一种大师:
CREATE TABLE `user` (
`id` varchar(18) NOT NULL,
`username` varchar(80) DEFAULT NULL,
`lastname` varchar(80) DEFAULT NULL,
`firstname` varchar(40) DEFAULT NULL,
`email` varchar(128) DEFAULT NULL,
`lastmodifieddate` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我可以通过编程来做到这一点,但我想知道并学习,如果可能的话,通过 SQL 来做到这一点。我需要,foreach 行,on db2.user
perform an UPDATE on db1.reps
but just when db2.user.lastmodifieddate > db1.reps.lastSyncAt
,如何?我必须清楚,如果在其上运行了db1.reps.lastSyncAt
更新,则应该将其更新NOW()
。这是我到目前为止所做的:
INSERT INTO db1.reps (
display_name,
username,
`first`,
`last`,
email,
lastSyncAt
)(
SELECT
CONCAT(
LCAPITAL (firstname),
' ',
LCAPITAL (lastname)
) AS display_name,
username,
LCAPITAL (firstname),
LCAPITAL (lastname),
email,
NOW()
WHERE
db1.reps.veeva_rep_id = db2.`user`.id
)
WHERE
db2.`user`.lastmodifieddate > db1.reps.lastSyncAt ON DUPLICATE KEY UPDATE db1.reps.display_name =
VALUES
(
CONCAT(
LCAPITAL (db2.`user`.firstname),
' ',
LCAPITAL (db2.`user`.lastname)
)
),
db1.reps.username =
VALUES
(db2.`user`.username),
db1.reps.`first` =
VALUES
(LCAPITAL(db2.`user`.firstname)),
db1.reps.`last` =
VALUES
(LCAPITAL(db2.`user`.lastname)),
db1.reps.email =
VALUES
(db2.`user`.email)
db1.reps.lastSyncAt =
VALUES
(NOW())
可以给我一些帮助吗?
编辑
因为db2.user
有重复项(一个完全没有限制的表 - 见下图)我需要在执行之前清理一下触发器。
这就是我在 SQL 方面的工作方式:
SELECT
count(*) AS reps,
userid,
lastmodifieddate,
territoryid
FROM
(
SELECT
userid,
territoryid,
count(*) AS territories
FROM
userterritory
GROUP BY
userid
HAVING
territories = 1
) T1
INNER JOIN (
SELECT
id AS userid,
lastmodifieddate
FROM
`user`
WHERE
`user`.`id` IN (
SELECT
userterritory.`userid`
FROM
userterritory
)
) T2 USING (userid)
GROUP BY
territoryid
HAVING
reps = 1
上面的查询向我保证只是在一个地区的代表应该是。我应该如何修改触发器来完成这个?
注意:LCAPITAL()
是我用于规范化字符串的函数
您可以在更新时使用 a
TRIGGER
进行更新。db1.reps.lastSyncAt
db2.user.lastmodifieddate
扳机:
如果你想在表中添加任何行时
INSERT
进入表:reps
user
顺便说一下,您必须更改表的数据类型,因为 75-80% 只是
VARCHARs
. 我制作的功能LCAPITAL
可以与 1 个以上的单词一起使用,例如:SELECT test.LCAPITAL(CONCAT('oanRE',' ','AnTIGUA'));
结果: