记一次MySQL历史数据初始化的问题

前言

先说下背景吧,在某项目迭代过程中,由于系统设计问题,需要对b表新增加两个字段(证件类型type和证件号码number),这本来是一件很平常的事情。

但是对于b表的历史数据,需要对其进行初始化维护,也就是历史数据要赋值,这理论上也是一件简单的事情。

证件信息数据要从a表获取,但是a表中对于一个用户(userId)可以有多种类型证件,且证件可以被修改。

对于b表,只需要一种证件,它遵循一个规则,即证件类型有个优先级(d、b、e、c、a),取优先级最高的一张,如果证件被改动过(update_date),则从改动的证件里按照上面优先级找到一张。

看起来很懵逼,我们来分析下。

正文

我们先来看下a,b表结构。

对于b表,我略去部分结构,b表主要简化如下,其中type和number是我们需求新增的字段,对于历史数据,这两个字段需要维护。

1
2
3
4
5
6
7
CREATE TABLE `b` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`user_id` varchar(32) DEFAULT NULL COMMENT '用户ID',
`type` varchar(10) DEFAULT '' COMMENT '证件类型',
`number` varchar(50) DEFAULT NULL COMMENT '证件号',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='b表';

A表的简化如下:

1
2
3
4
5
6
7
8
9
CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`user_id` varchar(32) DEFAULT NULL COMMENT '用户ID',
`type` varchar(10) DEFAULT '' COMMENT '证件类型',
`number` varchar(50) DEFAULT NULL COMMENT '证件号',
`create_date` datetime DEFAULT NULL COMMENT '创建时间',
`update_date` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='a表';

我们假设a、b表有如下数据:

b表

