Process an SQL Dump one Statement at a Time ( Grep for Text Over Multiple Lines)

Recently I was asked how to loop through a MySQL dump file one statement at a time, given that the statements in a dump file are broken up over multiple lines. There are any number of ways of doing this, but since I love Perl, that's usually my first stop.

The answer in Perl is simple. Change the end of line ( $/ ) and slurp the file into an array. So for a MySQL dump, the actual record separator is a semi-colon and a \n.


open( my $fh, "<", "Dump.sql") or die;

my @file;

{

local $/ = ";\n";

my @file = <$fh>;

}


Now unless ';\n' somehow ended up in the middle of an SQL statement, you have an array of SQL statements, one statement per element.

There is one problem with this method. It uses a lot of memory. As least as much as the size of your file. If you're dealing with large files, you'll want to process the file a line at a time instead. Tie::File comes in handy here.

use Tie::File;

my @file;

{

local $/ = ";\n";

tie @file, 'Tie::File', "Dump.sql" or die;

}


Now you can use this @file array to loop through the file, one SQL statement at a time, without having more than one SQL statement in memory at a time. Now say you wanted to find a pattern and evaluate one statement at a time.

my (@matches ) = grep /($pattern)/, @file;

print "Found $#matches matches\n";