SakuraTears的博客

越努力越幸运

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

发表于 更新于 分类于 阅读次数： Valine：
本文字数： 11k 阅读时长 ≈ 10 分钟

前言

先说下背景吧，在某项目迭代过程中，由于系统设计问题，需要对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的分组排序结果的。




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

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

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