日常工作中我们经常会遇到一些临时数据处理需求,常规的编程手段虽然可以解决, 但通常时间紧迫需要快速处理上线,这时便可以考虑用纯SQL方式, 如Oracle的 PL/SQL 脚本又或是存储过程, 这里整理记录一下线上数据处理流程。
需求引入
某天产品经理突然丢过来一个 Excel 让把其中的部门信息导入到线上的一个企业里,从产品设计角度来讲这应该算是一个功能需求了,但遗憾的是目前并没有这样的功能,只能临时处理,当前部门表结构如下
1 2 3 4 5 6 7
| CREATE TABLE `t_department` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '部门id', `name` VARCHAR(64) NULL DEFAULT NULL COMMENT '部门名称', `parent_id` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT '父部门id', PRIMARY KEY (`id`) USING BTREE );
|
给定的 Excel 数据内容类似如下
1 2 3 4 5
| /a /a/b /a/b/c /a/d /a/d/e
|
处理流程
graph LR
A(分析需求) --> B(构建中间表) --> C(编写存储过程) --> D(导出执行)
数据处理无外乎增删改,但实际需求复杂多变通常需要配合中间表协同处理,本次需求已知全路径信息现需要写入部门表中。
构建中间表
拿到需求数据后首先要考虑讲数据导入至中间表中,以方便后续的处理
- 表结构设计,一般直接按需求数据列设计,本次需求只有单列数据,故可以先设计的中间表名为
t_temp_data
, 列有自增主键id
全路径数据full_path
此列设置为唯一键
- 将数据导入至中间表中,一般SQL工具都有导入csv数据的功能,可以自行摸索
导入后数据显示如下
id |
full_path |
1 |
/a |
2 |
/a/b |
… |
… |
编写存储过程
存储过程的编写依赖于业务的逻辑分析,拆解之后处理步骤如下
graph LR
A(读取中间表数据) --> B(拆分全路径)
B --> C(父路径)
C --> D(查找父路径ID)
B --> E(部门名称)
E --> F(写入数据)
D --> F
通常数据单条处理操作主要使用游标来进行处理,本例中处理结构如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| BEGIN DECLARE v_id INT; DECLARE v_path VARCHAR(128); DECLARE v_done INT DEFAULT FALSE; DECLARE v_dep CURSOR FOR SELECT id, full_path FROM t_temp_data; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; OPEN v_dep; v_loop: LOOP FETCH v_dep INTO v_id, v_path; IF v_done THEN LEAVE v_loop; END IF; END LOOP; CLOSE v_dep; END
|
接下来要完善数据处理逻辑,按之前分析需要将全路径拆分为父路径和子路径,这就需要对 MySQL 字符串函数有一定了解学习,经过甄别后最终选出了两个适用函数
1 2 3
| SELECT SUBSTRING_INDEX('/a/b/c', '/', 2), REGEXP_SUBSTR('/a/b/c', '[^/]+', 1, 2) UNION ALL SELECT SUBSTRING_INDEX('/a/b/c', '/', 3), REGEXP_SUBSTR('/a/b/c', '[^/]+', 1, 3);
|
SUBSTRING_INDEX(‘/a/b/c’, ‘/‘, 2) |
REGEXP_SUBSTR(‘/a/b/c’, ‘[^/]+’, 1, 2) |
/a |
b |
/a/b |
c |
通过输出效果可以看到两个函数成功的把数据分离了父路径和子路径,最后一个参数作为剥离标志位,可以对指定层级进行剥离,有了这个铺垫后可以编写如下相关逻辑
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
| DECLARE v_index INT; DECLARE v_insert_id INT;
DECLARE v_curent_path VARCHAR(128);
DECLARE v_name VARCHAR(128);
DECLARE v_parent_id INT;
DECLARE v_parent_path VARCHAR(128); ... SET v_index = 1; v_process:LOOP SET v_index = v_index + 1; SELECT REGEXP_SUBSTR(v_path, '[^/]+', 1, v_index) INTO v_name; IF v_name IS NULL THEN LEAVE v_process; END IF; SELECT SUBSTRING_INDEX(v_path, '/', v_index + 1) INTO v_curent_path; IF EXISTS(SELECT 1 FROM t_temp_data WHERE full_path = v_curent_path AND dep_id IS NULL) THEN SELECT dep_id INTO v_parent_id FROM t_temp_data WHERE full_path = SUBSTRING_INDEX(v_path, '/', v_index); INSERT INTO `t_department` (`name`, `parent_id`) VALUES (v_name, v_parent_id); SELECT LAST_INSERT_ID() INTO v_insert_id; INSERT INTO t_temp_data(full_path, dep_id) VALUES(v_curent_path, v_insert_id) ON DUPLICATE KEY UPDATE dep_id = v_insert_id; END IF; END LOOP; ...
|
由于当前顺序处理逻辑导致,存储过程中可以看到中间表新增了一列dep_id
,增加这一列可以有以下好处
- 位置
(1)
做跳过判断,可以避免重复的父路径数据写入
- 支持位置
(2)
父路径的ID查询
- 相对应的需要在位置
(3)
处将新ID更新写入
- 由于处理逻辑是顺序处理,因此需要手动把根节点id赋值给中间表,本例中设置为 1
如此就完成了处理逻辑,接下来将存储结构和中间表用mysqldump
或SQL编辑器
导出,导出结果类似如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
| DROP PROCEDURE IF EXISTS `p_dep_insert`; DELIMITER // CREATE PROCEDURE `p_dep_insert`() BEGIN DECLARE v_id INT; DECLARE v_path VARCHAR(128); DECLARE v_index INT; DECLARE v_insert_id INT; DECLARE v_curent_path VARCHAR(128); DECLARE v_name VARCHAR(128); DECLARE v_parent_id INT; DECLARE v_parent_path VARCHAR(128); DECLARE v_done INT DEFAULT FALSE; DECLARE v_dep CURSOR FOR SELECT id, full_path FROM t_temp_data; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE; OPEN v_dep; v_loop: LOOP FETCH v_dep INTO v_id, v_path; IF v_done THEN LEAVE v_loop; END IF; SET v_index = 1; v_process:LOOP SET v_index = v_index + 1; SELECT REGEXP_SUBSTR(v_path, '[^/]+', 1, v_index) INTO v_name; IF v_name IS NULL THEN LEAVE v_process; END IF; SELECT SUBSTRING_INDEX(v_path, '/', v_index + 1) INTO v_curent_path; IF EXISTS(SELECT 1 FROM t_temp_data WHERE full_path = v_curent_path AND dep_id IS NULL) THEN SELECT dep_id INTO v_parent_id FROM t_temp_data WHERE full_path = SUBSTRING_INDEX(v_path, '/', v_index); INSERT INTO `t_department` (`name`, `parent_id`) VALUES (v_name, v_parent_id); SELECT LAST_INSERT_ID() INTO v_insert_id; INSERT INTO t_temp_data(full_path, dep_id) VALUES(v_curent_path, v_insert_id) ON DUPLICATE KEY UPDATE dep_id = v_insert_id; END IF; END LOOP; END LOOP; CLOSE v_dep; END// DELIMITER ;
DROP TABLE IF EXISTS `t_temp_data`; CREATE TABLE IF NOT EXISTS `t_temp_data` ( `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID', `full_path` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '全路径', `dep_id` int DEFAULT NULL COMMENT '写入ID', PRIMARY KEY (`id`), UNIQUE KEY `uk_full_path` (`full_path`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `t_temp_data` (`id`, `full_path`, `dep_id`) VALUES (1, '/a', 1), (2, '/a/b', NULL), (3, '/a/b/c', NULL), (4, '/a/d', NULL), (5, '/a/d/e', NULL);
|
导出之后追加如下调用及清理语句,之后发给相关人员在线上执行即可
1 2 3
| CALL p_insert_dep(); DROP TABLE IF EXISTS `t_temp_data`; DROP PROCEDURE IF EXISTS `p_dep_insert`;
|
写在最后
通过灵活的运用相关知识可以从容有效的应对复杂的需求,当然这次的需求处理还有其他思路,比如例子中用的是顺序处理,实际也可用倒序处理方式,这样可以降低父路径遍历写入的开销,当然也可用常规的编程方式例如用Spring Shell
代码处理,方法思路多种多样,条条大路通罗马希望本篇可以带你到罗马。