Search & replace 'http' to 'https' in database

Question

Using phpmyadmin, I want to run a query that will search my entire database for:

http://example.com

And replace with:

https://example.com

My SQL knowledge is limited, maybe something like:

UPDATE ?? = REPLACE(??, 'http://example.com', 'https://example.com');

The database is over 1gb, so what can I run that will not crash the server.

Update: Note that while there are other answers posted here on SO that deals with search and replace, they don't seem to cover the entire database.

Answer 1

use REPLACE. and if there is a index on the field then the UPDATE can use them

UPDATE t
     set url = REPLACE(url, 'http:', 'https:')
     WHERE url LIKE '%http:%';

only change example.com

this will only find row with 'http://example.com'

UPDATE t
     set url = REPLACE(url, 'http:', 'https:')
     WHERE url LIKE '%http://example.com%';

or this will find all rows with http:// but only change only this http://example.com to https://example.com

UPDATE t
     set url = REPLACE(url, 'http://example.com', 'https://example.com')
     WHERE url LIKE '%http:%';

Answer 2

Warning, the answers given so far will mess up serialized data!

For example, say your site stores serialized data in a row with the URL in it, like this:

a:1:{i:0;s:19:”http://example.com”;}

Notice that the value of this item has 19 characters, and is denoted by s:19 in the array.

If you replace content using a SQL query, the same row on your new environment would end up like this:

a:1:{i:0;s:19:”https://example.com”;}

But after this change, the value is now 20 characters long meaning s:19 is incorrect. This invalidates the array and the entire row.

So either you make sure your SQL statements deal with serialized data, or if you happen to be using WordPress then there are a few options to search using PHP so as to not break the serialized rows:

• The Better Search Replace plugin automatically handles serialized data
• The Search and Replace plugin offers an option which handles serialized data

Taken and adapted from: https://wpengine.com/support/wordpress-serialized-data/