【MySQL】下发功能SQL

SQL参考文章：

https://www.jb51.net/article/15627.htm

下发，就是从别的表中同步数据到此表中，也可能是来自不同库的表，或者不同实例的表

下发的逻辑要求：如果没有则是做插入，存在冲突的记录，则需要覆写更新，同时下发表要做出标记，不能重复下发，下发过的记录要做标记

 

案例：

A表：

CREATE TABLE `a` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `A` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `B` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `C` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

B表：

CREATE TABLE `b` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `D` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `E` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `F` varchar(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

首先A插入数据，然后下发给B表：

INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (1, '1', '1', '1');
INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (2, '2', '2', '2');
INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (3, '3', '3', '3');
INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (4, '4', '4', '4');
INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (5, '5', '5', '5');
INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (6, '6', '6', '6');
INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (7, '7', '7', '7');
INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (8, '8', '8', '8');
INSERT INTO `my-info`.`a` (`ID`, `A`, `B`, `C`) VALUES (9, '9', '9', '9');

 

B表为空，则可以直接向里面插入即可：（把A的全部数据向B表写入，主键为自增，不需要加入）

INSERT INTO `my-info`.`b` (`D`, `E`, `F`) SELECT `A`, `B`, `C` FROM `my-info`.`a`

现在A和B表一样的数据， 更新一条A表的数据：

UPDATE `my-info`.`a` SET `A` = '100', `B` = '100', `C` = '100' WHERE `ID` = 5;

 

A表 5ID的记录是100

+----+-----+-----+-----+
| ID | A   | B   | C   |
+----+-----+-----+-----+
|  1 | 1   | 1   | 1   |
|  2 | 2   | 2   | 2   |
|  3 | 3   | 3   | 3   |
|  4 | 4   | 4   | 4   |
|  5 | 100 | 100 | 100 |
|  6 | 6   | 6   | 6   |
|  7 | 7   | 7   | 7   |
|  8 | 8   | 8   | 8   |
|  9 | 9   | 9   | 9   |
+----+-----+-----+-----+
9 rows in set (0.04 sec)

把A的表记录下发到B表，操作：（全选A表的记录，包括主键或者约束键字段，写入到B表中，在主键发生冲突时，更新字段）

INSERT INTO `my-info`.`b` (`ID`, `D`, `E`, `F`) 
SELECT `ID`, `A`, `B`, `C` FROM `my-info`.`a`
ON DUPLICATE KEY UPDATE `D` = `A`, `E` = `B`, `F` = `C`

SQL语法是支持的

> Affected rows: 2
> 时间: 0.17s

查看B表，结果是修改了五ID的记录

mysql> SELECT * FROM `my-info`.`B`;
+----+-----+-----+-----+
| ID | D   | E   | F   |
+----+-----+-----+-----+
|  1 | 1   | 1   | 1   |
|  2 | 2   | 2   | 2   |
|  3 | 3   | 3   | 3   |
|  4 | 4   | 4   | 4   |
|  5 | 100 | 100 | 100 |
|  6 | 6   | 6   | 6   |
|  7 | 7   | 7   | 7   |
|  8 | 8   | 8   | 8   |
|  9 | 9   | 9   | 9   |
+----+-----+-----+-----+
9 rows in set (0.07 sec)

 

 

-- 实现不重复下发的逻辑问题：

A表再追加一个下发标记字段，在执行下发SQL的时候，筛选这个下发条件

INSERT INTO `my-info`.`b` (`ID`, `D`, `E`, `F`) 
SELECT `ID`, `A`, `B`, `C` FROM `my-info`.`a` WHERE `下发标记字段` = '未下发的状态值'
ON DUPLICATE KEY UPDATE `D` = `A`, `E` = `B`, `F` = `C`

执行下发之后，要更新A表的下发状态：

把标记为未下发的记录，更新为已下发

UPDATE `my-info`.`a` SET `下发标记字段` = '已下发的状态值' WHERE `下发标记字段` = '未下发的状态值';

以避免重复进行下发，或者是业务逻辑指定的区分方式来实现

 

另一种情况：

ON DUPLICATE KEY UPDATE

更新操作是依据这个SQL组合实现，即重复的Key

Key可以是主键，或者是索引。

主表的单列主键不需要下发到下发表，但是需要其他其他关键字段触发DUPLICATE KEY 

可以对字段添加唯一索引实现（唯一索引允许字段NULL值，需要字段设置NOT NULL限制）

ALTER TABLE `table_name` ADD UNIQUE unq_col1_col2 (`column1`, `column1`);

通过UNIQUE索引触发 DUPLICATE实现更新操作

 

删除索引：

drop index index_name on table_name ;

alter table table_name drop index index_name ;