INSERT INTO `db`.`table`
VALUES (
39741633
49302045
0
44
'{"CustomerName":"S","CustomerMobile":"8","CustomerEmail":"","VersionId":"5","CityId":"2","CampaignId":"1","InquirySourceId":"3","Eagerness":"-1","ApplicationId":"2","BranchId":"3","AssignedDealerId":"2","DMSInqNo":"45"}'
NULL
0
'2021-11-09 19:11:50'
NULL
1
29
NULL
);
INSERT INTO `db`.`table`
VALUES (
39741635
49970365
0
30
'{"CustomerName":"A","CustomerEmail":"[email protected]","CustomerMobile":"9","VersionId":"6","InquirySourceId":"1","Eagerness":"-1","IsCorporate":"z","CampaignId":"8","BranchId":"3","ApplicationId":"1","Location":{"City":{"CityId":"1"},"Area":{"AreaId":"4"}},"CouponCode":null,"CwOfferId":"0","AssignedDealerId":"0","PinCode":""}'
NULL
0
'2021-11-09 19:11:51'
NULL
1
29
NULL
);
我从 binlog 中提取了这些插入语句。它们并不完全像这样,我做了一些改动,但我还是卡住了。我需要将它们转换为实际的插入语句。如果您看到我需要在第二行(INSERT INTO db
. table
VALUES ())之后的每一行末尾添加逗号,对于每个插入语句。这意味着在两行之后添加逗号直到 11 行,然后在 INSERT INTO 之后再次开始并重复。
我尝试谷歌搜索并找到了这个
sed '/INSERT/{n;n;n;n;n;s/$/,/}' teststring.txt
--> 这将在插入后将逗号放在第五行。问题是如果我再次运行第六行
sed '/INSERT/{n;n;n;n;n;n;s/$/,/}' teststring.txt
,然后它替换第一个 SED 命令放置的先前逗号
这就是文件的外观。
INSERT INTO `db`.`table`
VALUES (
39741633,
49302045,
0,
44,
'{"CustomerName":"S","CustomerMobile":"8","CustomerEmail":"","VersionId":"5","CityId":"2","CampaignId":"1","InquirySourceId":"3","Eagerness":"-1","ApplicationId":"2","BranchId":"3","AssignedDealerId":"2","DMSInqNo":"45"}',
NULL,
0,
'2021-11-09 19:11:50',
NULL,
1,
29,
NULL
);
INSERT INTO `db`.`table`
VALUES (
39741635,
49970365,
0,
30,
'{"CustomerName":"A","CustomerEmail":"[email protected]","CustomerMobile":"9","VersionId":"6","InquirySourceId":"1","Eagerness":"-1","IsCorporate":"z","CampaignId":"8","BranchId":"3","ApplicationId":"1","Location":{"City":{"CityId":"1"},"Area":{"AreaId":"4"}},"CouponCode":null,"CwOfferId":"0","AssignedDealerId":"0","PinCode":""}',
NULL,
0,
'2021-11-09 19:11:51',
NULL,
1,
29,
NULL
);
我怎样才能实现这个目标?
答案1
您可以匹配不包含某些字符串的所有行,并附加逗号:
sed '/.*VALUES.*\|.*INSERT.*\|);$/! s/$/,/' your_file
它的工作方式如下:
- 我匹配任何包含
VALUES
或INSERT
结尾的行);
。 - 这是通过将不同的字符串用 链接在一起来完成的
\|
。 - 然后用
!
(因此只有不包含这些字符串的行才真正匹配)。 - 然后我在这些行后面添加一个逗号。
一旦您确认它执行了您想要的操作,只需添加即可-i
就地进行替换:
sed -i '/.*VALUES.*\|.*INSERT.*\|);$/! s/$/,/' your_file
编辑
正如 @they 在下面的评论中指出的,此命令还会在每个 INSERT 语句的最后一行放置一个逗号(我不确定这对您来说是否有问题):
INSERT INTO `db`.`table`
VALUES (
39741633,
.
.
.
29,
NULL, <--- unecessary comma here
);
答案2
sed -i '/INSERT INTO/{n;n;s/$/,/}' teststring.txt
我错过了这个-i
选项,这个 SED 命令在 INSERT INTO 之后的第二行末尾添加了一个逗号。我可以做我想做的事情,但我必须通过增加n;
每个命令来重复运行该命令 10 行,因此第三行的下一个命令将是
`sed -i '/INSERT INTO/{n;n;n;s/$/,/}' teststring.txt`.
这里有三个n;
,第一个命令中有两个n;
。
如果有人有更好的方法来做到这一点,我将不胜感激:-)。
答案3
每当我们找到以字符串 开头的行时VALUES
,请将下一行追加到 中的编辑缓冲区中sed
。重复此操作,直到缓冲区以 结尾);
。然后用逗号替换两侧没有括号的所有换行符。
/^VALUES/ {
# Loop until the buffer ends with ");".
# The N command reads the next line and appends
# it to the buffer, with a newline character as delimiter.
:again
N
/);$/ !b again
# Replace all newlines with commas,
# but only if the newline is not immediately
# next to a parenthesis.
s/\([^(]\)\n\([^)]\)/\1,\2/g
}
这可以用作单独的sed
脚本:
sed -f thescript file
...或直接在命令行上:
sed -e '/^VALUES/ {' \
-e ':again' \
-e 'N; /);$/ !b again' \
-e 's/\([^(]\)\n\([^)]\)/\1,\2/g; }' file
鉴于问题中的文档,这将生成语法正确的 SQL,如下所示:
INSERT INTO `db`.`table`
VALUES (
39741633, 49302045, 0, 44, '{"CustomerName":"S","CustomerMobile":"8","CustomerEmail":"","VersionId":"5","CityId":"2","CampaignId":"1","InquirySourceId":"3","Eagerness":"-1","ApplicationId":"2","BranchId":"3","AssignedDealerId":"2","DMSInqNo":"45"}', NULL, 0, '2021-11-09 19:11:50', NULL, 1, 29, NULL
);
INSERT INTO `db`.`table`
VALUES (
39741635, 49970365, 0, 30, '{"CustomerName":"A","CustomerEmail":"[email protected]","CustomerMobile":"9","VersionId":"6","InquirySourceId":"1","Eagerness":"-1","IsCorporate":"z","CampaignId":"8","BranchId":"3","ApplicationId":"1","Location":{"City":{"CityId":"1"},"Area":{"AreaId":"4"}},"CouponCode":null,"CwOfferId":"0","AssignedDealerId":"0","PinCode":""}', NULL, 0, '2021-11-09 19:11:51', NULL, 1, 29, NULL
);
如果您想要问题中的确切输出,请将最后一个替换替换为
s/\([^(]\)\(\n\)\([^)]\)/\1,\2\3/g
这不是用逗号替换换行符,而是在每个换行符之前插入一个逗号。
答案4
您可以使用两行模式空间,并将逗号附加到所有没有 INSERT 或 ) 的行;并且由于模式空间中有 2 行,即 INSERT 和 ) 周围的行;也就不附加了。
sed -e '
$!N;/INSERT\|);$/!{s/\n/,&/;P;D;}
' file