Fork me on GitHub

article

Search and replace directly within MySQL.

April 3, 2009 | Web Design & Development

MySQL always amazes me with the breadth of functionality that it has. Too often we just use it to push and pull information from the database. So when I hit an occasion where I can so in a single SQL statement that would require a time consuming PHP I get excited.

Here, I had to so some simple string replacement on some database values. I had some PDF files in a WordPress MU database that didn’t get updated by one means or another when upgrading from WordPress MU 2.6 to 2.7. WordPress 2.6 stores attachment location data will full file paths, so that when the database is moved to another server the file paths are wrong and need updating. This was fixed in 2.7 by storing just the path relative to the upload root. What that also means, though, is that when WordPress MU encounters that full file path that permalink generation borks.

So, with the scene set, here’s the code. Its pretty simple. We simply walk the database table using the REPLACE() function (found on the MySQL.com string functions page) where we encounter our string. Example:

  1.  UPDATE wp_1_postmeta
  2.  SET meta_value = REPLACE(meta_value,'/path/to/files/','')
  3.  WHERE meta_value LIKE '%.pdf'
  4.  AND meta_value LIKE '/path/to/files/%'
  5.  AND meta_key = '_wp_attached_file'

Explanation:

  1.  Update "table name"
  2.  SET "column name" = REPLACE("column name","find","replace")
  3.  WHERE "column name" LIKE "ends with .pdf"
  4.  AND "column name" LIKE "starts with path to files dir"
  5.  AND "other column name" = "file attachment identifier"

Yeah, its that simple. Not much more to say except that I know all the DB admins that run across this are gonna want to welcome me to 1998 😉

2 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  • that was really helpful.
    you saved my time.
    Affected rows: 131 (Query took 0.0036 sec)
    thanks a lot 😉

    tasarhane, May 31, 2009 6:39 am | permalink

  • Very good for those quick changes to paths lol used it:D great!

    Chris, June 5, 2009 3:26 am | permalink

Comments are closed