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.


3 Answers 3


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
    Commented Oct 19, 2017 at 18:06
  • @Swechsler I added a variation that takes file through stdin.
    – B Layer
    Commented Oct 19, 2017 at 20:23
  • @Swechsler Any luck?
    – B Layer
    Commented Oct 29, 2017 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
    Commented Oct 30, 2017 at 18:09

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
    Commented Oct 19, 2017 at 18:07

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
    Commented Oct 20, 2017 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
    Commented Oct 20, 2017 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
    Commented Oct 20, 2017 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
    Commented Oct 23, 2017 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
    Commented Oct 23, 2017 at 7:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .