7

Consider the following text (incidentally, part of a MySQL dump):

CREATE TABLE `table` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `full_index` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

I would like to remove the FULLTEXT key, and I also want to remove the trailing comma on the line above so that the SQL remains valid.

Can anyone come up with (and explain) a sed recipe to do this?

11

AWK answer

With your sample text in a file named sql, the following pattern (with line breaks and indentation for clarity):

awk -v skip=1 '{
    if (skip) { skip=0 }
    else {
        if (/FULLTEXT KEY/) { skip=1; sub(/,$/, "", prevline) }
        print prevline
    }
    prevline=$0
}
END { print prevline }' sql

produces:

CREATE TABLE `table` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Explanation:

  • We implement "lookahead" by only printing the previously encountered line at every iteration, after inspecting the current line.
  • If the current line contains the FULLTEXT KEY marker, we set a flag to skip printing this line during the next iteration. We also remove the trailing comma on the previous line that is about to be printed.
  • We skip printing an empty initial line (before prevline has been set) by initially setting skip to 1 ("true").
  • We make sure to print the last line by ending the script with an extra prevline print. Note that the current implementation assumes that this last line is not a line at risk of being skipped, i.e. that it does not contain the FULLTEXT KEY marker.

Original (incomplete) sed answer

This answer is incomplete and certainly in most cases incorrect, since sed will consume the input stream too quickly for the intended result when doing multiline matching -- as pointed out in the comments, it will only work for matches on even numbered rows! sed does not have "true" lookahead functionality, so we would be better off using Python/Perl/etc., or indeed AWK as above.

With your sample text in a file named sql, the following pattern:

$ sed 'N; s/,\n  FULLTEXT.*//' sql

produces:

CREATE TABLE `table` (
  `id` int(10) NOT NULL auto_increment,
  `name` varchar(100) NOT NULL default '',
  `description` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Explanation:

  • N enables multiline matching.
  • \n represents a line break.
  • s/pattern/replacement/ is the standard replacement syntax.
  • .* will match anything to the end of the current line.
  • Only works if your match is on even line. This wont work if "FULLTEXT" is on line 7 instead of line 6! – nexayq Jul 8 '16 at 13:46
  • @nexayq: Good catch! I cannot use sed in the fashion I attempted above. Since the "test case" worked by pure chance and no further feedback was given at the time of the answer some three years ago, I assumed sed had some intelligent stream backtracking ability in its multiline mode, and thus never reviewed my answer further. I add a more verbose but probably also more straightforward AWK solution as a substitute. It is most likely still possible to solve with sed, but then probably with more work than with AWK (or even Perl, Python, shell script, etc.). – Daniel Andersson Jul 28 '16 at 17:19
0

Managing two lines with sed is not that difficult.
Just keep two lines in pattern space.

  • $!N : this command append a line in pattern space.
  • P : print the first line in pattern space
  • D : delete the first line in pattern space and start new cycle (without read a line)
    if remain only one line then it behave like "d" command ( that is read a line and start new cycle )

sed -n '$!N; s/,[[:space:]]*FULLTEXT KEY.*// ;P;D' 

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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