高效办公小技巧——如何批量处理 SQL 文件?

背景

我们在日常工作中,经常会遇到一些「洗数据」的场景。

比如,我们有如下格式的源文件:

origin.csv

ID,积分,更新时间
1,1000,2023-07-12 12:00:00
2,2000,2023-07-12 12:00:00
3,3000,2023-07-12 12:00:00
4,4000,2023-07-12 12:00:00
...

文件格式为 csv 格式的文件,文件行数在数万行左右。共有三列内容,分别为:ID、积分和更新时间。

现在我们需要根据文件的内容,生成以下格式的 SQL 文件:

target.sql

UPDATE `users` SET `score` = {积分}, `updated_at` = {更新时间} WHERE `id` = {ID} LIMIT 1;
UPDATE `users` SET `score` = {积分}, `updated_at` = {更新时间} WHERE `id` = {ID} LIMIT 1;
UPDATE `users` SET `score` = {积分}, `updated_at` = {更新时间} WHERE `id` = {ID} LIMIT 1;
UPDATE `users` SET `score` = {积分}, `updated_at` = {更新时间} WHERE `id` = {ID} LIMIT 1;
...

当你遇到这种场景时,你一般是怎么处理的呢?

接下来,就让我们来看看有哪些常见的处理方法。

以下方法均为笔者用过的一些方法,如果大家有其他更好的方法,欢迎大家在评论区留言讨论~

处理方案

方案一:代码搞定一切

作为天天撸脚本的程序员来说,没有什么问题是几行代码不能搞定的。这里我们先以 Laravel 为例,看看如何实现这个功能。

由于功能比较简单,我们直接上代码:

...
//========== 获取文件路径 ==========
$originFile = storage_path('tmp/origin.csv');
$targetFile = storage_path('tmp/target.sql');

//========== 使用SplFileObject类处理文件 ==========
$splFileObj = new \SplFileObject($originFile, 'r+');

//========== 跳过行首 ==========
$splFileObj->current();
$splFileObj->next();

while (!$splFileObj->eof()) {
    //========== 读取行并移动文件指针 ==========
    $line = trim($splFileObj->current());
    $splFileObj->next();

    $lineInfo = explode(',', $line);

    //========== 文件行格式判断 ==========
    if(!$lineInfo || count($lineInfo) != 3){
        Log::error('Invalid line format for origin csv file.', ['line' => $line]);
        continue;
    }

    list($id, $score, $updatedAt) = $lineInfo;

    //========== 生成SQL文件 ==========
    $sql = "UPDATE `users` SET `score` = {$score}, `updated_at` = '{$updatedAt}' WHERE `id` = {$id} LIMIT 1;" . PHP_EOL;
    File::append($targetFile, $sql);
}
...

通过运行上述代码,我们就可以将 SQL 生成到 target.sql 文件中,是不是很 Easy 。

方案二:工具 + vim

但是,有时候我们不想这么麻烦地去写脚本,能不能简单一些呢?

我们发现源文件是一个 csv 文件,通过观察目标文件,我们可以发现以下特点:

由于上图中圈出来的部分每一列的内容都是一致的,我们可以把圈出来的部分放到 csv 的新增列中,而未圈出来的部分则是我们原始文件中的列,这样,我们直接在一个新的 csv 文件中生成我们的目标 SQL 。

由于涉及到一些具体的操作,这里我们拆分成几步来演示。

step1:修改日期列格式

这里我们以 WPS 软件为例。当我们用软件打开 origin.csv 文件后,会发现 更新时间 一列的格式被自动转换了,如下:

这里我们需要先修改下这一列的单元格格式,选中整列->右键->设置单元格格式,操作如下:

ahYvJtutVf.png

这里,我们把单元格格式设置为 yyyy-mm-dd hh: mm:ss ,确认后就会发现格式显示正常了:

step2:调整列的顺序

接下来,我们需要调整一下原始文件列的顺序,调整后效果如下:

