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.
Quoderat 
Useful script. Do you have it on github ??
If mysql prompts for a password, the input does not get fed to mysql correctly apparently.
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. 😦
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
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!
WOW jut what I was searching for. Came here by swarching for
what tto bring to file taxes
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.
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.
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.
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.