MySQL wrapper script: variables from the command line

There’s a lot I like about PostgreSQL, and I’ll probably switch eventually, but for now, I’m still more comfortable with MySQL, especially its ability to update huge amounts of existing data in-place very quickly.

I do have some technical gripes with MySQL, though:

  • InnoDB doesn’t support fulltext indexes, so I’ve forced to give up referential integrity for any table that needs fulltext searching.
  • The mysql(1) command-line tool doesn’t allow setting user variables on the command line when invoking a script.

The first is too big a problem for me to take on right now, but the second looked like it just needed a bit of perl magic, so I wrote a wrapper script:


This script scans each command-line option for a string beginning with “@”, and pulls it out of the arguments before passing the rest on to mysql. Any option in the format @name=value will be converted to a SQL variable declaration and passed to mysql; any other option gets passed through unmodified.


Let’s say that you have a SQL script named getstuff.sql:

use mydatabase;

select * from Stuff where type=@type;

You can invoke this script like this:

runmysql @type=foo -X < getstuff.sql

The script will run the command “mysql -X” (the “-X” means XML output — it’s just there to demonstrate how other options get passed through), and prepend a declaration to the SQL script, so that MySQL actually sees this:

set @type = 'foo';
use mydatabase;

select * from Stuff where type=@type;

That’s it, really. Of course, it’s not that useful in a trivial script like this, but for scripts that load data from many different files (for example), it makes life much simpler.

This entry was posted in General and tagged , , , . Bookmark the permalink.

10 Responses to MySQL wrapper script: variables from the command line

  1. deepwinter says:

    Useful script. Do you have it on github ??

  2. Marcel says:

    If mysql prompts for a password, the input does not get fed to mysql correctly apparently.

  3. Jamie McNaught says:

    Sadly this doesn’t seem to work if you want to specify the database name as a variable. e.g. CREATE DATABASE @DBNAME. Having to resort to using sed. 😦

  4. You CAN pass variables from the command-line [1], but you have to source your .sql script instead of feeding it via STDIN. Run the mysql command like this:

    mysql -h HOST -u USER -pPASS -e “set @type = ‘foo’; source getstuff.sql;”

    Don’t forget the semicolons.

    The sad part of this story is that you CANNOT use variables for a table, column, or database name or part of such a name[2]. Rats! That would be quite useful, wouldn’t it?


  5. Wow, wonderful blog format! How long have you been running a blog for?
    you make blogging glanjce easy. The overall look
    of your site is wonderful, as smartly as the content material!

  6. Adriana says:

    WOW jut what I was searching for. Came here by swarching for
    what tto bring to file taxes

  7. obviously like your web-site however you havfe to check the spelling on quite a
    feew of your posts. A number of them are rife with spelling
    issues and I to find it very bothersome to tell the truth on the
    othe hand Iwill surely come back again.

  8. Men can not imagine the way things look like based on
    the colors and patterns through imagination. For many pickers, vintage Nike clothing is still a lucrative market alongside vintage Hawaiian shirts, vintage
    Levis and vintage Levi jackets. They cost less than new items
    – a piece of clothing in a charity shop, car
    boot sale or second hand clothing website will almost certainly cost a fraction of it’s original new price.

  9. When you purchase a new bag to make your travel
    in a tote plan workable, consider buying an unusual color or
    pattern to make your bag distinctive from all the other black bags seen circling
    on conveyor belts. Even guys who know all the tips to attract women and
    have practiced their dating skills for years can have a hard time keeping
    a conversation alive and entertaining. To know more information about
    How to Attract Girls and How To Approach A Woman visit Sinnsofattraction.

  10. Aurelia says:

    also has major influences in building a civilization
    in India. The various books that Craig ‘felt inspired to
    write’ are available at. “”ISRAEL’S ROUTINE shelling of the
    Gaza coast was heavier and louder than usual yesterday.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s