乐趣区

关于mysql:我操作MySQL的惊险一幕

背景

前几天因工作须要,组长给我安顿了一个数据荡涤的工作。

工作:把 A 表的数据洗到 B 表。

我的第一反馈,什么是「洗」?洗数据是什么?洗钱我倒是晓得。

不过我不能慌啊,于是问了问组长。

我:组长,把 A 表的数据洗到 B 表是什么意思?

组长一脸无奈,手捂住脸,恨铁不成钢,而后调整过去,还是很急躁地跟我讲的,大略意思就是咱们当初 B 表须要 A 表的数据,A 表中和 B 表中字段含意一样,然而值可能不一样,这就须要咱们进行解决,在将 A 表数据搞到 B 表的过程中,把数据搞正确。

基于我理解能力无限,过后并不是很懂所谓的「洗数据」,而且这个 A 表的字段也和 B 表的字段没怎么对上,A 的字段显著多于 B 的字段,某些字段命名也和 B 不一样,然而表白的意思是一样的,该如何洗?

于是疯狂搜寻如何洗数据!

我这里就举个例子来阐明,别离给出 A 表和 B 表,当然我只列出了一部分字段,当初假如就这么多字段。

A 表

A 表字段:name, province_id, city_id, area_id, tech_id, crop_id, field_id, create_time, update_time, xxx, yyy, zzz, ...

A 表的字段是多于 B 表的 ,我须要将 A 表的数据洗到 B 表, 只解决我须要的字段,不须要的就不必理。

A 表中有 2 万多条记录,B 表我本人插入的有 200 多条记录。

当然,给出 A 表后,还给了个实体模型(JavaBean,Entity,超多种叫法,花里胡哨的)

public class A {
    private String name;
    private Long provinceId;
    private Long cityId;
    private Long areaId;
    private Long techId;
    private Long cropId;
    private Date createTime;
    private Date updateTime;
    ...
}

B 表

B 表字段:name, province_id, city_id, area_id, mature_id, crop_id, create_time, update_time

还原现场

上面我模仿测试环境中数据库的那两张表,下面是 A 表(2 万多条记录,这里我只模仿了 7 条),上面的 B 表(200 多条记录)。

洗数据思路

首先,我是先找出 A 表中能和 B 表对上意思的字段,而后将 A 表数据全副插入到 B 表中。

于是,我便找出了如下这些字段:

name, province_id, city_id, area_id, tech_id, crop_id, update_time

之后在 B 表中对新插入的数据进行解决,即 洗数据

写 SQL 操作

次要的 SQL 语句是:

INSERT INTO 指标表(字段 1, 字段 2, ...) SELECT 字段 1, 字段 2, ... FROM 起源表 WHERE 条件;

于是,便这样操作:

INSERT INTO b(name, province_id, city_id, area_id, mature_id, crop_id, update_time) 
SELECT name, province_id, city_id, area_id, tech_id, crop_id, update_time FROM a;

操作是失常的,胜利将 A 中 2 万多条记录全副插入到了 B 中。

然而!我漏了一个字段,就是 create_time

于是,想着对这个字段进行更新,将 A 中这个字段更新到 B 中。

于是写了一条 SQL 语句。

UPDATE b(create_time) SET create_time = (SELECT create_time FROM a);

> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(create_time) SET create_time = (SELECT create_time FROM a)' at line 1

噢,看来不能这样更新啊!(一个大嘴巴子过来,写错了还没发现)

换种写法:

UPDATE b AS tb, (SELECT create_time FROM a) AS ta SET tb.create_time = ta.create_time;

能够,噩梦开始了!

更新了十分久,看着十几秒的 SQL 执行到六七百秒还没执行完,心里着实很慌!眼看着数据库可能会崩,我不得不向我的组长求救了!

这时问题呈现了,有人数据库连贯不上了,可见这严重性,曾经影响到其他人的应用了!

于是我组长来帮我解决了,想着 kill 掉我的 navicat,然而 kill 掉还是没成果,毕竟这个 SQL 曾经在执行了。

我:能不能重启这个 MySQL 服务?

