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.

• jarlh: UPDATE is for one table only, and you have to specify what column(s) to update.
• André Dion: Possible duplicate of Find and Replace text in the entire table using a MySQL query
• Henrik Petterson: @jarlh What is the solution for searching the entire database instead of one table/column?
• André Dion: "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." The duplicate thread I linked to covers that.
• Stender: I would simply export the entire database - and then do the search and replace in sublime or any other text editor with SAR, and then reupload it.

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:%';

• Henrik Petterson: How can make it target example.com domains only? Will your code affect all tables? What does the t in UPDATE t stand for? Some clarification would be really appreciated. Thanks!
• Bernd Buffen: i have add it in my answer
• Henrik Petterson: Will this replace the following terms loremhttp://example.com/ipsum.html or array entries like array('hellohttp://example.com/go.html')?
• Gjermund B. Dahl: Very useful. I modified the latter part to this: like '%http:%'
• Roman: examle code see here domhtml.ru/wordpress_replase_http_https_db_mysql

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/

• Tobias Beuving: -1 Because the OP asks how he can perform the action using phpmyadmin - not with a plug-in. Your warning is a valuable contribution -.much appreciated, but it is not an answer to the question. Please add the appropriate SQL statement(s) to use in phpmyadmin and remove the links to the plug-ins. If I see an updated answer that fits the guidelines I will +1 the -1.
• red-o-alf: @TobiasBeuving I edited the answer to make it more on-topic, generically pointing to making sure that that SQL statements deal with serialized data. Regarding the WordPress plugins part, I prefixed it with "if you happen to be using WordPress", so now it can be see more like an extra advice.

Answer 3

I would use insert:

update t
 set url = insert(url, 5, 0, 's')
 where url like 'http:%';

• Henrik Petterson: Could you kindly update your answer with explanation on your code? How can make it target example.com domains only? And what is the update t part?
• Gordon Linoff: update t is the syntax for updating a table; t is the table. If you want to target example.com, then you include that in the where clause, probably as where url like 'example.com/%'` or something similar.