Find & Replace in MySQL

View Comments

When I am transferring applications from one server to another such as WordPress, CMS Builder, or Drupal, I find that absolute paths are sometimes stored in their database tables. So, once the files and database are moved over to a new location, all of those paths become invalid. While this can be performed outside of MySQL, it’s nice to know how to do it inside of MySQL.

The import / export method

When moving an application between servers, you have to export the database data from the old server and then import the data to the new server. When you need to change the paths in the database data, you can simply:

  • export your old database to a raw SQL file,
  • open the raw SQL file in a text editor, like Notepad++,
  • perform a find and replace operation on the file to convert the old paths to the new paths, and
  • import the raw SQL file to the new database.

This method works. It hasn’t failed me yet. But, it becomes inconvenient when I am not switching servers. When building a site for a client using a CMS application, for example, we oftentimes place their site into a folder on their server like “newsite,” or something like that. So, exporting the database to a raw SQL file, editing the SQL file, and re-importing the database requires that the database be destroyed between the export and the import, granted you do also change all of the IMPORT statements into REPLACE statements. Luckily, MySQL actually has an easier way to do this.

The SQL query method

The next method that I’d like to share is very useful for:

  • moving an application to a different folder within the same server,
  • moving an application to a different server while leaving the database on the previous server, and
  • moving a database to a new server without being forced to export the old database as a raw SQL file (which allows you to save it in a compressed format).

This method is simple and requires that you run a query on any database table that needs to be updated. On the server that your application is going to reside on, run an SQL query using the REPLACE() function in SQL (note: this is not the same as the REPLACE statement). The REPLACE() function takes three parameters: the string to operate on, the string to replace, and the string to replace the occurrences of the second string. Much like a find and replace operation in a text editor. It then returns the resulting string. Here is an example:

Using the REPLACE() function

update `table_name` set `column_with_path` = replace(`column_with_path`, 'path/to/old', 'path/to/new');

This will replace all occurrences of path/to/old with path/to/new in the column_with_path column. The limitation of this is that you must specify every table and every column that you want to be updated in your MySQL database. But, with some PHP magic, I’m sure you can find a way to make that easier.

Did you like this? Share it:
  • Benjamin Rosa

    Yet I have not found any path that can deal with this in an easy and simple way. This is the only logic to which I think of to import my old application to new one.

blog comments powered by Disqus