step3:插入新列

根据方案开头的效果图,我们新增数据列,并填充内容,最后再把表头删除。操作以后的效果图如下:

因为 更新时间 一列是字符串,所以我们在外层包裹了双引号。

到这里,看上去已经处理完了。我们将处理完的文件保存为 target.sql 文件,然后打开看看效果:

UPDATE `users` SET `score` =     1000    , `updated_at` = "    2023-07-12 12:00:00    " WHERE `id` =     1     LIMIT 1;
UPDATE `users` SET `score` =     2000    , `updated_at` = "    2023-07-12 12:00:00    " WHERE `id` =     2     LIMIT 1;
UPDATE `users` SET `score` =     3000    , `updated_at` = "    2023-07-12 12:00:00    " WHERE `id` =     3     LIMIT 1;
UPDATE `users` SET `score` =     4000    , `updated_at` = "    2023-07-12 12:00:00    " WHERE `id` =     4     LIMIT 1;

这里我们发现,文件中多了一些空白字符,这样的数据格式肯定是不行的,特别是更新时间中的空白字符,可能会产生一些不可预料的后果。

那么应该怎么去掉这些「多余」的空白字符呢?

step4:删除多余空白字符

首先我们需要清楚「空白字符」具体是什么。

我们使用 vim 打开 target.sql 文件,然后命令模式下运行 : set list ,查看空白字符的格式,如下图所示:

通过观察我们发现,空白字符都是 ^I 的格式,这时我们只需要在 vim 的命令模式下执行以下命令将其替换掉就可以了:

:%s/^I//g

注意:^I 是通过 ctrl + I 打出来的,而不是直接输入的,这里需要注意。

操作以后,发现「多余」的空白字符都被删除了,完美~

大功告成。

方案三:awk showtime

可能有些大神「更懒」,连工具都懒得用,那还有没有更简单的方法呢?

既然这样,只能有请上古神器「awk」登场了:

cat origin.csv | awk -F ',' 'NR>1{print "UPDATE `users` SET `score` = " $2  ", `updated_at` = \"" $3 "\" WHERE `id` = " $1  " LIMIT 1;"}'

一步到位,就是这么简单粗暴~

总结

以上就是通过三种不同的方式来处批量处理 SQL 文件的内容。

大家在日常工作中是怎么处理类似问题的呢,欢迎在评论区留言讨论。

感谢大家的持续关注~

本作品采用《CC 协议》,转载必须注明作者和本文链接
你应该了解真相,真相会让你自由。
本帖由系统于 7个月前 自动加精
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
讨论数量: 9

awk 效率杠杠的

7个月前 评论
快乐的皮拉夫 (楼主) 7个月前

碰到过类似需求 让gpt帮我写了

file

7个月前 评论
快乐的皮拉夫 (楼主) 7个月前
sanders

批量拼SQL我一般都用 excel 公式搞定。大多数情况只需要记住 & 字符串链接 双引号用双引号转义即可。

举个例子:

商品编号 条码 商品名称 改后折扣价 商品分级 分级转换 SQL
69719 69719 诗裴丝 干发喷雾 17.9 S =IF(E2=”S”,1,IF(E2=”A”,2,IF(E2=”B”,3,IF(E2=”C”,4,””)))) = “UPDATE akitado.skus SET sku_custom_attribute=’” & “[{“”id””: 1, “”value””: “””&F2&”””}]” & “‘ WHERE sn=’”&A2&”‘;”

最终的结果:

商品编号 条码 商品名称 改后折扣价 商品分级 分级转换 SQL
69719 69719 诗裴丝 干发喷雾 17.9 S 1 UPDATE akitado.skus SET sku_custom_attribute=’[{“id”: 1, “value”: “1”}]’ WHERE sn=’69719’;
7个月前 评论
快乐的皮拉夫 (楼主) 7个月前
高延迟战神 7个月前

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!