组长一波连环炮过去了

组长:重启?你晓得这个 MySQL 有多少人在用吗?又不只是咱们在用,你重启其他人怎么搞?

我理屈词穷,心里十分忐忑,想着闯祸了,GG,就看着他操作。没过多久,通过他的一顿操作,终于解决了这个问题。我的心里的一块悬着的大石终于放下了,还好解决了。组长牛逼,救世主!

我:如何解决的?

组长:将这个事务回滚解决的,你更新的 SQL 怎么写的?(致力回忆)

于是写出了下面写的 SQL:

UPDATE b AS tb, (SELECT create_time FROM a) AS ta SET tb.create_time = ta.create_time;

组长:你为什么这样写?不应该把子查问写在 SET tb.create_time 前面吗?

我:对啊,我一开始就是把这个子查问写在它前面的,然而提醒我语法错误,我就换了一种写法。

组长:那你写写你说提醒谬误的 SQL。

于是我又丢进去一个 SQL:

UPDATE b SET create_time = (SELECT create_time FROM a);

实际上,这条 SQL 也是不行的,子查问返回的后果不止一行,而以后 SET 是更新某一行的。

正确的写法是:

UPDATE b AS tb 
SET create_time = (SELECT create_time FROM a AS ta WHERE tb.id = ta.id AND tb.name = ta.name)
WHERE tb.id = (SELECT tb.id FROM a AS ta WHERE tb.id = ta.id AND tb.name = ta.name)

博客园 -SQL 把一个表中数据更新到另一个表的多种办法

最初组长沉思,你 B 表曾经有 2 万多条记录了,A 表也有 2 万多条记录,你这样更新,每一次都须要子查问查出 A 表的 2 万多条记录,B 也有 2 万多条记录,这样成笛卡尔积了,你晓得什么是笛卡尔积吧?2 万 × 2 万 = 4 亿的记录行了,难怪这么久。

总结

状况:漏了某一个字段 X,须要将 A 表的这个字段列值更新到 B 表

条件:A 中的 id 字段的值等于 B 表中的 id 字段的值 且 A 中的 name 字段的值等于 B 中 name 字段的值(条件为什么这样写?)。

条件这样写次要是因为 表和表之间的关联关系 可能有多个字段,此处只选二个字段,多个依此类推。

操作:

  • 一张表的数据插入到另一张表,能够这样写:
INSERT INTO 指标表(字段 1, 字段 2, ...) SELECT 字段 1, 字段 2, ... FROM 起源表 WHERE 条件;
  • 批量更新一张表的某个字段到另一张表,那么 SQL 能够相似这样写:
UPDATE b AS tb 
SET create_time = (SELECT create_time FROM a AS ta WHERE tb.id = ta.id AND tb.name = ta.name)
WHERE tb.id = (SELECT tb.id FROM a AS ta WHERE tb.id = ta.id AND tb.name = ta.name)

所谓 洗数据:在我的了解中,就是把旧数据,依照新数据的规定把旧数据不正确的值批改正确,同时把这些旧数据插入到新数据中,成为新数据。举个例子,A 表中的 province_id,值为 10 代表 广东,而 B 表中的 province_id,值为 19 代表 广东,把 A 表中的数据插入到 B 表的过程中,把值为 10 批改为 19,这样插入的数据能力在 B 表中正确示意 广东,这个过程就是「洗数据」,当然,也能够在插入后再批改,不论过程是怎么,最终能把数据的值批改正确,就是洗数据!

教训:

  1. 数据量大的表,少在测试环境操作,要操作尽量保障写的 SQL 是正确的,能在本地环境操作就当初本地环境操作!
  2. 能用 Java 代码进行操作,优先写 Java 代码操作!

最初的最初

由自己程度所限,不免有谬误以及不足之处,屏幕前的靓仔靓女们 如有发现,恳请指出!

最初,谢谢你看到这里,谢谢你认真对待我的致力,心愿这篇博客对你有所帮忙!

你轻轻地点了个赞,那将在我的心里世界削减一颗亮堂而夺目的星!

退出移动版