How can I change an URL inside a field in MySQL?

3 Views

I have a table called “wp-posts” with a field “post-content”. This field contains the text for a blog posts. I’d like to change all records to replace an URL for another one.

Imagine that I can have things like:

Read More

This is a test and somewhere there’s something like <img src=”http://oldurl.com/wp-content/somimg.jpg”> and something like <a href=”http://oldurl.com/something”>a link</a>.”

I want it to be

This is a test and somewhere there’s something like <img src=”http://newurl.com/wp-content/somimg.jpg”> and something like <a href=”http://newurl.com/something”>a link</a>.”

I need to be able to change this for every record in my table without having to open each post in WordPress and change them by hand. There has to be a way to do this

Post Views: 3

Related posts

Leave a Reply

5 comments

  1. This can be easily achieved with a simple SQL statement using MySQL’s replace() function. Before we do that, you should definitely do a database dump or whatever you use for backups. It’s not only that it’s The Right Thing To Do™, but if you make a mistake on your substitution, it might prove difficult to undo it (yes, you could rollback, but you might only figure out your mistake later on.)

    To create a database dump from MySQL, you can run something like this —

    mysqldump -h hostname -u username -p databasename > my_sql_dump.sql

    Where (and you probably know this, but for the sake of completeness for future generations…) —

    • hostname is a placeholder for the database hostname. If the database server is running on your own machine, then you can either use “localhost” or simply leave the “-h hostname” off entirely
    • username is a placeholder for the user with permission to run a dump on the database. This is often an admin, but if it’s a shared db, it might simply be you.
    • databasename is the name of the whole database containing your tables. (Note that the “-p” has nothing to do with this database name. “-p” indicates that MySQL should ask you for a password in order to log in.)

    Now that we got that out of the way, you can log in to the MySQL database using:

    mysql -h hostname -u username -p databasename

    And simply run this statement:

    UPDATE `wp-posts` SET `post-content` = REPLACE(`post-content`, "http://oldurl.com", "http://newurl.com");

    And that should do it!

    If you make a mistake, you can often rerun the statement with the original and new texts inverted (if the new text — in your case the new URL — didn’t already exist in the text before you did the replace.) Sometimes this is not possible depending on what the new text was (again, not likely in your case.) Anyway, you can always try recovering the sql dump —

    cat my_sql_dump.sql | mysql -h hostname -u username -p databasename

    And voilà.

  5. There is a few places to work that out.

    1. Backup your database
    2. Try using WordPress internal utility
    3. Search for matches
    4. Write your own search and replace

    Procedure

    1. Backup your database

    I generally go like so:

    mysqldump -u dbuser -p'dbpassword' --complete-insert --default-character-set=utf8 databasename > /home/renoirb/backups/databasename.sql

    I even have this in a crontab on my own server. But that’s an other topic.

    2. Try using WordPress internal utility

    WordPress has it’s own utility for that matter. Most of the time, it can be done only with it.

    Simply add to your theme’s functions.php (see wordpress documentation) file the following line:

    update_option('siteurl','http://local.workspace/');
update_option('home','http://local.workspace/');

    Assuming you are already running the theme that has that particular functions.php file.

    Run the site, by refreshing the page.

    Then, comment the lines!

    You won’t need them anymore.

    Otherwise they get to update your database configuration at every page load.

    If it is not enough, continue…

    3. Search for matches

    I personally use Adminer on my deployment, it is a lightweight one-file replacement for PHPMyAdmin.

    Why I suggest Adminer? It has a nice search all database feature that I find quite handy in our use-case.

    It is a search box, labelled: “Search in data tables”.

    4. Write your own search and replace

    UPDATE `wp_comments` SET `comment_author_url` = REPLACE(`comment_author_url`, "http://url", "https://url");
UPDATE `wp_postmeta` SET `meta_value` = REPLACE(`meta_value`, "http://url", "https://url");
UPDATE `wp_options` SET `option_value` = REPLACE(`option_value`, "http://url", "https://url");

    Note that in my case, I was migrating ALL my urls from NON HTTPS to HTTPS.

    Hope that was helpful