1
2
3
4
5
6
7
8
9
10
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('1', '111111', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('2', '222222', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('3', '333333', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('4', '444444', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('5', '555555', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('6', '666666', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('7', '777777', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('8', '888888', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('9', '999999', '', NULL);
INSERT INTO `b` (`id`, `user_id`, `type`, `number`) VALUES ('10', '111111', '', NULL);

a表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('1', '111111', 'a', '123', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('10', '111111', 'c', '1234', '2019-09-10 14:02:46', '2019-09-11 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('2', '111111', 'd', '32122', '2019-09-10 14:02:46', '2019-09-11 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('12', '222222', 'd', '32111', '2019-09-10 14:02:46', '2019-09-12 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('3', '333333', 'b', '12131', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('13', '333333', 'd', '12133', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('4', '444444', 'b', '3434', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('14', '444444', 'e', '34341', '2019-09-10 14:02:46', '2019-09-13 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('5', '555555', 'c', '353452', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('15', '555555', 'a', '3534599', '2019-09-10 14:02:46', '2019-09-11 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('6', '666666', 'c', '3654688', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('16', '666666', 'a', '365461', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('7', '777777', 'd', '4645655', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('17', '777777', 'b', '4645653', '2019-09-10 14:02:46', '2019-09-11 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('8', '888888', 'd', '4444', '2019-09-10 14:02:46', '2019-09-11 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('18', '999999', 'c', '44447', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('9', '999999', 'd', '234243', '2019-09-10 14:02:46', '2019-09-10 14:02:46');
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('19', '999999', 'e', '234248', '2019-09-10 14:02:46', '2019-09-10 14:02:46');

他们的表数据如下:

upload successful
upload successful

我们根据前言里提到的规则,b表只接受该用户一张证件,优先级为(d、b、e、c、a),但是如果证件被改动过的话,就在改动列表里继续按照优先级寻找。

所以对于上面的user_id = 444444的用户,我们应该放入e证件,因为它被改动过;对于 user_id = 111111的用户,可以看到c和d都被改动过,因此从c和d里选,而不考虑a类型,d的优先级高,故最后放入d证件。

虽然程序里很好解决,但是历史数据初始化需要使用SQL处理,我们来看下。

首先要更新b表数据,这些数据应该从a表(或者a表最后经过一系列查询形成的临时表)获取并赋值,它的SQL语句部分应大致如下:

1
2
3
4
5
6
UPDATE b
INNER JOIN (
SELECT XXXXX from a
) AS temp ON b.user_id = temp.user_id
SET b.type = temp.type,
b.number = temp.number;

这个SQL比较好理解,temp临时表部分表示我们在a里查询出一个user_id、type、number一组数据,在通过user_id相等对b表的type和number进行赋值。

接下来就是关于如何获取生成的临时表这部分了。

我们先寻找改动过的,在里面对于每个用户找到他的最高优先级的证件。

可以看到这是在MySQL中使用分组排序获取最高优先级的数据。

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
SELECT
*
FROM
(
SELECT
user_id,
type,
number
FROM
a
WHERE
create_date <> update_date
GROUP BY
user_id,
(
CASE type
WHEN 'd' THEN
1
WHEN 'b' THEN
2
WHEN 'e' THEN
3
WHEN 'c' THEN
4
WHEN 'a' THEN
5
END
)
) AS t
GROUP BY
t.user_id

这儿我们看到,我们这里GROUP BY了user_id 和 type,因此t表返回的每个user_id的第一条即为需要的数据,这时候对t表根据user_id进行GROUP BY,进而获取到了每个的第一条数据。

我们在获取没有改动过的,这儿要注意要除去改动过的用户证件,因为它已经包含在上面的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
SELECT
*
FROM
(
SELECT
user_id,
type,
number
FROM
a
WHERE
create_date = update_date
AND user_id NOT IN (
SELECT
user_id
FROM
a
WHERE
create_date <> update_date
)
GROUP BY
user_id,
(
CASE type
WHEN 'd' THEN
1
WHEN 'b' THEN
2
WHEN 'e' THEN
3
WHEN 'c' THEN
4
WHEN 'a' THEN
5
END
)
) AS t
GROUP BY
t.user_id

然后我们使用UNION ALL 将两部分合并。

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
(SELECT
*
FROM
(
SELECT
user_id,
type,
number
FROM
a
WHERE
create_date <> update_date
GROUP BY
user_id,
(
CASE type
WHEN 'd' THEN
1
WHEN 'b' THEN
2
WHEN 'e' THEN
3
WHEN 'c' THEN
4
WHEN 'a' THEN
5
END
)
) AS t
GROUP BY
t.user_id)
UNION ALL
(SELECT
*
FROM
(
SELECT
user_id,
type,
number
FROM
a
WHERE
create_date = update_date
AND user_id NOT IN (
SELECT
user_id
FROM
a
WHERE
create_date <> update_date
)
GROUP BY
user_id,
(
CASE type
WHEN 'd' THEN
1
WHEN 'b' THEN
2
WHEN 'e' THEN
3
WHEN 'c' THEN
4
WHEN 'a' THEN
5
END
)
) AS t
GROUP BY
t.user_id)

然后我们最后得到的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
UPDATE b
INNER JOIN (
(
SELECT
*
FROM
(
SELECT
user_id,
type,
number
FROM
a
WHERE
create_date <> update_date
GROUP BY
user_id,
(
CASE type
WHEN 'd' THEN
1
WHEN 'b' THEN
2
WHEN 'e' THEN
3
WHEN 'c' THEN
4
WHEN 'a' THEN
5
END
)
) AS t
GROUP BY
t.user_id
)
UNION ALL
(
SELECT
*
FROM
(
SELECT
user_id,
type,
number
FROM
a
WHERE
create_date = update_date
AND user_id NOT IN (
SELECT
user_id
FROM
a
WHERE
create_date <> update_date
)
GROUP BY
user_id,
(
CASE type
WHEN 'd' THEN
1
WHEN 'b' THEN
2
WHEN 'e' THEN
3
WHEN 'c' THEN
4
WHEN 'a' THEN
5
END
)
) AS t
GROUP BY
t.user_id
)
) AS temp ON b.user_id = temp.user_id
SET b.type = temp.type,
b.number = temp.number;

我们也可以将上面数据放入临时表中,再通过临时表插入b表,如下:

创建临时表help_temp:

1
2
3
4
5
CREATE TEMPORARY TABLE help_temp (
user_id VARCHAR (32) NOT NULL,
type VARCHAR (10) NOT NULL,
number VARCHAR (100) NOT NULL
);

插入临时表数据:

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
INSERT INTO help_temp (user_id, type, number) SELECT
*
FROM
(
(
SELECT
*
FROM
(
SELECT
user_id,
type,
number
FROM
a
WHERE
create_date <> update_date
GROUP BY
user_id,
(
CASE type
WHEN 'd' THEN
1
WHEN 'b' THEN
2
WHEN 'e' THEN
3
WHEN 'c' THEN
4
WHEN 'a' THEN
5
END
)
) AS t
GROUP BY
t.user_id
)
UNION ALL
(
SELECT
*
FROM
(
SELECT
user_id,
type,
number
FROM
a
WHERE
create_date = update_date
AND user_id NOT IN (
SELECT
user_id
FROM
a
WHERE
create_date <> update_date
)
GROUP BY
user_id,
(
CASE type
WHEN 'd' THEN
1
WHEN 'b' THEN
2
WHEN 'e' THEN
3
WHEN 'c' THEN
4
WHEN 'a' THEN
5
END
)
) AS t
GROUP BY
t.user_id
)
) AS temp;

将临时表数据放入到b表中:

1
2
3
4
UPDATE b
INNER JOIN help_temp ON b.user_id = help_temp.user_id
SET b.type = help_temp.type,
b.number = help_temp.number;

删除临时表:

1
DROP TEMPORARY TABLE help_temp;

总结

我们上述使用了CASE...WHEN语句,它可以跟在ORDER BY 或者 GROUP BY语句后面,可以让数据按照指定格式排序;

我们通过GROUP BY user_id 和 type得到一次分组结构,这分组里面的第一条即是我们要求的数据,其实这样GROUP BY返回的数据也是通过对user_id分组,对type排序的数据集;我们拿到user_id这一层分组的第一条即可,故得到的数据再对user_id进行GROUP BY即可。

使用UNION ALL合并两部分数据,UNION ALL是合并全部数据,如果只使用UNION,合并时相同的数据只会保留一条。

要将一张表里的数据UPDATE到另一张表里,我们这里使用了INNER JOIN ON语句。

最后提到了临时表,当我们处理过程较复杂时,可以加入临时表进行辅助处理数据,处理完后连接断开后临时表自动删除,当然也可以主动去删除临时表。

引申

我们上面所说的分组排序,相当于GROUP BY了两个字段,对于type来说,只能为不同的值,如果user_id,type相同,则两条数据会被分到同一组。

如下,a表我们新增一条数据。

1
INSERT INTO `a` (`id`, `user_id`, `type`, `number`, `create_date`, `update_date`) VALUES ('20', '111111', 'a', '1234', '2019-09-10 14:02:46', '2019-09-10 14:02:46');

可以看到此时a表数据如下:

upload successful

对于user_id = 111111的用户,他有两个相同的type = a的证件,这时候我们GROUP BY user_id,type,得到的数据这两条肯定会只取一条。

upload successful

我们如果想分组排序这样肯定是不对的,因为缺失了一条数据,怎么处理呢?

一种非常简单的处理方式是再引入一个字段GROUP BY,比如证件号number,如下:

upload successful

可以看到这时候数据就完全展示了。

会不会没有辅助字段可以引入呢?

实际中,只要有主键id存在,我们理论上无论如何也是可以找到辅助GROUP BY的字段,如上面我们使用主键id,也是可以得到user_id和type的分组排序结果的。




-------------文章结束啦 ~\(≧▽≦)/~ 感谢您的阅读-------------

您的支持就是我创作的动力!

欢迎关注我的其它发布渠道