A Shebang for MySQL: Execute your SQL files directly from the UNIX prompt

I'm sure as a system administrator on any flavour of UNIX you appreciate the power of the few magic characters, such as #!/bin/sh, found at the first line of many scripts, commonly called a shebang. They allow you to treat a script almost as a binary executable. To run it, you just type its name at the prompt and don't have to worry if it's written in Perl, Bash, awk, Python or anything else. Just for your MySQL scripts you still type the verbose mysql < your_script.sql all the time, don't you?

I found this to become especially annoying (yes, programmers and administrators are lazy folks) now that every MySQL release gives me more and more powerful features: Where I before had to write a script in another language anyway, now I often find that MySQL allows me to do everything in plain SQL, even some complex data extraction tasks (using e.g. GROUP_CONCAT(), some stored functions and built-in features of the mysql command line client). But then I always have to remember that this particular script needs to be fed to the mysql client via input redirection (and maybe mysql even needs some special options to achieve this task).

So, what's the problem? Why don't we just start our SQL scripts with a shebang pointing to the command line client? Like so:

#!/usr/local/mysql/bin/mysql
# This does NOT work

SELECT 'Hello world!' AS test;

The difficulty is, that mysql always expects the script to be fed to it via standard input. You can't specify a script to execute as an argument on the command line (the argument defines the default database) and UNIX (at least the flavours I use) don't allow you to use input redirection in a shebang:

#!/usr/local/mysql/bin/mysql <
# This does NOT work

SELECT 'Hello world!' AS test;

Of course you could use a simple one line wrapper script, let's call it mysql_shebang and install it somewhere in your PATH:

#!/bin/sh
mysql < $1

Then you could call this script from the shebang in your SQL scripts (instead of directly calling mysql):

#!/usr/bin/env mysql_shebang

SELECT 'Hello world!' AS test;

Note that we run mysql_shebang through env, this is needed as one shebang script cannot call another shebang script directly. The additional advantage is that PATH will be searched for mysql_shebang and we don't have to specify the full path in the shebang.

I still don't like this solution as it is not self contained: It won't work if you move it to another machine or hand it to somebody where mysql_shebang is not installed.

But there exists in fact a simple (though maybe a little bit obscure) solution. You only need to add a second line to all your SQL scripts, just after the shebang:

#!/bin/sh
--/. &> /dev/null; exec mysql "$@" < $0

SELECT 'Hello world!' AS test;

This works! Just save this script as your_script.sql, make it executable and run it directly from the shell prompt:

$ chmod u+x your_script.sql
$ ./your_script.sql
test
Hello world!
$

If the script is in your PATH (or you're running on insecure settings) you don't even need the ./ to specify the current directory. And even better, you can hand additional options/arguments (such as host, user or default schema) directly to your script, they will be forwarded to mysql:

$ your_script.sql -h your_host your_schema

So let's just have a final look at what these two obscure lines at the beginning of the script actually do:

An interesting lesson of shell coding in just one line...