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:

runmysql

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.

Example

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.

About David Megginson

Scholar, tech guy, Canuck, open-source/data/information zealot, urban pedestrian, language geek, tea drinker, pater familias, red tory, amateur musician, private pilot.
This entry was posted in General and tagged , , , . Bookmark the permalink.

11 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?

    References:
    1. http://stackoverflow.com/questions/10229324/pass-parameter-to-mysql-script-command-line
    2. http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

  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.

  11. 83Philomena says:

    Hello admin, i must say you have hi quality content here.
    Your blog should go viral. You need initial traffic boost only.
    How to get it? Search for; Mertiso’s tips go viral

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s