mysql - 如何使用 id 从一个表更新两个表到另一个表?
问题描述
我正在编写一个 Node JS 脚本,我想用它来更新远程数据库中的 2 个表。到目前为止,该脚本执行以下操作:
- 它连接到远程数据库,选择自给定时间以来的新值。
- 它连接到本地数据库,将新的/编辑的行插入或更新到表 A。
- 使用表 A 中的 id 作为主键更新或插入新行到第二个表。我已经完成了任务 1 和 2。
到目前为止我的代码:
let mysql = require('mysql');
let con = mysql.createConnection({ //Server A
host: "192.168.1.10",
user: "user1",
password: "pswd1",
database: "a_database"
});
let con2 = mysql.createConnection({ //Server B
host: "192.168.1.11",
user: "user2",
password: "pswd2",
database: "b_database"
});
let sqlSelectDatabaseA = "SELECT uid,name,email,city,street,number FROM users \n" +
"WHERE uid IN (SELECT DISTINCT u.uid FROM logs l INNER JOIN users u ON l.id = u.uid ORDER BY uid;";
let sqlSelectId = "SELECT max(id) AS ID FROM addresses";
let sqlInsertUsers = "INSERT INTO users (username,email,mobile,city,street,client_number,contactAddress) VALUES ? ON DUPLICATE KEY UPDATE \n" +
"username=VALUES(username),email=VALUES(email),mobile=VALUES(mobile),city=VALUES(city),street=VALUES(street);"; //I've solved the insert/update in this sql command
let sqlInsertAddresses = "INSERT INTO addresses (country,city,street,number) VALUES ?"; //Here I would need update too
con.connect(async function(err) {
if (err) throw err;
con.query(sqlSelectDatabaseA, function (err, result) {
if (err) throw err;
con2.connect(function(err) {
if (err) throw err;
con2.query(sqlSelectId, function (err, result2) {
if (err) throw err;
let addressId = result2[0].ID;
let values1 = [];
let values2 = [];
for (let i = 0; i < result.length; i++) {
addressId++;
values1[i] = [result[i].city,result[i].street,result[i].number];
values2[i] = [result[i].name,result[i].email,result[i].city,result[i].street + ' ' + result[i].number,result[i].uid,++addressId];
}
con2.query(sqlInsertAddresses, [values1], function (err, result3) {
if (err)
throw err;
console.log("Number of records inserted/updated in addresses table : " + result3.affectedRows); //Now I only insert the addresses, don't check them if they exist, this would be the task, to achieve a check if it exists or not, if exists, update it, if not, insert it
con2.query(sqlInsertUsers, [values2], function (err, result4) {
if (err) throw err;
console.log("Number of records inserted/updated in users table: " + result4.affectedRows);
con.end();
con2.end();
});
});
});
});
});
});
我想找到一个解决方案,将 users.contactAddress 的值用作地址表上的 PRIMARY KEY。如果具有给定主键的记录存在,它只更新值,如果不存在,则插入新记录。
如果可能,无需新的 sql 命令。但如果没有其他办法,那也行。
例子:
- 在日志表中出现了一个新行,它看起来像 |logId|userId|
- 脚本使用这一行的 userId,连接到数据库 A,从 users 表中获取新值
- 使用数据库 A 中的值,脚本插入或更新数据库 B 中的用户表
- 根据是否发生更新或插入,它向地址表添加一行,或更新地址表中 id (PRIMARY KEY) 等于更新用户记录的contactAddress 字段的值
CREATE TABLE IF NOT EXISTS usersA (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`mobile` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
`street` varchar(255) NOT NULL,
`number` bigint(20) NOT NULL,
PRIMARY KEY (`uid`)
);
CREATE TABLE IF NOT EXISTS logsA (
`logId` int(11) NOT NULL AUTO_INCREMENT,
`userId` varchar(255) NOT NULL,
PRIMARY KEY (`logId`)
);
CREATE TABLE IF NOT EXISTS addressesB (
`id` int(11) NOT NULL AUTO_INCREMENT,
`city` varchar(255) NOT NULL,
`street` varchar(255) NOT NULL,
`buildingNumber` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS usersB (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL,
`mobile` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
`street` varchar(255) NOT NULL,
`klient_number` bigint(20) NOT NULL,
`contactAddress` bigint(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `klient_number` (`klient_number`)
);
INSERT INTO usersA (username,email,mobile,city,street,number) VALUES("John Smith", "john.smith@gmail.com", "0000", "city", "street", 15);
INSERT INTO usersA (username,email,mobile,city,street,number) VALUES("Kate Smith", "kate.smith@gmail.com", "0000", "city_updated", "street1", 11);
INSERT INTO usersA (username,email,mobile,city,street,number) VALUES("Will Smith", "will.smith@gmail.com", "0000", "city2", "street2", 6);
INSERT INTO usersB (username,email,mobile,city,street,klient_number, contactAddress) VALUES("John Smith", "john.smith@gmail.com", "0000", "city", "street 15", 1, 1);
INSERT INTO usersB (username,email,mobile,city,street,klient_number, contactAddress) VALUES("Kate Smith", "kate.smith@gmail.com", "0000", "city 1", "street1 11", 2, 2);
INSERT INTO addressesB (city, street, buildingNumber) VALUES ("city", "street", "15");
INSERT INTO addressesB (city, street, buildingNumber) VALUES ("city 1", "street1", "11");
INSERT INTO logsA (userId) VALUES (2);
INSERT INTO logsA (userId) VALUES (3);
期望的结果:脚本从 usersA 表中选择用户,检查 usersB 表中的用户。如果在 usersA 表中存在与 PRIMARY KEY 相同的 kclient_number 的记录(so,uid),则更新,否则插入。它在地址表中也是如此。
谢谢您的帮助!
解决方案
也许你追求的是这样的:
INSERT INTO usersB
(username
,mobile
,email
,city
,street
,klient_number)
SELECT username
, mobile
, email
, city
, CONCAT_WS(' ',street,number)
, uid
FROM usersA a
ON DUPLICATE KEY
UPDATE username = a.username
, mobile = a.mobile
, email = a.email
, city = a.city
, street = CONCAT_WS(' ',a.street,a.number)
, klient_number = a.uid;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3 Duplicates: 1 Warnings: 0
SELECT * FROM usersB;
+----+------------+--------+----------------------+--------------+------------+---------------+----------------+
| id | username | mobile | email | city | street | klient_number | contactAddress |
+----+------------+--------+----------------------+--------------+------------+---------------+----------------+
| 1 | John Smith | 0000 | john.smith@gmail.com | city | street 15 | 1 | 1 |
| 2 | Kate Smith | 0000 | kate.smith@gmail.com | city_updated | street1 11 | 2 | 2 |
| 3 | Will Smith | 0000 | will.smith@gmail.com | city2 | street2 6 | 3 | 0 |
+----+------------+--------+----------------------+--------------+------------+---------------+----------------+
推荐阅读
- reactjs - react native submit pro 报错
- python - 如果我有一个生成器对象作为数据,我如何将其更改为某种格式,以允许我从 Excel 工作表中取消合并列?
- python - 如何从文本中提取日期(任何格式)?
- flutter - 构建应用程序时颤动file_picker包错误
- ifc - IfcClosedShell 创建
- image - flutter release APK不显示图片资源
- r - 当我们在 R 中有 r 和 p 值时,如何为 pearsons 计算多重相关的回归线斜率线
- python - 使用 django 在 digitalocean 上动态添加 cron 任务
- python - 如何使用 django-background-task 模块使用 Supervisord
- docfx - 如何配置 DOCFX 以在每个页面上显示源代码