在不匹配模式的行上附加文本

在不匹配模式的行上附加文本
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. tableVALUES ())之后的每一行末尾添加逗号,对于每个插入语句。这意味着在两行之后添加逗号直到 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

它的工作方式如下:

  • 我匹配任何包含VALUESINSERT结尾的行);
  • 这是通过将不同的字符串用 链接在一起来完成的\|
  • 然后用!(因此只有不包含这些字符串的行才真正匹配)。
  • 然后我在这些行后面添加一个逗号。

一旦您确认它执行了您想要的操作,只需添加即可-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

相关内容