design for quality


MySQL commands you don't see often enough



I'd looked for an equivalent to Find and Replace to use within SQL statements in MySQL for a while, and it wasn't until a few months ago that I came across the replace() string function. For whatever reason, I didn't find mention of this function or its application as easily as I would have liked, so perhaps this post will help the next guy or gal become aware of it.

The usage is quite simple:

UPDATE table-name SET column-name = replace(column-name,'string_to_find','string_to_replace');

Another way to do find and replace, FYI, is to perform a mysqldump, and then, on the dump file use Notepad, or some other text editor (I like Programmer's Notepad) to do a regular Find and Replace.

Caveat: be careful using the latter method if your database/app saves data in serialized fashion. (Users of Drupal, take note.)

copy a database, easy!

I've often copied databases using MySQL dumps, not realizing it was this easy:

mysqldump -uusername -ppassword dbname | mysql -uusername -ppassword dbname-to-copy-to

Note the | (or "pipe"). Perhaps you've seen a mysqldump like this:

mysqldump -uusername -ppassword dbname > dumpfilename.sql

The difference between this and the former command is what we're doing with the output of the dump. With the pipe, we "pipe" the output to another program as input (in this case, the mysql executable). If you use > (aka "redirect") your output is simply written to a text file.


© 2008