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.
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.
