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.

UPDATE is for one table only, and you have to specify what column(s) to update. — jarlh, Mar 17 '17 at 12:45
Possible duplicate of [Find and Replace text in the entire table using a MySQL query](http://stackoverflow.com/questions/11839060/find-and-replace-text-in-the-entire-table-using-a-mysql-query) — André Dion, Mar 17 '17 at 12:46
@jarlh What is the solution for searching the entire database instead of one table/column? — Henrik Petterson, Mar 17 '17 at 12:48
"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. — André Dion, Mar 17 '17 at 12:49
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. — Stender, Mar 17 '17 at 13:09
The trick is to know where you store the url's. Database design issue! — jarlh, Mar 17 '17 at 13:35

score 51 · Answer 1 · edited Mar 18 '20 at 04:13

51

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

edited Mar 18 '20 at 04:13

clayRay

683
1
14
32

answered Mar 17 '17 at 12:54

Bernd Buffen

14,525
2
24
39

2

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! – Henrik Petterson Mar 17 '17 at 13:16
i have add it in my answer – Bernd Buffen Mar 17 '17 at 13:22
1

Will this replace the following terms `loremhttp://example.com/ipsum.html` or array entries like `array('hellohttp://example.com/go.html')`? – Henrik Petterson Mar 17 '17 at 13:34
Very useful. I modified the latter part to this: `like '%http:%'` – Gjermund Dahl Jun 15 '19 at 10:13
examle code see here https://domhtml.ru/wordpress_replase_http_https_db_mysql/ – Roman Mar 17 '20 at 15:42
@Gjermund Dahl Why do we need the `like` key since REPLACE will do nothing if the string doesn't exist ! – Sami Jan 05 '23 at 17:58

Redoman · Answer 2 · 2020-08-28T17:17:26.530

3

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/

edited Aug 28 '20 at 17:17

answered Aug 03 '20 at 19:34

Redoman

3,059
3
34
62

-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. – Tobias Beuving Aug 26 '20 at 09:10
@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. – Redoman Aug 28 '20 at 17:22

score 2 · Answer 3 · answered Mar 17 '17 at 12:45

2

I would use insert:

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

answered Mar 17 '17 at 12:45

Gordon Linoff

1,242,037
58
646
786

1

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? – Henrik Petterson Mar 17 '17 at 12:46
`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 `'http://www.example.com/%'` or something similar. – Gordon Linoff Mar 18 '17 at 02:02

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

3 Answers3

Linked