There have been several questions here regarding replacing multi-line strings using the Unix shell, but I haven't found one that will work in this situation.

I am trying to remove keys and constraints from some MySQL DDL, which looks like this (one example):

CREATE TABLE `access_group` (
  `GROUP_NAME` varchar(45) NOT NULL,
  `GROUP_DESC` varchar(45) NOT NULL DEFAULT '',
  KEY `testkey` (`PARENT_GROUP_ID`)

I want to remove everything from the comma ending the line before 'PRIMARY KEY' up to, but not including ') ENGINE=' (there can be zero or multiple lines between these, and they won't always start with KEY or have the parenthesis, but the ') ENGINE=' is consistent). The result should look like this:

CREATE TABLE `access_group` (
  `GROUP_NAME` varchar(45) NOT NULL,
  `GROUP_DESC` varchar(45) NOT NULL DEFAULT ''

I'm open to using any standard command-line utility (e.g. sed, perl, awk), but since these files can be fairly large (some are on the order of tens or hundreds of GB) they need to be efficient. Since the files are usually stored gzipped (or sometimes I process the output of the mysql dump utility directly rather than first writing to disk) I need something that can be piped into and out of.


Keep state on whether to print the previous line, edit said to remove the comma when necessary. This method only keeps one or two lines of the file in memory.

#!/usr/bin/env perl
use strict;
use warnings;

my $printing = 1;
my $previous;

# reads from standard input (optionally with the conventional -) or from
# the named files
shift @ARGV if @ARGV == 1 and $ARGV[0] eq '-';
while ( my $line = readline ) {
    if ( $line =~ m/^\s+PRIMARY KEY/ ) {
        $previous =~ s/,[ \t]*$//;
        $printing = 0;
    } elsif ( $line =~ m/^\) ENGINE/ ) {
        $printing = 1;
    } elsif ( !$printing ) {
        undef $previous;
    print $previous if defined $previous;
    $previous = $line if $printing;
# don't forget last line after fall off the end of input (eof)
print $previous if defined $previous;
  • 1
    Yeah, it occurred to me to possibly write a Perl script, but IME it's much slower than sed or a Perl one-liner – Swechsler Oct 19 '17 at 18:07

Using ex (aka vim in Ex mode):

ex +'%s/,\n *PRIM\_.*\ze\n) ENGINE//' +wq file

Just a "batch" version of the Vim substitute-delete (empty substitution //) which does multi-line match with \_.* and excludes the last part of the pattern with \ze.

This modifies the file in-place. If you don't want that do this to save to a new file file2:

ex +'%s/,\n *PRIM\_.*\ze\n) ENGINE//' +'w file2' +q! file

Update: To pipe in a file...this is a little unusual with the addition of /dev/stdin but does the trick:

cat file | ex +'%s/,\n *PRIM\_.*\ze\n) ENGINE//' +'%p|q!' /dev/stdin
  • I guess I should have stated that the files in questions are stored gzipped, so I need something that can behave as a pipe for both STDIN and STDOUT – Swechsler Oct 19 '17 at 18:06
  • @Swechsler I added a variation that takes file through stdin. – B Layer Oct 19 '17 at 20:23
  • @Swechsler Any luck? – B Layer Oct 29 '17 at 17:59
  • I ended up using a Perl solution, since it's what I code in most of the time anyway (I was hoping for a one-liner, though). Even though it's probably slower, since loading the data is significantly slower than any of these solutions, and it's always going to be used as part of a pipe, it didn't really matter. – Swechsler Oct 30 '17 at 18:09

Stream-based GNU sed solution:

#Unless on the last line, read the next line and append it to the pattern space

#If the current pair of lines in buffer, matches the "/,\nPRIMARY KEY/" pattern
/,\n\?\s*PRIMARY KEY/ { 
   #Read the following lines, until "/) ENGINE/" pattern is encountered
   /) ENGINE/ b exit 
   b loop 

#Strip away everything between ", PRIMARY KEY" and ") ENGINE"
s/,\n\?\s*PRIMARY KEY.*\() ENGINE\)/\n\1/

#Print the content of the pattern space up to the first newline (i.e. the first line out of two)

#Delete everything up to the first newline (leaving the second line in pattern space buffer)
#and restart the cycle

Run as follows:

cat data.txt|sed -nf script.sed

(you can compress this to one-liner, by removing comments and replacing newlines with ";").

Version by @Philippos:

With some simplification and more portable:

sed -e '$!N;/,\n *PRIMARY KEY/!{P;D;};s/,//;:loop' -e 'N;s/ *PRIMARY KEY.*\() ENGINE\)/\1/;T loop'
  • @BLayer w/o P;D the input lines would be read pairwise: '12', '34', '56' ..., so if the pattern starts at the wrong line it would not be detected. With 'P;D', it is processing the pairs of adjacent lines: '12', '23', '34', '45', '56' ..., eliminating the issue. – zeppelin Oct 20 '17 at 6:12
  • Yeah, I tested that scenario and it worked but I just rechecked and turns out I was mixing up my input files so I didn't actually test it. :P My bad. – B Layer Oct 20 '17 at 6:25
  • 1
    @Philippos I believe your version won't handle the "no newlines" case properly (e.g. when comma and "PRIMARY KEY" are on the same line). – zeppelin Oct 20 '17 at 13:16
  • Sure, because this case is not required. The question says "everything from the comma ending the line before 'PRIMARY KEY'", implying that there is always a newline after the comma. Maybe you got confused by "there can be zero or multiple lines between these", but even with zero lines in between, there is still one newline between them and the script works. – Philippos Oct 23 '17 at 7:06
  • @Philippos Hmm, I see your point, but I still think it is more flexible (and correct) to allow for no newline between comma and the "PRIMARY KEY". Would you mind to post your version as a separate answer, so that we do not have two approaches mixed in one post ? – zeppelin Oct 23 '17 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.