++ed by:
Author image Nic Sandfield


Mysql::PrettyPrinter - A lean, efficient SQL pretty-printer for MySQL


Version 0.10


The pretty-printer uses capitalisation, line-breaks, and indentation to reformat the given chunk of SQL into a standardised form.

    $formatted = Mysql::PrettyPrinter->format(sql => $sql);


    my $pp = Mysql::PrettyPrinter->new;
    $pp->add_tokens($tok0, $tok1, $tok2);
    # ...do some processing on @{$pp->{tokens}}
    my $output = $pp->format(indent => '  ');


    mysqlbinlog /data/log/binlog.001 \
        | head -n 40 \
        | perl -MMysql::PrettyPrinter \
            -e'Mysql::PrettyPrinter->passthrough' \
        | a2ps


Very simple-minded pretty-printer for MySQL SQL. It gets 'almost there' results with terse efficient code. It sets line-breaks and probably-appropriate indentation, and optionally wraps token classes in markup.

If your requirements are more sophisticated, this class will never do the whole job for you, however you might get a lot of mileage from using this class as a pre-processor, extracting the tokens from $pp->tokens (and never calling $pp->format).


new(wordsep => ' ', linesep => "\n", indent => ' ', wrap => undef, sql => $sql)

Constructor. If you're only invoking format once then you can probably skip the constructor. On the other hand, if you want to construct the object once and use it many times, you can set your default options here.


Character or string to use as a word separator, default: ' '.


Character or string to use as a line separator, default: "\n". You may want to specify "\r\n" for Windows, or "\n\r" for old Mac.


String equating to one level of indentation, default: wordsep x 4. You may want to specify "\t" if that suits your coding standard.


Markup for wrapping token classes, default: undef. The example from SQL::Beautify is

    wrap => { keyword => [ "\x1B[0;31m", "\x1B[0m" ] }

which will make keywords red on some terminals.

Another example is

    wrap => { keyword => [ '<div class="keyword">', '</div>' ] }

which will wrap keywords in HTML+CSS markup.

If you specify a hash for wrap, you must specify a pair of strings for one or more of the token classes: keyword, function, literal. (If you want to omit one half of the wrapping pair, use an empty string in its place.)


The SQL string to be formatted. Useful if you want to manipulate the SQL or token list before calling format, or you are going to invoke format several times on the same SQL string. Otherwise just specify it when calling format.


Another place for specifying the string to be formatted. Useful if you want to re-use the same object with different SQL.

Returns the current SQL string if invoked without argument.


Lets you append a string before lexical analysis. Useful if you want to build up SQL in more than one pass.

make_tokens(sql => $sql)

Invokes lexical analysis. An additional place where you can specify the SQL. Useful if you want to examine or manipulate tokens before formatting.


Lets you specify your own list of tokens, perhaps a result of your own lexical analysis or a result of manipulation you performed on the previous list.

Returns the current token list if invoked without argument. Relevance depends on you invoking make_tokens or tokens beforehand.


Lets you append a list of tokens to the current token list. Usefulness depends on you invoking make_tokens or tokens beforehand.

format(wordsep => ' ', linesep => "\n", indent => ' ', wrap => undef, sql => $sql)

Returns the formatted output string. Takes same arguments as new. Can be invoked as a class method, which is very convenient in the simple (most common) use case.

    my $pp = Mysql::PrettyPrinter->new;
    print $pp->format(sql => 'SELECT 1 + 1');


    print Mysql::PrettyPrinter->format(sql => 'SELECT 1 + 1');
lexicals($sql, $omit_whitespace_tokens)

Utility method for lexical analysis, returning the list of lexical tokens from a given string of SQL. This is not specific to MySQL, and is only a minor performance enhancement over SQL::Tokenizer. A true value for $omit_whitespace_tokens causes it to strip out tokens of pure whitespace such as blank lines.


A convenience method for invoking format from the commandline. It's arguable whether the example at the top of these notes is any more convenient than using

    | perl -MMysql::PrettyPrinter -e'BEGIN{$/=q{}}
        print Mysql::PrettyPrinter->format(%options, sql => <>)'

One advantage is it avoids issues when using double quotes.


Most methods support method chaining. So you can invoke

    print Mysql::PrettyPrinter->new
    ->sql('SELECT')->add_sql('1 + 1')
    ->make_tokens->tokens('SELECT')->add_tokens('2', '+', '2')

The exceptions are when used as getters:

    my $sql = $pp->sql;
    my @tokens = $pp->tokens;


The strengths of this module are simplicity and speed. The main weakness is that it does very little semantic processing. So the IF in DROP TABLE IF EXISTS is treated the same as in SELECT IF (....

The second weakness is it provides little flexibility on style. You can specify strings for line-break and word-break, what string equates to a 'tab', and whether keywords, functions, and constants should be wrapped in any markup, but that's about it. You cannot say "I want to align on neighbouring 'ON'/'AS'/comparitor". The simplicity of its approach means it can be adapted easily to other purposes, eg modify the keywords to let it handle oracle instead, eg modify its I/O so it can process on a pipeline with minimal lookahead and buffering.

Ideas for future enhancements include:

It should be extended to handle comments, both in indentation and markup. It should provide more output options, eg PHP, Perl, HTML+CSS. It could provide smart processing, eg remove redundant parentheses. If the list of options grows, it would be nice to support having a configuration file.


Nic Sandfield, <niczero at cpan.org>


It has no unit tests.

Please report bugs, issues, or feature requests to bug-mysql-prettyprinter at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Mysql-PrettyPrinter. I will be notified and then you'll automatically be notified of progress on your bug as I make changes.


To install this module from its CPAN source package, run the following commands:

        perl Makefile.PL
        make test
        make install


You can find documentation for this module with the perldoc command.

    perldoc Mysql::PrettyPrinter

You can also look for information at:



My module owes its existence to the fine beginnings in this module (C) 2009 Jonas Kramer. I merely fixed some bugs, extended the usability, made it more efficient, and specialised it to the syntax of MySQL (v5.1).


The regular expression within lexicals was lifted from this module (C) 2010 Igor Sutton. I merely made it fractionally more efficient.


Copyright 2010 Nic Sandfield. All rights reserved.

This program is free software; you may use it, redistribute it, or modify it under the terms of the GNU General Public Licence (GPL) v3 (or any later version) as published by the Free Software Foundation.

See http://dev.perl.org/licenses/ or http://www.gnu.org/licenses/license-list.html for more information.